Dynamically assign alias to all the field names in

问题:

I have 2 tables in mysll DB. Both tables have few fixed and few dynamic columns (Fields / Schema). I want to join both these tables with following query:

SELECT *
FROM `cd` cd
LEFT JOIN cd_n cn ON cd.id = cn.fk_cd

And I want to result as

CD_Column1   CD_Column1   CD_Column3   ...... CN_Column1   CN_Column2   CN_Column3  .....
value        value        value        ...... value        value        value       ...       
value        value        value        ...... value        value        value       ...       

Where ..... is dynamic column names of both the tables. So the case is I dont know the column names because they are dynamic and I want rename (alias) it on query level. Please let me know how can I do this?

回答1:

You would need to query the information_schema to get the column names of that two tables. Lets assume You would have the cd column names stored in the array $cd_columns and the cd_n column names in the array $cdn_columns.

Then in PHP when creating the query loop through the column arrays and do something like this:

$sql = 'SELECT ';

// add the cd columns
$i = 0;
foreach($cd_columns as $col) {
    $sql .= "{$col} AS CD_Column{$i},";
    $i++;
}

// add the cd_n columns
$i = 0;
foreach($cdn_columns as $col) {
    $sql .= "{$col} AS CN_Column{$i},";
    $i++;
}

// remove the trailing comma
$sql = trim($sql, ',');
// continue the SQL
$sql .= ' FROM ...';

Was this helpful?