Displaying an HTML table from MySQL data
Wednesday, July 23rd, 2008Here’s one from the 2003 archives:
Did you know that PHP could interface with a MySQL database? Well it can, and here’s how! The code below illustrates just how easy it is to produce a nice table with headers populated with data culled from a database. One little trick I like to use (and have used for a very long time) when displaying table data is to alternate the background color. This makes it a bit easier to read the data (at least it does for me) Notice that when the table is initialized (<table><tr bgcolor=\”#afafaf\”>), I define the background color. Later as the code iterates through the lines of data, I test to see what the current backgound color is, and then reverse it. Not rocket science, but it spruces things up just a bit. I have been using a heavily modified version of this code in several places, whether it is for displaying log files or pulling intrusion reports from a Snort database and it has worked quite well. Any comments or improvements are always welcome!
<?php
$link = mysql_connect("database_host", "user", "password");
mysql_select_db("database_name", $link);
$qry = mysql_query("SELECT * FROM table_name", $link);
echo <table><tr bgcolor=\"#afafaf\">;
if (mysql_num_rows($qry) > 0) {
for ($i = 0; $i<mysql_num_fields($qry); $i++) {
echo "<td><b>" . mysql_field_name($qry, $i) . "</b></td>";
}
}else{
echo "<td>No entries found in the database</td>";
}
echo "</tr>\n";
if (mysql_num_rows($qry) > 0) {
for ($j = 0; $j<mysql_num_rows($qry); $j++) {
if ($bgcolor == "#ffffff"){
$bgcolor="#cccccc";
}else{
$bgcolor="#ffffff";
}
echo "<tr bgcolor=\"$bgcolor\">";
for ($k = 0; $k<mysql_num_fields($qry); $k++) {
echo "<td>" . mysql_result($qry,$j, $k) . "</td>";
}
echo "</tr>\n";
}
echo "</table>";
}
?>
