title: How to convert an SQL select query into a formatted HTML table within a MySQL function
body: <p>I'm looking for a way to generate valid HTML code within MySQL (without PHP) by converting any query output into an HTML table.</p>
  
  <p>Here's my progress so far and evidently, I'm stuck. I hope I can get some help, thanks.</p>
  
  <p><strong>1. "dynSQL" - A procedure to take any Select query and create a named table out of it</strong></p>
  
  <p>Since MySQL doesn't allow dynamic queries in functions, I'm calling a procedure that creates a named table, <code>tmp</code>. I can't use a temporary table because info about temporary tables is not available in information_schema (in mysql 5.6)</p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>2. "MakeHTML" - Function to read from the table <code>tmp</code> and return a formatted HTML table</strong></p>
  
  <pre><code>CREATE DEFINER=`root`@`%` FUNCTION `MakeHTML`() RETURNS text CHARSET utf8
      DETERMINISTIC
  BEGIN
  
      DECLARE HTML text default "&lt;TABLE&gt;&lt;TR&gt;";
    DECLARE rowCount int default 0;
      DECLARE i int default 0;
      select concat('&lt;TR&gt;',group_concat('&lt;TD&gt;',column_name,'&lt;/TD&gt;' separator ''),'&lt;/TR&gt;') into html from information_Schema.`columns` where table_name='tmp';
      Select max(CustColHTML_ID) into rowCount from `tmp`; -- Set the row counter
      WHILE i&lt;=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
  </code></pre>
  
  <p>As you can see, I'm stuck at looping through the unknown and dynamic columns of table <code>tmp</code>. 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.</p>
  
  <p>I'd really appreciate your time and assistance, thanks!</p>
  
  <p>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.</p>