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
LIMITmodifierUsing SQL table aliases in querying
Using the HTML
<table>environment as a presentation toolProducing 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.
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.
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$rowarray can be referenced by attribute name. The resultant code is more readable and more query-independent than ifmysql_fetch_row( )is used.
Manipulating presentation by using structure is, unfortunately, part of working with HTML.
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.
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.
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>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.
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.
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.
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.
Get Web Database Applications with PHP, and MySQL now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
