MYSQL ALIAS HELP

[Davey-UK]

I am working on a script that has the following sinario.
Basically a table is created and populated from MYSQL with table headers and the answers listed below in rows. The table top names are actually meant to be questions. As in the image below


The table headers msq2, msq3, msq4 etc, etc are auto generated table names from the database. In the script itself, we actually call these columns the question names but for some reason the questions are saved in the database as a "comment" on the original table. As in the image below



The question is this:
Does anyone know a way of renaming the outputted table headers to the actual "comment" name. So that for example mcq5 in my output will be "Are all pad retaining clips fitted?" instead of mcq5?

The sql used at the moment is as follows;
SELECT e.*, d.f_name f_name, d.l_name l_name, d.email email, d.phone phone, d.ip ip, d.score score, d.max_score max_score, d.user_id user_id, ( SELECT COUNT( id ) FROM wp_fsqm_direct_11 r WHERE r.ref = e.id ) AS revision FROM wp_fsqm_direct_12 e LEFT JOIN wp_fsq_data d ON e.data_id = d.id WHERE ref = 0 ORDER BY updated desc LIMIT 0,20

Which i know i can modify with ALIAS, but this is so time consuming and an easier way surely is possible. I have searched all over and cannot find a suitable answer.
Alias code i can use would be like:
SELECT e.mcq0 `New Name 1`, e.mcq2 `New Name 2`, e.mcq3 `New Name 3`, e.mcq4 `New Name 5`, e.mcq5`Are all pad retaining clips fitted?`, e.mcq6 `etc`, e.mcq7 `etc`, e.mcq8, e.mcq9, e.mcq10, e.mcq11, e.mcq12, e.mcq13, e.freetype0, e.freetype1, e.freetype20, e.freetype22, e.freetype21, e.freetype23, e.freetype30, e.freetype31, e.freetype32, e.freetype33, e.freetype34, e.freetype35, e.freetype36, e.freetype37, e.freetype38, e.freetype39, e.freetype41, e.freetype42, e.freetype28, e.pinfo0, e.pinfo1, e.pinfo2, d.f_name f_name, d.l_name l_name, d.email email, d.phone phone, d.ip ip, d.score score, d.max_score max_score, d.user_id user_id FROM wp_fsqm_direct_11 e LEFT JOIN wp_fsq_data d ON e.data_id = d.id ORDER BY updated desc LIMIT 0,20

The select all columns e.* tag at the beginning is the part that needs modifying to get the full question name and not the short table name. I just dont understand why they are saved as just a comment on the original table.

A little long winded i know, but what a pain aliasing everything seperately is. Any help is greatly appreciated.

 

[smozgur]

This is not something that I would do in a production database as it is not a very good idea. But if it has to be done in MySQL directly instead using a scripting language, then perhaps a stored procedure can help:

Basically, getting the column comments from he information_schema, and creating the aliased SQL string by using these information. Then use a prepared statement to execute the SELECT query.
I didn't use any parameters, in or out, but just to give an idea.

BEGIN

DECLARE finished INTEGER DEFAULT 0;
DECLARE columnWithAlias varchar(200) DEFAULT "";
DECLARE result varchar(1000) DEFAULT "";
DECLARE dbName varchar(50) DEFAULT "database_name";
DECLARE tblName varchar(50) DEFAULT "table_name";

DECLARE curColumns
    CURSOR FOR
        SELECT CONCAT(COLUMN_NAME, ' AS `',IF(COLUMN_COMMENT='',COLUMN_NAME, COLUMN_COMMENT),'`')
            FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_SCHEMA = dbName
                  AND TABLE_NAME = tblName;
          
    DECLARE CONTINUE HANDLER
    FOR NOT FOUND SET finished = 1;        
    OPEN curColumns;
    getColumn: LOOP
        FETCH curColumns INTO columnWithAlias;
        IF finished = 1 THEN
            LEAVE getColumn;
        END IF;
        IF result = '' THEN
            SET result = columnWithAlias;
        ELSE
            SET result = CONCAT(result, ',', columnWithAlias);
        END IF;
        
    END LOOP getColumn;
    CLOSE curColumns;
    
    SET @sql = CONCAT('SELECT ', result, ' FROM ', dbName, '.', tblName);
    
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END