Creating HTML Tables from SQL

If you are like me you have probably come across this scenario

You have wasted your precious time developing a super nice and complex select query to fetch your data from an RDBMS, but you have been doing this in a database client and your SQL is working pretty well. Cute, now you need your application like PHP to run this SQL and produce and HTML table that you should echo to your user. Great! Just when you thought the codding job was done now you need to start referencing php syntax aswell and this code takes you all morning to write since you keep confusing SQL syntax and PHP syntax 😠😠

Well you can get on the internet of things and use a php code like this one here by David Walsh, or if you are crazy like me you can make your sql write the HTML code for you so you just simply echo the result to your browser. Lets begin:

1. The Data Set: we first of all need to get the data set that we want to convert into an HTML table. This is simple enough we just run our select query. For my data sent I have amended the pivot stored procedure in my previous post here, to support summarizing by multiple {field:function} combinations. Then I have also amended the select to cache the results in a table temporary called _vapps_ddl_pr_json_pivot using the create temporary table .. as select ... syntax. So for me I will run my SQL in two steps as follows (I am using MySQL 5.7 Server and HeidiSQL client, but workbench will do as well)

call _vapps_ddl_pr_json_pivot('select * from test','["student"]','["yr"]','[{"score":"SUM"},{"score":"AVG"}]');
select 
t.`student`,
t.`Sum of 2015`,
t.`Avg of 2015`,
t.`Sum of 2015`,
t.`Avg of 2015`,
t.`Sum of 2015`,
t.`Avg of 2015` 
from 
_vapps_ddl_pr_json_pivot t;

We know every HTML table cell begins with the <td> tag and ends with a </td> tag and every row with <tr></tr> tags, so all we will need to do is to add these strings in the correct places using the concat SQL function (in oracle you would be using the || operator). Now my queries will look like this:

call _vapps_ddl_pr_json_pivot('select * from test','["student"]','["yr"]','[{"score":"SUM"},{"score":"AVG"}]');
select
concat(
'<tr>',
'<td>', ifnull(t.`student`,'&nbsp;'),'</td>',
'<td>', ifnull(t.`Sum of 2015`,'&nbsp;'),'</td>',
'<td>', ifnull(t.`Avg of 2015`,'&nbsp;'),'</td>',
'<td>', ifnull(t.`Sum of 2015`,'&nbsp;'),'</td>',
'<td>', ifnull(t.`Avg of 2015`,'&nbsp;'),'</td>',
'<td>', ifnull(t.`Sum of 2015`,'&nbsp;'),'</td>',
'<td>', ifnull(t.`Avg of 2015`,'&nbsp;'), '</td>',
'</tr>') html
from 
_vapps_ddl_pr_json_pivot t;

This gives us multiple rows, but we want a single string of our table definition, so we will just use the group_concat function in mysql (in oracle you would be using listagg) with a blank separator. Then finally we will use concat again outside the group _concat to apped the <table> and </table> tags. Our SQL would now look like this:

call _vapps_ddl_pr_json_pivot('select * from test','["student"]','["yr"]','[{"score":"SUM"},{"score":"AVG"}]');
select 
concat('<table cellspacing="0" class="sql">',
group_concat(
concat('<tr>',
'<td>',ifnull(t.`student`,'&nbsp;'),'</td>',
'<td>',ifnull(t.`Sum of 2015`,'&nbsp;'),'</td>',
'<td>',ifnull(t.`Avg of 2015`,'&nbsp;'),'</td>',
'<td>',ifnull(t.`Sum of 2016`,'&nbsp;'),'</td>',
'<td>',ifnull(t.`Avg of 2016`,'&nbsp;'),'</td>',
'<td>',ifnull(t.`Sum of 2017`,'&nbsp;'),'</td>',
'<td>',ifnull(t.`Avg of 2017`,'&nbsp;'),'</td>',
'<tr>')
separator ''),
'</table>') html
from 
_vapps_ddl_pr_json_pivot t;

Run this and voila!!! you are done, all your PHP has to do now is echo this to your browser. when I run my SQL I got the result below:

<table><tr><td>chiku</td><td>&nbsp;</td><td>&nbsp;</td><td>152.56</td><td>76.280000</td><td>169.90</td><td>84.950000</td><tr><tr><td>peter</td><td>&nbsp;</td><td>&nbsp;</td><td>160.95</td><td>80.475000</td><td>106.53</td><td>53.265000</td><tr><tr><td>some alumini</td><td>143.10</td><td>71.550000</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><tr><tr><td>tawonga</td><td>&nbsp;</td><td>&nbsp;</td><td>222.73</td><td>55.682500</td><td>276.43</td><td>69.107500</td><tr></table>

So lets test this by opening Google Chrome and typing in about:blank in the address bar and hit enter. Then hit F12 and go to the element's tab of the developer tools. Right click the <body></body> tags and click Edit as HTML then paste your sql results inside the body tags. Lastly right click the <head></head> tags and Edit as HTML and paste the following in between the head tags. Your table should now look nice

<style>
    .sql{
    width:100%;
    border: 1px solid blue;
    border-bottom: none;
border-right: none;
font-family: arial;
    }
    .sql td{
        border-bottom: 1px solid blue;
border-right: 1px solid #ddddff;
    }
    .sql tr:hover{
background: #e0e0ff

}</style>

So why would you want to do such a thing as building html from a database level. Well apart from convenience and centralized development  there is a minor performance benefit. Building the table in PHP will require that the SQL Server runs the result set then php traverses the same result set all over again just to insert the tags. Where as adding the HTML to your SQL query means you eliminate the second looping in PHP so the SQL solution should run roughly twice faster. I tried this with Z-Ray on Zend Server and indeed the performance statistics seem to agree with this claim. Actually this makes more sense when you consider that the searching at db level is multi threaded and uses optimized key based searching algorithms to traverse the data where as the PHP loop is a single thread serial iteration.

Obviously from here we now need some of that RDBMS JSON magic to create a unified function that builds such a table for any SQL that you dream of and also to fix the most difficult part... the column headers of-cause 😀, but that's a post for another day