CODEWITHSUNDEEP
phpmysql
Xavi
Xavi

Reputation: 2594

How to get mysql column names and the values

i am trying to getting a column name and the value dynamically it means if new column is added in database i want to show in front end when page is refresh

The below will get me the column names, but I then need to retrieve the value regarding to the column name.can anyone guide me how to do that thanks.

  <?php

  Include"db.php";


    echo "<table>";
    echo "<tr>";
    $qColumnNames = mysql_query("SHOW COLUMNS FROM fullnames") or die("mysql error"); 


    $numColumns = mysql_num_rows($qColumnNames); 
    $x = 0; 
    while ($x < $numColumns) 
    { 
        $colname = mysql_fetch_row($qColumnNames); 
        $col[$colname[0]] = $colname[0]; 
        $x++; 
    } 

    foreach($col as $head){
        echo "<th>$head</th>";

    }



    echo "</tr>";
    echo "</table>";

    ?>

Upvotes: 2

Views: 3596

Answers (3)

Chinmay235
Chinmay235

Reputation: 3414

Hi Please use this code-

$query = 'select * from fulllnames WHERE id=1';
$result = mysql_query($query);
$i = 0;
$getResult=mysql_fetch_array($result, MYSQL_ASSOC);
while ($i < mysql_num_fields($result)){
    $fld = mysql_fetch_field($result, $i);
    $myarray=$fld->name;
    //Field column name  =  Field value
    echo $myarray.' = '.$getResult[$myarray]."<br>";
    $i++;
}

Upvotes: 0

Alex
Alex

Reputation: 1593

As @Toby Allen asked to format my comment as answer:

...
// Just select absolutely all columns, including last two "dynamic" columns.
$query = $mysqli->query('SELECT * FROM fullnames') or die($mysqli->error);

// Get data and column names together
if ($row = $query->fetch_assoc()) {
  $columns = array_keys($row);
  // Do what you need with $columns
  echo '<tr>';
  foreach ($columns AS $column) {
    echo '<th>' . htmlspecialchars($column) . '</th>';
  }
  echo '</tr>';

  do {
    // Do what you need with rows
    echo '<tr>';
    foreach ($row AS $column => $value) {
      echo '<td>' . htmlspecialchars($value) . '</td>';
    }
    echo '</tr>';
  } while ($row = $query->fetch_assoc());
}

Upvotes: 0

Philip G
Philip G

Reputation: 4104

Could do something like this: (provided your functions for retriving columnname is working)

EDITED CODE 

Include("db.php");


echo "<table>";

$qColumnNames = mysql_query("SHOW COLUMNS FROM fullnames") or die("mysql error"); 


$numColumns = mysql_num_rows($qColumnNames); 
$x = 0; 

while ($x < $numColumns) 
{ 
    $colname = mysql_fetch_row($qColumnNames); 
    $col[$colname[0]] = $colname[0];
    $x++; 
}

$result = mysql_query("SELECT * FROM fullnames");
$counter = 0;
while($row = mysql_fetch_assoc($result)){
     foreach($col as $c){
        $rows[$counter][] = "<td>" . $row[$c] . "</td>";
     }
    $counter++;
}

echo "<tr>";
foreach($col as $c){
    //echo headers
     echo "<th>" . $c . "</th>";
}
echo "</tr>";
foreach($rows as $r){
    //Echo all content
    echo "<tr>";
        foreach($r as $cell){
             //echo all cells
             echo $cell;
        }
    echo "</tr>";
}
echo "</table>";

?>

Haven't had time to test this, but it should work. (but you should use msqli_ functions instead.

Upvotes: 3

Related Questions