CODEWITHSUNDEEP
phpmysql
user3138025
user3138025

Reputation: 825

PHP Select from mysql with column header names

I have the following PHP 5.4 script which selects from MySQL 5.7, and it works fine. 

<?php
$link = new mysqli("localhost","my_username","my_password", "my_schema");
  if (mysqli_connect_errno())
   {
   printf("Connect failed: %s\n", mysqli_connect_error());
  exit();
}
$sugg_query = 
"SELECT 
    s.prim_key, 
    s.created_date,
    s.created_by,
    s.suggestion 
FROM suggestion s;";
if ($sugg_result = mysqli_query($link, $sugg_query))
{
// determine number of rows result set */
    $sugg_row_cnt = mysqli_num_rows($sugg_result);
    printf("Result set has %d rows.\n", $sugg_row_cnt);

    while($sugg_row = mysqli_fetch_array($sugg_result))
    {
    echo "<br />";
    echo 
    $sugg_row['prim_key'] . " " . 
    $sugg_row['created_date'] . " " .
    $sugg_row['created_by'] . " " .
    $sugg_row['suggestion'] . " " ; 
    }
    /* close result set */
    mysqli_free_result($sugg_result);
}
//Close the connection
mysqli_close($link);
//close PHP
?>

I'd like to add column headers to the result. If I change the SELECT statement to the following, I get a result where only the row count line is produced.

$sugg_query = 
"SELECT 
    s.prim_key as 'Key', 
    s.created_date as 'Date',
    s.created_by as 'Source',
    s.suggestion as 'Suggestion' 
FROM suggestion s;";

How can I obtain data values and column headers from a SELECT in PHP?

Upvotes: 0

Views: 1465

Answers (1)

Cesar
Cesar

Reputation: 717

For getting the headers you can iterate a row like:

foreach($sugg_row as $header => $value){
    echo $header;
}

So maybe something like this should work in your code:

$rows = mysqli_fetch_array($sugg_result)
foreach($rows[0] as $header => $value){
    echo $header;
}

You need also to keep in count that mysqli_fetch_array() will return NULL if there is no rows

Upvotes: 1

Related Questions