How to Get the Columns Names Along with Resultset in PHP/Mysql

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

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>";
?>

How to get column names from the result of a select query using mysqli

This is not a complete working snippet, but instead a nod in the right direction.

$sql = "SELECT * FROM clients WHERE id = 1";
$res = $mysqli->query($sql);
$row = $res->fetch_assoc();
$colNames = array_keys($row);

Now $row contains an associative array with all column names and variables for the first selected row. If you only want the column names, you have them in $colNames and you're done.

To process all the data use

while ($row = $res->fetch_assoc()) {
    ...
}

Getting ResultSet column names

The Creole drivers for MySQL use the "classic" MySQL functions, and you can do this too. With mysql_fetch_field() you can get information about a specific field, even if no rows were returned. You only need to pass the correct result resource, and you can get that via the getResource() method of your ResultSet.

How to get column names and values from a single-row resultset with PDO?

No need to do array_search(), do like below:-

function story($id) {
    global $db;
    $sql = "select *  from users where id = :aid limit 1";
    $st = $db->prepare($sql);
    $st -> execute([":aid" => $id]);
    $row = $st->fetch(PDO::FETCH_ASSOC);
    if(count($row)>=1){
      foreach ($row as $column => $value) {
        echo "<div class='title'>" . $column . "</div>\n" .
        echo "<div class='story'>" . $value. "</div>\n";
      }
    }
}

how to get the mysql columns names including the right result values in one loop

You need to iterate once more in the inner loop to get all rows of the table's columns.

while($rowvalue = mysql_fetch_row($selecttable)) {       
        foreach($rowvalue as $r){
        echo "<tr><td> ".$r."</td></tr>";
        }
  }

$rowvalue[$i] showed rows incorrectly because it's following index based on the outer loop which is $i.
So basically the loop will print all rows of each column for n-times where n=number of all columns, not just the price column.
.

And you can also print all elements per-$rowvalue at once with :

while($rowvalue = mysql_fetch_row($selecttable)) {       
            $allElements = implode(",",$rowvalue ); // comma ',' is the separator
            echo "<tr><td>". $allElements."</td></tr>";
            }
      }

get column names from stored procedure for displaying in html table using php

The question has nothing to do with stored procedures, as it's applicable to any data returned from database.

the most convenient way would be to fetch the data first, get the column names from the first row and then print the data.

besides, use of prepare execute is not justified for this query

$data = $db->query( "CALL spalldetails" )->fetchAll(PDO::FETCH_ASSOC);
$column_names = array_keys($data[0]);

here you have your column names before output which you can make using foreach() instead of while()

Retrieve the column names from an empty MySQL query result

For PDO, try PDOStatement->getColumnMeta(). For mysqli, there's mysqli_result->fetch_fields(), mysqli_result->fetch_field() and mysqli_result->fetch_field_direct(). For mysql, replace it with PDO or mysqli.

---

Related Topics