From MySQL to HTML table

I'm using PHP to generate some HTML. Once estabilished a connection to our MySQL database, I want to perform a select on one of its table and show the result to the user.

Assuming the connection has been successfully estabilished, and that we have its resulting object in $conn, we select all the items from a table getting the resultset in the $result variable:

$query = "select * from events";
($result = mysql_query($query, $conn)) or die("Error accessing database");

The error handling is quite crude: if for any reason the select fails, we just output an error message and terminate the page generation.

Now the fun stuff: we write a function that gets in input the resultset, as returned by a successful call to mysql_query(), and output its data in an HTML table. To make it more readable we alternate yellow rows to light gray ones:

function myPrintAsTable($result)
print "<table><tbody>";

$rows = mysql_num_rows($result); // 1.
$cols = mysql_num_fields($result); // 2.
for($i = 0; $i < $rows; ++$i) // 3.
$row = mysql_fetch_row($result); // 4.
if($i%2) // 5.
print '<tr style="background-color: lightgrey;">';
print '<tr style="background-color: yellow;">';

for($j = 0; $j < $cols; ++$j) // 6.
print "<td>". $row[$j] ."</td>";

print "</tr>";
print "</table></tbody>";

1. mysql_num_rows() extracts from a resultset the number of rows in it.
2. mysql_num_fields() extracts from a resultset its number of fields (or columns, if we think to them in terms of table terms).
3. Loop on all the rows.
4. mysql_fetch_row() reads the next row and moves the cursor ahead, ready for the next fetch.
5. Set the background color for the current table row in alternate colors.
6. Loop on all the columns and put each field in a different table data tag.

If you have, or could have, multiline text among the data in your table, you should think about using the nl2br() function, that converts any newline in HTML <br> tags, so that you can actually display it to the user.

No comments:

Post a Comment