CODEWITHSUNDEEP
phpmysqldrupal-6
kaspnord
kaspnord

Reputation: 1473

Obtain column names from MySQL query object (PHP)

Currently, I'm storing MySQL query results in an array of objects, where each object represents a row that matches the query. Later, I run through two foreach loops to extract the results - in the example below, they are outputted as a dynamically-generated HTML table.

My question: Is it possible to obtain the column names from the query result object? My goal is to be able to dynamically generate the table headings, which my HTML table is currently lacking.

Thanks!

$data = array();

$result = db_query("SELECT column1, column2 FROM table");

while ($obj= db_fetch_object($result)) {
    array_push($data, $obj);
}

$ret = "<table>";
foreach ($data as $row) {
   $ret .= "<tr>";
   foreach ($row as $field) {
      $ret .= "<td>$field</td>";
   }
   $ret .= "</tr>";
}
$ret .= "</table>";

return $ret;

Upvotes: 3

Views: 4151

Answers (3)

El Gucs
El Gucs

Reputation: 967

The question is old, but this is how it can be done now with mysqli_*

//Function to generate a HTML table from a SQL query
function myTable($obConn,$sql)
{
    $rsResult = mysqli_query($obConn, $sql) or die(mysqli_error($obConn));
    if(mysqli_num_rows($rsResult)>0)
    {
        //We start with header
        echo "<table width=\"100%\" border=\"0\" cellspacing=\"2\" cellpadding=\"0\"><tr align=\"center\" bgcolor=\"#CCCCCC\">";
        $i = 0;
        while ($i < mysqli_num_fields($rsResult)){
           $field = mysqli_fetch_field_direct($rsResult, $i);
           $fieldName=$field->name;
           echo "<td><strong>$fieldName</strong></td>";
           $i = $i + 1;
        }
        echo "</tr>";

        //We dump info
        $bolWhite=true;
        while ($row = mysqli_fetch_assoc($rsResult)) {
            echo $bolWhite ? "<tr bgcolor=\"#CCCCCC\">" : "<tr bgcolor=\"#FFF\">";
            $bolWhite=!$bolWhite;
            foreach($row as $data) {
                echo "<td>$data</td>";
            }
            echo "</tr>";
        }
        echo "</table>";
    }
}

Upvotes: 1

Sabari
Sabari

Reputation: 6335

There is function to retreive column name. Please check with this link :

http://php.net/manual/en/function.mysql-fetch-field.php

Upvotes: 3

Michal
Michal

Reputation: 3368

You can use this function http://php.net/manual/en/function.mysql-fetch-field.php

Upvotes: 2

Related Questions