Generate PHP Code to Display MySQL Table

This chunk of PHP code automatically generates the PHP code to display a MySQL table. All you have to do is change the table name and enter your database settings. This comes in handy so that you can format each column appropriately depending on the data type (dates, emails, etc.) rather than just having to display the data exactly as it's stored in the database.

You can see this code in action by viewing this example. The code that is displayed on the example is what you would cut and paste (for this particular table) into your PHP file and then tinker with as needed.

<?php

$dbaddress = "db.example.com" ;
$dbname = "db1" ;
$dbuser = "username" ;
$dbpw = "password" ;
$tablename = "mytable" ;

$link = mysql_connect($dbaddress, $dbuser, $dbpw);
mysql_select_db($dbname, $link);

echo "<pre>\n" ;
echo htmlspecialchars("echo \"<table>\\n\" ;\n") ;
echo htmlspecialchars("echo \"  <thead>\\n\" ;\n") ;
echo htmlspecialchars("echo \"  <tr>\\n\" ;\n") ;
$query = "SHOW COLUMNS FROM `$tablename`" ;
$result = mysql_query($query) ;
while ($row = mysql_fetch_array($result)) {
    echo htmlspecialchars("echo \"    <td>".$row[0]."</td>\\n\" ;\n") ;
    }
echo htmlspecialchars("echo \"  </tr>\\n\" ;\n") ;
echo htmlspecialchars("echo \"  </thead>\\n\" ;\n") ;
echo htmlspecialchars("echo \"  <tbody>\\n\" ;\n") ;
echo "\$query = \"SELECT * FROM `$tablename`\" ;\n" ;
echo "\$result = mysql_query(\$query,\$link) ;\n" ;
echo "while (\$row = mysql_fetch_array(\$result)) {\n" ;
echo htmlspecialchars("    echo \"  <tr>\\n\" ;\n") ;
$result = mysql_query($query) ;
while ($row = mysql_fetch_array($result)) {
    echo htmlspecialchars("    echo \"    <td>\".\$row['".$row[0]."'].\"</td>\\n\" ;\n") ;
    }
echo htmlspecialchars("    echo \"  </tr>\\n\" ;\n") ;
echo "    }\n" ;
echo htmlspecialchars("echo \"  </tbody>\\n\" ;\n") ;
echo htmlspecialchars("echo \"</table>\\n\" ;\n") ;
echo "</pre>\n\n" ;

mysql_close($link) ;

?>

Last updated on November 8, 2009.