Case Study: The Front-Page Panel (Web Database Applications with PHP & MySQL)

$Web Database Applications with PHP \& MySQL$

4.3. Case Study: The Front-Page Panel

In this section, we show how to engineer a front-page panel—we call this the panel. The completed panel was shown in Figure 4-1. We use the techniques discussed so far in this chapter to present more attractive HTML <table> formatted results, to process multiple query results, and to customize the output based on the data retrieved. No significant new concepts are introduced in the case study.

The panel case study is a progressive development of a script to display the details of new wines. We show the following details in the panel:

Information about the three wines most recently added to the database, including the vintage year, the winery, the wine name, and the varieties
The review written by a wine writer
How much a bottle costs, how much a case of a dozen bottles costs, and any per-bottle discount users receive if they purchase a case

To achieve the outcome of a functional and attractive panel, you need to query the wine, winery, inventory, grape_variety, and wine_variety tables. You also need to use the structure of the HTML <table> environment to achieve distinct presentation of the three components—the details, the review, and the price—of each newly added wine. Last, you need some mathematics to calculate any savings for buying a case and present these savings to the user.

The panel component developed in this chapter is the basis of the front page of our online winestore. However, shopping cart features that are not discussed in detail here have been added to the production version shown in Figure 4-1. The finalized code that includes the shopping-cart functionality is discussed further in Chapter 5, and the completed code is presented in Chapter 11.

In engineering the panel, we use the following techniques:

Querying with the MySQL proprietary LIMIT modifier
Using SQL table aliases in querying
Using the HTML <table> environment as a presentation tool
Producing consolidated HTML output from multiple SQL queries
Presenting data based on calculations
Using MySQL functions—especially mysql_fetch_array( )—in practice

Script development is an iterative process of adding features. It is almost always easier to start with the skeleton of a component and progressively add functionality to achieve the final goal. The Web is particularly good for this: a click on the Refresh or Reload buttons in a web browser tests a script, without the need for compilation or processing of other components of the system. Moreover, PHP is good at reporting errors to the browser, and the HTML output can easily be viewed. In most browsers, right-clicking on the HTML document in the web browser window offers the option to view the HTML source.

4.3.1. 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

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>Hugh 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.

4.3.1.1. 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.

4.3.2. Step 2: Adding Varieties to the Panel

To add varieties to the panel, you need two SQL queries in a single script. This next step adds an additional query to find the varieties of a wine, and the consolidated varieties are presented together with the vintage, winery, and wine name.

The second addition to the panel in this step is the calculation and conditional display of results. We introduce a new feature to the panel that calculates the savings in buying a dozen bottles and shows the user the per-bottle saving of buying a case of wine, but only when there is such a saving. We don't deal with the situation where a case costs more than 12 single purchases.

The script showing these two new concepts is in Example 4-10. The script improves on Example 4-9 by removing the first two limitations identified in the last section. The output of Example 4-10 is shown in Figure 4-4.

Figure 4-4. Adding wine varieties and discounts to the panel

Example 4-10. An improved display with varieties and the dozen-bottle discount

<!DOCTYPE HTML PUBLIC
               "-//W3C//DTD HTML 4.0 Transitional//EN"
               "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
  <title>Hugh 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';

   // Print out the varieties for a wineID
   function showVarieties($connection, $wineID)
   {
      // Find the varieties of the current wine,
      // and order them by id
      $query = "SELECT gv.variety
                FROM grape_variety gv,
                     wine_variety wv, wine w
                WHERE w.wine_id = wv.wine_id
                AND wv.variety_id = gv.variety_id
                AND w.wine_id = $wineID
                ORDER BY wv.id";

      // Run the query
      if (!($result = @ mysql_query($query, $connection)))
         showerror( );

      // Retrieve the varieties ...
      while ($row = @ mysql_fetch_array($result))
         // ... and print each one
         echo " " . $row["variety"];
   }

   // ---------

   $query = "SELECT wi.winery_name,
                     i.cost,
                     i.case_cost,
                     w.year,
                     w.wine_name,
                     w.description,
                     w.wine_id
             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"] . " ";

      // Print the varieties for this wine
      showVarieties($connection, $row["wine_id"]);

      echo "</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)";

      // Calculate the saving for 12 or more bottle
      $dozen_saving = $row["cost"] - ($row["case_cost"]/12);

      // If there's a saving, show what it is
      if ($dozen_saving > 0)
         printf(" Save <b>%.2f</b> per bottle when
                  buying a dozen\n", $dozen_saving);

      echo "</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>

4.3.2.1. Adding a second or subsequent query

Often one query isn't enough to gather all the information required for a report or component in a web database application. The panel is a good example: it is difficult to formulate a single query that can retrieve the wine details (wine_name, year, and description), the winery_name, the inventory data (cost and case_cost), and the varieties (from the wine_variety and grape_variety tables).

It is possible to write a single query, but the query needs post-processing to remove duplicate information before presentation. A natural join of wine, winery, inventory, wine_variety, and grape_variety produces one row per variety of each wine. So, for example, a Cabernet Merlot variety wine is two rows in the output, one row for Cabernet and one row for Merlot. The post-processing involves consolidating the two rows into one HTML <table> row for presentation by using an if statement to check that all other values are identical.

In many cases, more than one query is issued to produce a consolidated result. In the case of the panel, the existing query is used to get most of the information (all the data from wine, winery, and inventory). The second query is nested inside the first; that is, for each row retrieved from the result set of the first query, you run the new query to get the varieties. The result is that the script runs four queries: one to retrieve the three wines, and three queries to get their varieties.

Let's return to Example 4-10. The first query has not changed and still returns one row per inventory of each of the most recently added wines that has a written review. For each wine, the script produces a heading showing the year, winery_name, and wine_name.

It is after this query is run and the year, winery_name, and wine_name output that the new functionality of an additional query begins. In this example, a function, showVarieties( ), is called. This function runs a query to find the varieties of a particular wine with a wine_id value that matches the parameter $wineID:

$query = "SELECT gv.variety
          FROM grape_variety gv,
               wine_variety wv, wine w
          WHERE w.wine_id = wv.wine_id
          AND wv.variety_id = gv.variety_id
          AND w.wine_id = $wineID
          ORDER BY wv.id";

For example, the query identifies that the first-listed 1999 Binns Hill Vineyard Morfooney with wine_id=191 is a Cabernet Sauvignon. The results are ordered by wine_variety.id so that, as in previous examples, a Cabernet Merlot can be distinguished from a Merlot Cabernet.

The subsequent processing of the second query follows a similar pattern to the first. A mysql_query( ) retrieves all result rows with mysql_fetch_array( ) and prints out the only attribute retrieved, $row["variety"], the grape variety of the wine. The connection isn't closed because it's needed, later to find the next wine's varieties.

This multiple-query approach is common and is used throughout the winestore; the approach is used in the panel to produce order receipts for presentation and email confirmation, and in many of the stock and customer reports.

4.3.2.2. Adding calculations to the result presentation

Often data that is displayed to the user isn't stored directly in the database. For example, the total of an order placed by the user isn't stored. Instead, the following pieces of information are stored: the quantity of each item ordered, the item's price, the delivery cost, and any discount applied. From these, calculating and displaying the total requires some mathematics.

Why isn't such data stored? The answer is usually that it is redundant: storing it adds no more information to the database. The down side is that you need calculations to recreate output when it is needed. In this section, this is illustrated with a simple example that shows the per-bottle saving when a user purchases more than a dozen bottles.

Returning to the script in Example 4-10, having produced a complete heading that now includes the wine variety, we produce the wine review in the script as before. However, rather than finishing with a simple bottle cost and case_cost, we do some calculations that show users any savings through buying a case:

$dozen_saving = $row["cost"] - ($row["case_cost"]/12);
if ($dozen_saving > 0)
              printf("Save <b>%-.2f</b> per bottle 
              when buying a dozen\n", $dozen_saving);

The element $row["cost"] is the cost of a single bottle, and $row["case_cost"] is the cost of a case. Since a case contains 12 bottles, it follows that the cost of 1 bottle in the case is $row["case_cost"]/12. The difference between the price of a single bottle and the price of bottle that comes in a case is then:

$row["cost"]-($row["case_cost"]/12)

The result is stored in $dozen_saving.

A saving is printed out only if there is one; that is, when $dozen_saving is greater than zero. In the case where buying a dozen bottles at once costs the same as 12 separate purchases (or maybe more!), nothing is shown. printf is used in preference to echo, so that you can include the formatting string %-.2f to show exactly two decimal places (that is, the cents of the $dozen_saving).

There are many examples of calculations that are performed on the raw data from the database to present information to the user in our winestore. These include calculating order totals, discounts, receipt information, delivery charges, and so on. Elementary mathematics is a common component of most web database applications; it's used throughout later examples.

4.3.3. Step 3: Finishing the Panel

We have built a satisfactory component. However, one problem identified earlier still remains. The first-listed wine appears twice. In this case it is because there are two inventory entries for the same wine, with the only difference being the prices. Of course, our user will pick the cheapest.

4.3.3.1. Fixing the queries

To address the inventory problem—where a wine appears multiple times in the front panel if there are multiple inventories of that wine—you need to modify the initial query.

Only one row should be produced per wine, not one per inventory. To do this, remove the inventory table attributes from the SELECT statement and add a DISTINCT to remove the duplicates. However, you can't remove the inventory table fully from the query, because you still need to ORDER BY date_added to display the newest wines added to our winestore cellar. The query is now as follows:

$query = "SELECT wi.winery_name,
                  w.year,
                  w.wine_name,
                  w.description,
                  w.wine_id
          FROM wine w, winery wi, inventory i
          WHERE w.description != \"\"
          AND w.winery_id = wi.winery_id
          AND w.wine_id = i.wine_id
          GROUP BY winde-id
          ORDER BY i.date_added DESC LIMIT 3";

With this modified query, one entry is produced per wine. However, having removed the inventory attributes, you no longer have the pricing information.

You need another query and some script reorganization. Example 4-11 shows a substantially rewritten script that adds a second new function, showPricing( ), that has the correct inventory handling. The function showPricing( ) has a similar structure to showVarieties( ).

showPricing( ) adds the cheapest inventory price to the panel for each wine and uses a new query. The query is:

$query =  SELECT min (cost), min (case_cost)
          FROM inventory 
          WHERE wine_id = $wineID;

Example 4-11. Script with correct inventory handling for the latest wine display

<!DOCTYPE HTML PUBLIC
               "-//W3C//DTD HTML 4.0 Transitional//EN"
               "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
  <title>Hugh 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';

   // Print out the varieties for a wineID
   function showVarieties($connection, $wineID)
   {
      // Find the varieties of the current wine,
      // and order them by id
      $query = "SELECT gv.variety
                FROM grape_variety gv,
                     wine_variety wv, wine w
                WHERE w.wine_id = wv.wine_id
                AND wv.variety_id = gv.variety_id
                AND w.wine_id = $wineID
                ORDER BY wv.id";

      // Run the query
      if (!($result = @ mysql_query($query, $connection)))
         showerror( );

      // Retrieve the varieties ...
      while ($row = @ mysql_fetch_array($result))
         // ... and print each one
         echo " " . $row["variety"];
   }

   // Print out the pricing information
   function showPricing($connection, $wineID)
   {
      // Find the cheapest prices for the wine,
      $query =  SELECT min (cost), min (case_cost)
                FROM inventory
                WHERE wine_id = $wineID

      // Run the query
      if (!($result = @ mysql_query($query, $connection)))
         showerror( );

      // Retrieve the cheapest price
      $row = @ mysql_fetch_array($result);

      // Print the pricing information
      echo "\n<tr>\n\t<td bgcolor=\"gray\">" .
           "<b>Our price: </b>" .
           $row["min(case_cost)"] .
           "(" . $row["min(cost)"] . " a dozen)";

      // Calculate the saving for 12 or more bottle
      $dozen_saving = $row["min(cost)"] - ($row["min(case_cost)"]/12);

      // If there's a saving, show what it is
      if ($dozen_saving > 0)
         printf(" Save <b>%.2f</b> per bottle when
                 buying a dozen\n", $dozen_saving);
      echo "</td>\n</tr>";
   }

   // ---------

   $query = "SELECT wi.winery_name,
                     w.year,
                     w.wine_name,
                     w.description,
                     w.wine_id
             FROM wine w, winery wi, inventory i
             WHERE w.description != \"\"
             AND w.winery_id = wi.winery_id
             AND w.wine_id = i.wine_id
             GROUP BY w.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("winestore", $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"] . " ";

      // Print the varieties for this wine
      showVarieties($connection, $row["wine_id"]);

      echo "</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>";

      // Show the pricing information
      showPricing($connection, $row["wine_id"]);

      // Blank row for presentation
      echo "\n<tr>\n\t<td></td>\n</tr>";
   }

   echo "\n</table>\n";

   if (!mysql_close($connection))
      showerror( );
?>
</body>
</html>

The difference in producing price information is that the code doesn't retrieve all rows in the result set with a loop. Rather, it retrieves only one row—the row representing the cheapest inventory. It then outputs the min(cost) and min(case_cost) as previously, with the same dozen_saving calculation.

The final panel, with correct inventory handling, calculations, and varieties, is shown in Figure 4-5.

Figure 4-5. Panel with correct inventory handling, calculations, and varieties