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>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"];
}
// 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
