How to render a HTML table from MySQL data to build simple live dashboards

In this post I’m going to describe how to build simple online dashboards by rendering a mysql query in a HTML table. Using Tablesorter and Tablechart the output will not only become sortable but you’ll also be able to filter any column and display a chart of the resulting data.

As an example I’ve used my wordpress database to query the number of posts I wrote over the years and display the resulting data in a chart.

The Result

Table SQL Tablesorter

Features

Sorting The basic functionality of the tablesorter-plugin. You can sort the table by clicking on each table-header. You can even pre-sort the table (these settings would go into the tablesorter-options.php file of my example-code).

Math: I’ve included the math-plugin of the tablesorter-plugin. In this example the footer row dynamically calculates the sum of the posts (here 13). This is achieved by adding data-math=”col-sum” to the th-element of the table. Check out the documentation of the math-plugin here.

Filter: You can filter each column by typing in the fields there. The calculated sum even updates dynamically.

$Sum of Table$

The Code

I’ve prepared an archive of the example to get you started. Please be aware that this my personal custom usage of the plugins. You probably have to adapt and expand the code quite a bit to make it work for your project.

→ Download example files

So let’s talk you through the code. I’m going to explain the most important parts – from there you probably just have to go and try/error yourself.

The SQL connection

To get started you need to fill in the connection details of your mysql database. Locate the following code block and edit accordingly.

// DB connect //

$link = mysql_connect('DATABASE_HOST_URL', 'USERNAME', 'PASSWORD');
if (!$link) {
die('No connection possible: ' . mysql_error());
}
mysql_select_db ('DATABASE_NAME' , $link);

The SQL query

This is where you put your sql query. My query is a very simple one selecting the number of posts I wrote on this blog over time. The resulting data has two columns; Time and Posts.

// SQL Query //

$sql= "
SELECT Concat(Year(post_date), '-Q', Quarter(post_date)) Time, 
   Count(*) 
FROM   wp_posts 
WHERE  post_status = 'publish' 
   AND post_type = 'post' 
   AND Date(post_date) > '2014-01-01' 
GROUP  BY Time 
ORDER  BY Time DESC 
";

The Loop

This part is quite important and you’ll need to edit it according to your query. We’re converting the sql result to variables that we can output in the resulting table rows. If you had a third data column resulting from your query you would have to add

$thirdcolumn = $row[3]
$thirdcolumn

in the code and so forth.

// The Result Loop (The Table Rows) //

while($row = mysql_fetch_row($res)) {

$quarter        = $row[0];
$posts      = $row[1];  

echo "

$quarter
$posts

";
}

Settings & Options

In this part of the code the Tablesorter and Tablechart jQuery Plugins get their settings & options. Most of them are quite easy to understand but at some point you probably have to read up about other options.

// Table Sorter Options //

include('tablesorter-options.php');

// Initiate Tablechart + Options //

echo "
";

I hope this helps somebody to get a quick start building a dashboard from sql data – be aware though that I probably can’t give support if you can’t get the code to work due to a number of reasons (not enough time, not even a coder myself, …)

Technical Marketing Guide