MqttCogs Now Supports DataTables

This post is based on Version 2.3 of mqttcogs.

Have you tried using the Google Visualization Table chart? It isn’t great. Just about acceptable, if you want to dump some data but is difficult to style and is missing a lot of features that you get with other table components. In this post we’ll compare the Google Visualization Table and also the new mqttcogs_drawdatatable, this uses DataTables to draw a table.

Lets try a simple google table first. We’ll show some temperatures from one of my Minew sensors. I’ve actually added some basic css styling to this because I really dislike the standard google styling

[mqttcogs_drawgoogle refresh_secs="30" charttype="Table" options="{width: '100%', height: '100%'}"][mqttcogs_data limit="5" order="DESC" topics="/minew/AC233FA249C9$.temperature"][/mqttcogs_drawgoogle]

It is possible to click on the headers and change the ordering. It is also possible to set the column headers (I think!). So for a basic table it ‘sort of’ works. One area that I really dislike is the refresh. The table flickers as it appears to be completely redrawn.

Lets compare with the new mqttcogs_drawdatatable. This uses DataTables to draw the table. Like the google visualization and leaflet visualizations this includes a standard javascript file that draws the table. You can replace this with your own version. Out of the box, it looks like this.

[mqttcogs_drawdatatable refresh_secs="30" options="{width: '100%', height: '100%'}"][mqttcogs_data limit="5" order="DESC" topics="/minew/AC233FA249C9$.temperature"][/mqttcogs_drawdatatable]

You get a much cleaner table and the ability to customize using the DataTables library. This is the default clientside javascript that is used to draw the table (used if you don’t replace with your own). The data parameter being a Google Datatable, the datatable parameter the DataTable instance and the datatableoptions parameter the options as specified in the shortcode.

function (data, datatable, datatableoptions) {
				if (!this.datatable) {
					//we have to inject html for column headers
					//datetime, topic1, topic2, topic3...
					var headerelement = jQuery('#' + this.id + '>thead>tr').first(); 
					
					for (var cidx=0;cidx<data.getNumberOfColumns();cidx++) {
							headerelement.append('<th>' + data.getColumnId(cidx) + '</th>');		
					}		
					datatable = jQuery('#' + this.id).DataTable(this.options);		
					this.datatable = datatable;
					
					//datatables can't handle nulls....
					for(var cidx=0;cidx<datatable.columns().length;cidx++) {
							datatable.column(cidx).defaultContent = "";
					}
				}
				
				datatable.clear();
					
				for (var ridx=0;ridx<data.getNumberOfRows();ridx++) {
					var row = [];
					var val = null;
					for (var cidx=0;cidx<data.getNumberOfColumns();cidx++) {
						val = data.getValue(ridx, cidx);
						if (val instanceof String) {
							row.push(val);
						}
						else if (val instanceof Date) {
							row.push(val);
						}
						else if (val instanceof Object) {
							row.push(JSON.stringify(val));
						}
						else {
							row.push(val);
						}
					}
					datatable.row.add(row);		
				}
				
				datatable.draw();



6 Comments

Hi, something changed with a different cronjob approach. I see now a _mqttcogs_plugin_lock.pid on my server but get no cronjob messages. Does it mean that mqttcogs is running but the mqtt-server sends no data?

I have on my todo list to change logging to write to the database – we could then view the debug log in the wordpress site without messing around.

For now though, the php_errors.log file works well. It will log all failed connections to the mqttbroker and all messages in and out.

Chris

Thanks for your help so far. It seems that there is a connect to the MQTT Server, but it also looked like the are not able to write anything to the database. Would it be possible that I send you the latest Log-messages?
Thanks Sebastian

Hi, I have trouble installing and using mqttcogs. My cronjob reports the following failure: ERROR [SHUTDOWN] lvl:2 | msg:mysqli_query(): Error reading result set’s header | file:/…/wp-includes/wp-db.php | ln:2024

The cron.log file reports the following:

X-Powered-By: PHP/7.3.19
Set-Cookie: PHPSESSID=f754d5f675c746a59047caee446153bc; path=/
Expires: Thu, 19 Nov 1981 08:52:00 GMT
Cache-Control: no-store, no-cache, must-revalidate
Pragma: no-cache
Content-type: text/html; charset=UTF-8

What could be wrong?

Hi,

I’ve not seen this error before. A quick google suggests that it is related to mysql timeout. You could try altering the MQTT Connection Recycle (secs) settings (see MQTT Cogs setting page) from 298 and then altering how often your CRON job kicks off. Change to 58 and 60 respectively and see if that helps?

Chris

Leave a Reply

Your email address will not be published. Required fields are marked *