How to convert an SQL select query into a formatted HTML table within a MySQL function

Question

I'm looking for a way to generate valid HTML code within MySQL (without PHP) by converting any query output into an HTML table.

Here's my progress so far and evidently, I'm stuck. I hope I can get some help, thanks.

1. "dynSQL" - A procedure to take any Select query and create a named table out of it

Since MySQL doesn't allow dynamic queries in functions, I'm calling a procedure that creates a named table, tmp. I can't use a temporary table because info about temporary tables is not available in information_schema (in mysql 5.6)

CREATE DEFINER=`root`@`%` PROCEDURE `dynSQL`(SQL_QUERY TEXT)
BEGIN
set @SQLQ := 'Drop table if exists tmp;';
PREPARE stmt from @SQLQ;
Execute stmt;

SET @SQLQ := concat('create table tmp as ',SQL_QUERY);
PREPARE stmt from @SQLQ;
Execute stmt;

-- I'm adding a auto increment ID column to be able to loop through the rows later

SET @SQLQ := "ALTER TABLE tmp add column CustColHTML_ID INT NOT NULL AUTO_INCREMENT FIRST, ADD primary KEY Id(CustColHTML_ID)";
PREPARE stmt from @SQLQ;
Execute stmt;


DEALLOCATE PREPARE stmt;

END

2. "MakeHTML" - Function to read from the table tmp and return a formatted HTML table

CREATE DEFINER=`root`@`%` FUNCTION `MakeHTML`() RETURNS text CHARSET utf8
    DETERMINISTIC
BEGIN

    DECLARE HTML text default "<TABLE><TR>";
  DECLARE rowCount int default 0;
    DECLARE i int default 0;
    select concat('<TR>',group_concat('<TD>',column_name,'</TD>' separator ''),'</TR>') into html from information_Schema.`columns` where table_name='tmp';
    Select max(CustColHTML_ID) into rowCount from `tmp`; -- Set the row counter
    WHILE i<=rowCount DO

  -- What do I do here? How do I loop through the columns of table tmp?

        set i:=i+1;
    END WHILE;
    RETURN HTML;  
END

As you can see, I'm stuck at looping through the unknown and dynamic columns of table tmp. I read about how a cursor can be used here, but all the examples I saw make use of known columns and assign those into named variables. However, since the query itself is dynamic, I wouldn't know the names of the columns.

I'd really appreciate your time and assistance, thanks!

p.s. I've posted this as a new question because my earlier question was marked as closed as being too broad. I subsequently edited my question but it was still showing as Closed. I've therefore deleted the older question and replaced it with this one.

You know the table name you are using right? you can use this I think:http://stackoverflow.com/questions/4950252/mysql-iterate-through-column-names — Jeremy C., May 12 '15 at 11:46
Why would you want to render your html using a query? Surely this is better handled in the application layer? — GarethD, May 12 '15 at 12:03
@GarethD Yes, this was asked in my earlier question as well. The reason I'm doing it within MySQL is so that I can have an event render a query as an HTML table and insert it into another MySQL table (emailPendingTable). An external 'EmailSender' program routinely scans this and sends out formatted mails to the intended recipients. — Prashanth, May 12 '15 at 12:07
@JeremyC. Hi, and thanks. I've also thought about a cursor looping through the columns of table `tmp`. The outer loop would have to run for each row in the table and the inner loop for each column. To extract the value at each cell (so that I can pad it with tags), I have to generate a dynamic query that selects the particular column from the current row. It just seems excessive that I have to make mXn queries and I was therefore wondering if I could select a whole row at once and then loop through each column of that result set while building the HTML table. — Prashanth, May 12 '15 at 12:11
well i don't really see a problem with looping through the column names for every row unless you have hundreds of columns (which I doubt) and I don't really see any other way of doing this tbh — Jeremy C., May 12 '15 at 12:16
@JeremyC. Ah another roadblock. Since MySQL doesn't allow dynamic queries in functions, I'll have to move this code into the procedure 'dynSQL' and store the generated HTML in another temporary table. The function has to read this HTML and return it as a text variable. — Prashanth, May 12 '15 at 12:52

GarethD · Accepted Answer · 2015-05-12T13:12:04.990

With a sample table as such:

CREATE TABLE tmp (ID INT, Col1 INT, Col2 INT);

The SQL you would need to generate your HTML is:

SELECT CONCAT('<table>', GROUP_CONCAT(CONCAT('<tr><td>',ID,'</td><td>',Col1,'</td><td>',Col2,'</td><tr>')), '</table>')
FROM tmp;

You can generate this using the INFORMATION_SCHEMA:

SELECT  CONCAT
        (
            'SELECT CONCAT(''<table>'', GROUP_CONCAT(CONCAT(''<tr>'', ', 
            GROUP_CONCAT(CONCAT('''<td>'',', COLUMN_NAME, ',''</td>''')), 
            ', ''</tr>'')), ''</table>'') FROM tmp'
        )
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_NAME = 'tmp';

It is then just a case of executing this:

SET @SQL = (
                SELECT  CONCAT
                        (
                            'SELECT CONCAT(''<table>'', GROUP_CONCAT(CONCAT(''<tr>'', ', 
                            GROUP_CONCAT(CONCAT('''<td>'',', COLUMN_NAME, ',''</td>''')), 
                            ', ''</tr>'')), ''</table>'') FROM tmp'
                        )
                FROM    INFORMATION_SCHEMA.COLUMNS
                WHERE   TABLE_NAME = 'tmp'
            );

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Example on SQL Fiddle

ADDENDEUM

Forgot to include table headers:

SET @SQL = (
                SELECT  CONCAT
                        (
                            'SELECT CONCAT(''<table><tr>'',',
                            GROUP_CONCAT(CONCAT('''<th>'',''', COLUMN_NAME, ''',''</th>''')), 
                            ', ''</tr>'', GROUP_CONCAT(CONCAT(''<tr>'', ', 
                            GROUP_CONCAT(CONCAT('''<td>'',', COLUMN_NAME, ',''</td>''')), 
                            ', ''</tr>'')), ''</table>'') FROM tmp'
                        )
                FROM    INFORMATION_SCHEMA.COLUMNS
                WHERE   TABLE_NAME = 'tmp'
            );

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Example on SQL Fiddle

Brilliant and so much more elegant. Thank you very much! I can't vote up your answer due to my low rep score, but I will when I can! — Prashanth, May 12 '15 at 16:48

How to convert an SQL select query into a formatted HTML table within a MySQL function

1 Answers1