How to display a MySQL table completely?

Question

I am trying to display the whole contents of a MySQL table using PHP. I want the code to display the table completely and it should work for any table that has been created in my database.

I have tried this, but it doesn't work.

<html>
<table>
<tr>
    <th>Col 1</th>
    <th>Col 2</th>
    <th>Col 3</th>
</tr>
<?php
        require "config.php";
        $strSQL = "SELECT * FROM MyGuests" or die(mysql_error($db));
        $rs = mysqli_query($db, $strSQL);
        while($row = mysqli_fetch_array($rs)) {
            echo "\t<tr><td>".$row['col1data']."</td> 
<td>".$row['col2data']."</td><td>".$row['col3data']."</td></tr>\n";
        }
        mysqli_close($db);
?>
</table>
</html>

It returns Col 1 Col 2 Col 3.

`mysql_error` will not work here...you can't mix mysqli_ and mysql_ code — ADyson, Jun 04 '19 at 20:14
Try to indicate the type of array you want in ```$row```: ```while($row = mysqli_fetch_array($rs,MYSQLI_ASSOC))```. — wasanga7, Jun 04 '19 at 20:20
Also the `or die...` code should be on the same line as the `mysqli_query`...that's the thing which can fail. Creating a string in PHP will not cause a SQL error. — ADyson, Jun 04 '19 at 20:20

ADyson · Accepted Answer · 2019-06-04T20:27:59.693

You mentioned

it should work for any table...

If you want to display all columns without knowing in advance what the columns will be, then you need to loop through them instead of naming them explicitly, e.g.

while($row = mysqli_fetch_array($rs, MYSQLI_ASSOC)) {
  echo "<tr>";
  foreach ($row as $col)
    echo "<td>".$col."</td>";
  }
  echo "</tr>";
}

N.B. You might also want to get the column names in advance so you can create the correct number of table header cells. See https://stackoverflow.com/a/1526722/5947043 for suggestions on how to do that.

How to display a MySQL table completely?

1 Answers1