Retrieve column names from MySQL query

Question

I am attempting to retrieve the column headers for html headers from a MySQL query. The structure of my application is such that the column headers aren't static (sometimes there are 4, sometimes 7, etc.) For some unbearable reason I can't find a simple way to get the column names from any given query.

My end goal is to output an html table from the query.

@SuperNoob You realize the answer you marked as correct was written today and is a duplicate of my answer, right? As in, he actually copied and pasted my answer. Check out the mistake I made where I added the code comment saying both foreach statements were column names instead of the lower being the column value... — James, Dec 18 '13 at 19:34
I don't know why people marked this question as duplicate, since it ask a different thing =/ — El Gucs, Apr 24 '16 at 06:43

score 1 · Accepted Answer · answered Dec 18 '13 at 03:30

Well if you get the query as an associative array, you can do something like this:

$query = "Select [stuff]";
$results = mysql_query($query);
$i = 0;

while ($row = mysql_fetch_assoc($results)) {
    if ($i < 1) {
        foreach($row as $key => $val) {
            echo $key;//column name
        }
    }

    //Do other stuff
    $i++;
}

And yes, I know they should be doing mysqli_..., but this is my example using old mysql_...

Edit

To expand on this:

$query = "Select [stuff]";
$results = mysql_query($query);
$i = 0;

echo "<table>";

while ($row = mysql_fetch_assoc($results)) {
    echo "<tr>";
    if ($i < 1) {
        foreach($row as $key => $val) {
            echo "<td>".$key."</td>";//column name
        }
    }
    echo "</tr>";

    echo "<tr>";
    foreach($row as $key => $val) {
        echo "<td>".$val."</td>";//column name
    }
    echo "</tr>";

    //Do other stuff
    $i++;
}

THIS is the correct answer. In it's original form. I had similarly programmed my code, but I was using mysqli_fetch_array — SuperNoob, Dec 18 '13 at 23:39
@SuperNoob Thanks, imagine my surprise when I seen that Rizwan had straight copied my answer. :/ Literally. I appreciate the change. — James, Dec 19 '13 at 03:43

score 0 · Answer 2 · answered Dec 18 '13 at 03:41

0

You can use function mysql_list_fields for getting column names.

answered Dec 18 '13 at 03:41

voodoo417

11,861
3
36
40

score 0 · Answer 3 · answered Dec 18 '13 at 04:11

Code below gets data from sql. Including header column

// Data, depends on your sql statement
$sql_dt = array(); // make sure it is array

foreach( $sql_dt as $key => $val ) {    
$data[] = <<< EOH
  <tr>
    <th>$val['column_name']</th> <tr/>
    <th>$val['column_age']</th>
  <tr/>
EOH;
}

$table_dt = implode( $data );
echo <<< EOT
<table>
  <tr>
    <th>Name</th> <tr/>
    <th>Age</th>
  <tr/>
  {$table_dt}
<table/>
EOT;

Retrieve column names from MySQL query

3 Answers3