如何在MySQL函数中将SQL选择查询转换为格式化的HTML表

我正在寻找一种通过将任何查询输出转换为HTML表来在MySQL中生成有效HTML代码(无PHP)的方法.到目前为止,这是我的进度,很明显,我遇到了困难.希望我能得到一些帮助,谢谢.1.“ dynSQL”-一种执行任何Select查询并从中创...

我正在寻找一种通过将任何查询输出转换为HTML表来在MySQL中生成有效HTML代码(无PHP)的方法.

到目前为止,这是我的进度,很明显,我遇到了困难.希望我能得到一些帮助,谢谢.

1.“ dynSQL”-一种执行任何Select查询并从中创建命名表的过程

由于MySQL不允许函数中的动态查询,因此我将调用一个创建命名表tmp的过程.我不能使用临时表,因为有关临时表的信息在information_schema中不可用(在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”-从表tmp读取并返回格式化的HTML表的函数

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

如您所见,我被困在遍历表tmp的未知列和动态列的过程中.我阅读了有关如何在此处使用游标的信息,但是我看到的所有示例都利用了已知的列并将它们分配给命名变量.但是,由于查询本身是动态的,所以我不知道列的名称.

非常感谢您的时间和协助,谢谢！

ps.我将其发布为新问题,因为我先前的问题被标记为已关闭,因为范围太广.随后,我编辑了问题,但问题仍显示为“已关闭”.因此,我删除了较早的问题,并用此问题代替了.

解决方法:

带有这样的样本表：

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

生成HTML所需的SQL是：

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

您可以使用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';

这只是执行此操作的一种情况：

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

登场

忘记包含表格标题：

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