How to get the columns names along with resultset in php/mysql?

Question

Is this OK ?

$i = 0;
while ($row = mysql_fetch_array($result))
{
    $resultset[] = $row;
    $columns[] = mysql_fetch_field($result, $i);
}

Then when trying to print

<tr><th><?php echo $columns[0] ?></th><th><?php echo $columns[1] ?></th></tr>

I got an error

Catchable fatal error: Object of class stdClass could not be converted to string

I notice that $i is not incremented, always having value 0. Also, `$row` and `$resultset` are unused in this example, and `$result` is not defined within the scope of the example. — Ewan Todd, Dec 05 '09 at 19:10

Atli · Accepted Answer · 2009-12-05T19:13:21.777

Try the mysql_fetch_field function.

For example:

<?php
$dbLink = mysql_connect('localhost', 'usr', 'pwd');
mysql_select_db('test', $dbLink);

$sql = "SELECT * FROM cartable";
$result = mysql_query($sql) or die(mysql_error());

// Print the column names as the headers of a table
echo "<table><tr>";
for($i = 0; $i < mysql_num_fields($result); $i++) {
    $field_info = mysql_fetch_field($result, $i);
    echo "<th>{$field_info->name}</th>";
}

// Print the data
while($row = mysql_fetch_row($result)) {
    echo "<tr>";
    foreach($row as $_column) {
        echo "<td>{$_column}</td>";
    }
    echo "</tr>";
}

echo "</table>";
?>

score 16 · Answer 2 · answered Dec 05 '09 at 19:02

16

Use mysql_fetch_assoc to get only an associative array and retrieve the column names with the first iteration:

$columns = array();
$resultset = array();
while ($row = mysql_fetch_assoc($result)) {
    if (empty($columns)) {
        $columns = array_keys($row);
    }
    $resultset[] = $row;
}

Now you can print the head of your table with the first iteration as well:

echo '<table>';
$columns = array();
$resultset = array();
while ($row = mysql_fetch_assoc($result)) {
    if (empty($columns)) {
        $columns = array_keys($row);
        echo '<tr><th>'.implode('</th><th>', $columns).'</th></tr>';
    }
    $resultset[] = $row;
    echo '<tr><td>'.implode('</td><td>', $rows).'</td></tr>';
}
echo '</table>';

answered Dec 05 '09 at 19:02

Gumbo

643,351
109
780
844

1

You could also remove the if and do $columns = isset($resultset[0])? array_keys($resultset[0]): array(); outside of the loop :) – Carlos Lima Dec 05 '09 at 19:11
Hi thanks, great idea, better than to use mysql_fetch_field field but I voted for the post below as it is direct answer to my question :) – programmernovice Dec 05 '09 at 19:26
Still after 10plus years a nice solution with the `implode`. – Timo Feb 26 '20 at 12:40

score 8 · Answer 3 · answered Dec 05 '09 at 19:00

8

You want to look at

 mysql_fetch_assoc

Which gives each row as an associative key => value pair where the key is the column name.

Documentation here

answered Dec 05 '09 at 19:00

Doug T.

64,223
27
138
202

score 1 · Answer 4 · answered Feb 25 '20 at 10:43

Here is a more modern solution with ...i:

$db_link = @mysqli_connect($server, $user, $pass, $db) or die("Connection failed");
$spalten = ['pc_name', 'pc_type', 'pc_snr', 'pc_bj', 'mo_port_1', 'mo_snr_1', 'mo_bj_1', 'mo_port_2', 'mo_snr_2', 'mo_bj_2'];

$abfrage = "SELECT " . implode(',', $spalten) . " FROM hardware order by pc_name";

$liste = mysqli_query($db_link, $abfrage);
$spalten = mysqli_num_fields($liste);


while ($werte[] = mysqli_fetch_array($liste, MYSQLI_ASSOC)) {}
    <table class="display" id="table" style="width:100%">
        <thead>
            <tr>
                <?php
                for ($i = 0; $i < $spalten; $i++) {
                    $feldname[$i] = mysqli_fetch_field_direct($liste, $i)->name;
                    echo '<th>' . ucfirst($feldname[$i]) . '</th>';
                }
                ?>
            </tr>
        </thead>
        <tbody>
            <?php
            for ($i = 0; $i < mysqli_num_rows($liste); $i++) {
            ?>
                <tr>
                    <?php for ($j = 0; $j < $spalten; $j++) {
                    ?>
                        <td><?php
                            echo $werte[$i][$feldname[$j]];
                            ?>

                        </td>
                    <?php } ?>
                </tr>
            <?php } ?>
        </tbody>
    </table>

score 0 · Answer 5 · answered Feb 10 '16 at 23:47

0

Though it's deprecated and no longer in PHP 7 you can avoid having to use an object by using the function mysql_field_name instead which returns a string.

answered Feb 10 '16 at 23:47

Tom Mulkins

431
4
8

How to get the columns names along with resultset in php/mysql?

5 Answers5

Linked