Mysql get column names then results

Question

Say I have a query to get all information in a particular database that could have different schemas.

For example, I may have:
DB1 with [id] [blah] [shibby] &
DB2 with [id] [yadda] [etc] [andsoforth]

Then I run a query which needs to return:
A) the field names into a table header
B) the results into a table cell

The query would be generated from a form via post in order to set some variables to determine which database to get the information from.

The below will get me the column names, but I then need to populate the remainder with the actual results.

echo "<table>";
echo "<tr>";
$qColumnNames = mysql_query("SHOW COLUMNS FROM ".$db) or die("mysql error"); 
$numColumns = mysql_num_rows($qColumnNames); 
$x = 0; 
while ($x < $numColumns) 
{ 
    $colname = mysql_fetch_row($qColumnNames); 
    $col[$colname[0]] = $colname[0]; 
    $x++; 
} 

foreach($col as $key){
    echo "<th>$key</th>";
}
echo "</tr>";
echo "</table>";

Possible existing answers here: http://stackoverflow.com/questions/1526688/get-table-column-names-in-mysql and http://stackoverflow.com/questions/4165195/mysql-query-to-get-column-names — JoLoCo, Apr 04 '13 at 23:46
While both of those will show the column names, they do not populate the results. I need both column names and results based on the column names. — Rik, Apr 04 '13 at 23:56

score 1 · Accepted Answer · answered Apr 04 '13 at 23:47

You say database, but you mean fields from a table? You have the first part - get column names - and then you could implode those into a string from your array and use that in your SELECT query:

$query = "SELECT ".implode(",", $col)." FROM [tablename] ...";

Mysql get column names then results

1 Answers1