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>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';
// 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>