PHP —

Step 1: Producing Visually Appealing Tables

Example 4-9 shows a script that is the first step in producing the panel. Not surprisingly, the script combines the same querying process described earlier with an HTML <table> environment to wrap the output. The output is more attractive than in previous examples and the output in a Netscape browser is shown in Figure 4-3.

Figure 4-3. The first step in producing a front-page panel that shows more attractive presentation
figs/wda_0403.gif

The basis of the script is a moderately complex SQL query that uses table aliases and the LIMIT operator:

SELECT wi.winery_name,
        i.cost,
        i.case_cost,
        w.year,
        w.wine_name,
        w.description
        FROM wine w, winery wi, inventory i
        WHERE w.description != ""
        AND w.winery_id = wi.winery_id
        AND w.wine_id = i.wine_id
        ORDER BY i.date_added DESC LIMIT 3;

The table aliases allow the query to be written concisely. For example, the inventory table can be referenced throughout the query by the single character i.

The query returns one row for each inventory of a wine. If a wine has multiple inventories, the wine appears multiple times. The query also outputs the wine's winery_name, the vintage attribute year, the wine_name, and a descriptive review, description. The WHERE clause ensures that only reviewed wines-those with a description that isn't empty-are returned. The WHERE clause also implements a natural join with the wine table using the primary keys of the winery and inventory tables.

The ORDER BY clause in the SQL query uses the DESC modifier. The date_added isn't an attribute of the wine, it is a value from the latest-added inventory, and the LIMIT 3 ensures only the three latest-added inventories are retrieved.

The include files error.inc and db.inc are included in the script, as discussed in the last section.

Example 4-9. A script to display the three newest wines added to the winestore
<!DOCTYPE HTML PUBLIC
               "-//W3C//DTD HTML 4.0 Transitional//EN"
               "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
  <title>Alexa and Dave's Online Wines</title>
</head>
<body bgcolor="white">
<h1>New Wines</h1>
Here are three top new wines we have in stock
<br><br>
<?php
   include 'db.inc';
   include 'error.inc';
   $query = "SELECT wi.winery_name,
                     i.cost,
                     i.case_cost,
                     w.year,
                     w.wine_name,
                     w.description
             FROM wine w, winery wi, inventory i
             WHERE w.description != \"\"
             AND w.winery_id = wi.winery_id
             AND w.wine_id = i.wine_id
             ORDER BY i.date_added DESC LIMIT 3";
   // Open a connection to the DBMS
   if (!($connection = @ mysql_connect($hostName,
                                       $username,
                                       $password)))
      die("Could not connect to database");
   if (!mysql_select_db($databaseName, $connection))
      showerror(  );
   // Run the query created above on the database through
   // the connection
   if (!($result = @ mysql_query ($query, $connection)))
      showerror(  );
   echo "\n<table border=\"0\">";
   // Process the three new wines
   while ($row = @ mysql_fetch_array($result))
   {
      // Print a heading for the wine
      echo "\n<tr>\n\t<td bgcolor=\"maroon\">" .
           "<b><font color=\"white\">" .
           $row["year"] . " " .
           $row["winery_name"] . " " .
           $row["wine_name"] . " " .
           "</font></b></td>\n</tr>";
      // Print the wine review
      echo "\n<tr>\n\t<td bgcolor=\"silver\">" .
           "<b>Review: </b>" .
           $row["description"] .
           "</td>\n</tr>";
      // Print the pricing information
      echo "\n<tr>\n\t<td bgcolor=\"gray\">" .
           "<b>Our price: </b>" .
           $row["cost"] .
           "(" . $row["case_cost"] . " a dozen)" .
           "</td>\n</tr>";
      // Blank row for presentation
      echo "\n<tr>\n\t<td></td>\n</tr>";
   }
   echo "\n</table>\n";
   if (!mysql_close($connection))
      showerror(  );
?>
</body>
</html>

Besides the moderately complex SQL query, Example 4-9 is only slightly more sophisticated than the examples in previous sections. The code to produce the <table> isn't complex but is a little less readable because:

  • The information for each wine is represented over three table rows using three <tr> tags.

  • Different background colors for the single <td> element are set in each table row <tr>; the colors are maroon, silver, and gray.

  • The color attribute of the <font> tag is set to white for the heading of each wine.

  • The bold tag <b> is used for pricing information.

  • A blank row between wines is used for spacing in the presentation.

  • mysql_fetch_array( ) is used to retrieve rows. This has the advantage that the elements of the $row array can be referenced by attribute name. The resultant code is more readable and more query-independent than if mysql_fetch_row( ) is used.

Manipulating presentation by using structure is, unfortunately, part of working with HTML.

Limitations of Step 1

This code is an incomplete solution to the aims we described in the introduction to the case study. Three particular limitations are:

  • The varieties of the wines are not shown. For example, you can't tell that the first-listed Binns Hill Vineyard Morfooney is a Cabernet Sauvignon variety.

  • The user expects that the dozen price represents a per-bottle saving over purchasing bottles in smaller quantities. However, the front panel doesn't show the saving, and the user needs a calculator to decide whether a dozen bottles is worth the discount.

  • The first-listed wine appears twice. There are two inventory entries for the same wine, and the query has returned two rows for that same wine, with the only difference being the prices.

    Another explanation for a double appearance could be that there are two wines with the same review and year, but with different grape_varieties. This is very unlikely and isn't the case here.

We improve the panel progressively in the next section to address these limitations, while also adding new features.