Let's consider an example that presents results in an HTML <table> environment. Example 4-6 shows a script to query the winestore database and present the details of wines. Previously, in Example 4-5, the details of wines were displayed by wrapping the output in HTML <pre> tags. The script in Example 4-6 uses the function displayWines( ) to present the results as an HTML <table>. The main body of the script has a similar structure to previous examples, with the exceptions that the query is stored in a variable, and the username, password, and the showerror( ) function are stored in separate files and included in the script with the include directive. We introduced the include directive in Chapter 2 and discuss it in more detail later in this section.
The displayWines( ) function first outputs a <table> tag, followed by a table row <tr> tag with six <th> header tags and descriptions matching the six attributes of the wine table. We could have output these using mysql_fetch_field( ) to return the attribute names rather than hardcoding the heading names. However, in most cases, the headers are hardcoded because attribute names are less meaningful to users than manually constructed textual descriptions.
Example 4-6. Producing simple <table> output with MySQL
<!DOCTYPE HTML PUBLIC
"-//W3C//DTD HTML 4.0 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Wines</title>
</head>
<body>
<?php
include 'error.inc';
include 'db.inc';
// Show the wines in an HTML <table>
function displayWines($result)
{
echo "<h1>Our Wines</h1>\n";
// Start a table, with column headers
echo "\n<table>\n<tr>\n" .
"\n\t<th>Wine ID</th>" .
"\n\t<th>Wine Name</th>" .
"\n\t<th>Type</th>" .
"\n\t<th>Year</th>" .
"\n\t<th>Winery ID</th>" .
"\n\t<th>Description</th>" .
"\n</tr>";
// Until there are no rows in the result set,
// fetch a row into the $row array and ...
while ($row = @ mysql_fetch_row($result))
{
// ... start a TABLE row ...
echo "\n<tr>";
// ... and print out each of the attributes
// in that row as a separate TD (Table Data).
foreach($row as $data)
echo "\n\t<td> $data </td>";
// Finish the row
echo "\n</tr>";
}
// Then, finish the table
echo "\n</table>\n";
}
$query = "SELECT * FROM wine";
// Connect to the MySQL server
if (!($connection = @ mysql_connect($hostname,
$username,
$password)))
die("Cannot connect");
if (!(mysql_select_db("winestore", $connection)))
showerror( );
// Run the query on the connection
if (!($result = @ mysql_query ($query, $connection)))
showerror( );
// Display the results
displayWines($result);
// Close the connection
if (!(mysql_close($connection)))
showerror( );
?>
</body>
</html>
After producing the HTML <table> open tag, the displayWines( ) function retrieves the rows in the result set, showing each row as a separate <table> row using the <tr> tag. Each attribute value for each wine-where the attributes match the headings-is displayed within the row as <table> data using the <td> tag. Carriage returns and tab characters are used to lay out the HTML for readability; this has no effect on the presentation of the rendering of the document by a web browser, but it makes the HTML much more readable if the user views the HTML source.
The results of using a <table> environment instead of <pre> tags are more structured and more visually pleasing. The output in a Netscape browser is shown in Figure 4-2, along with a window showing part of the HTML source generated by the script.
Figure 4-2. Presenting wines from the winestore in an HTML <table> environment
