Columns... retrieving and displaying

[JordanTLClive]

I need to retrieve and display the titles and data from a table... without knowing exactly what columns exist or rows. In other words, the function needs to query the database and format the data into a html table without knowing what fields are in the table.

[oodie]

This should work

$query = "SELECT * FROM table_name";
$result = mysql_query($query);

// get the number of columns
$numofField = mysql_num_fields($result);

echo "<table border=1>\n<tr>\n";

for($i=0; $i<$numofField; $i++){
    // store column name in an array
    $field[$i] = mysql_field_name($result,$i);
    echo "<td>$field[$i]</td>\n";
}

echo "</tr>\n";

while($row = mysql_fetch_array($result)){
  echo "<tr>\n";

  for($i=0; $i<$numofField; $i++) {
      if($row[$field[$i]] == "")
          echo "<td>&nbsp;</td>\n";
      else
          echo "<td>". $row[$field[$i]] ."</td>\n"; 
  }

  echo "</tr>\n";
}

echo "</table>"; 

[7stud]

Hi,

You will need to use mysql_list_tables() to get the table names. For details see:

www.php.net

Here is my version of how to output the data, which I think is a little more efficient and a little more straight forward:

<?php
//Connect to the database:
$connection=mysql_connect("localhost", "", "")
      or die("Couldn't connect to server.");
$db=mysql_select_db("db_name", $connection)
      or die("Couldn't select database.");

//Select the data from the table and get the number of fields:
$sql="SELECT * FROM $table_name";
$sql_result=mysql_query($sql);
$num_fields=mysql_num_fields($sql_result);

echo '<table>';

echo '<tr>';
//Display the field names as column headings:
for ($i=0; $i< $num_fields; $i++)
{
    echo '<th>';
    echo mysql_field_name($sql_result, $i);
    echo '</th>';
}
echo '</tr>';

//Get the individual records from the table:
while ($row=mysql_fetch_row($sql_result))  
{
    echo '<tr>';

    //Display all the fields in the record:
    for ($i=0; $i< $num_fields; $i++)  
    {
        echo '<td>';
        
        if ($row[$i]='';)
        {
            echo '&nbsp;';
        }
        else
        {
            echo $row[$i];
        }

        echo '</td>';
    }

    echo '</tr>';
}

echo '</table>';
?>