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.

Answer 1

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