Web Database Applications With Php And Mysql (2nd Edition) [Electronic resources] نسخه متنی

18.2 The Winestore Home Page

Example 18-1,

later in this section,

lists the code for the home page of
the online winestore. The code outputs the following from the
winestore database:

Information about the three most-recently added wines that have been
reviewed, including the vintage year, the winery, the wine name, and
the varieties.

A review of the wine that's been written by a wine
writer.

The price of a bottle and of a dozen bottles of the wine.

To produce this information, the script queries the
wine, winery, and
inventory tables. These tables were created
using the following statements:

CREATE TABLE wine (
wine_id int(5) NOT NULL,
wine_name varchar(50) NOT NULL,
wine_type int(2) NOT NULL,
year int(4) NOT NULL,
winery_id int(4) NOT NULL,
description blob,
PRIMARY KEY (wine_id),
KEY name (wine_name),
KEY winery (winery_id)
) type=MyISAM;
CREATE TABLE winery (
winery_id int(4) NOT NULL,
winery_name varchar(100) NOT NULL,
region_id int(4) NOT NULL,
PRIMARY KEY (winery_id),
KEY name (winery_name),
KEY region (region_id)
) type=MyISAM;
CREATE TABLE inventory (
wine_id int(5) NOT NULL,
inventory_id int(3) NOT NULL,
on_hand int(5) NOT NULL,
cost decimal(5,2) NOT NULL,
date_added date,
PRIMARY KEY (wine_id,inventory_id)
) type=MyISAM;

The wine table stores details about the wines
that are available in the winestore, and includes a
winery_id that's used to
reference the winery that makes the wine in the
winery table. The winery
table describes wineries. The wine table also
includes a wine_type that references the
wine_type lookup table that contains a general
class of the wine such as red or white. It's also
related to the grape_variety table via the
wine_variety table, and this is used to maintain
the grape varieties that make up the wine.

The inventory table stores information about
stock at the winestore. Each wine can have more than one entry, and
so the primary key is a combination of the wine_id
and an inventory_id. For the winestore home page,
the important fields in the inventory table are
the cost of the wine, the
on_hand quantity available, and when the inventory
was added to the database (date_added).

The three tables are used in a moderately complex join query in the
function showPanel( ) in Example 18-1:

   $query = "SELECT  wi.winery_name, w.year, w.wine_name, w.wine_id,
w.description
FROM wine w, winery wi, inventory i
WHERE w.winery_id = wi.winery_id
AND w.wine_id = i.wine_id
AND w.description IS NOT NULL
GROUP BY w.wine_id
ORDER BY i.date_added DESC LIMIT 3";

The query finds the details of the three most-recently stocked wines
that have been reviewed by a wine writer. The query uses table
aliases, as discussed in Chapter 15, so that the
query is more compact.

The WHERE clause joins together the
wine, winery, and
inventory tables, and ensures only reviewed
winesthose with a description that
isn't NULLare returned.
The GROUP BY clause is needed because, without it,
the query returns one row for each inventory of a wine and so, if a
wine had multiple inventories, the wine would appear multiple times.
The ORDER BY clause 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.

Two external functions are called in the showPanel(
) function. The function showVarieties( )
displays the varieties of a specific wine and
showPricing( ) is used to discover the cheapest
bottle price of a wine. Both are part of the
winestore.inc file discussed in Chapter 16.

Example 18-1. The index.php script that displays the winestore home page

<?php
// This is the home page of the online winestore
require_once "DB.php";
require_once "includes/winestore.inc";
require_once "includes/template.inc";
set_error_handler("customHandler");
function showPanel($connection, &$template)
{
// Find the hot new wines
$query = "SELECT  wi.winery_name, w.year, w.wine_name, w.wine_id,
w.description
FROM wine w, winery wi, inventory i
WHERE w.winery_id = wi.winery_id
AND w.wine_id = i.wine_id
AND w.description IS NOT NULL
GROUP BY w.wine_id
ORDER BY i.date_added DESC LIMIT 3";
// Run the query on the database through
// the connection
$result = $connection->query($query);
if (DB::isError($result))
trigger_error($result->getMessage( ), E_USER_ERROR);
// Process the three new wines
while ($row = $result->fetchRow(DB_FETCHMODE_ASSOC))
{
// Add the wine details to the template
$template->setCurrentBlock("row");
$template->setVariable("YEAR", $row["year"]);
$template->setVariable("WINERY", $row["winery_name"]);
$template->setVariable("WINE", $row["wine_name"]);
$template->setVariable("DESCRIPTION", $row["description"]);
$template->setVariable("VARIETIES",
showVarieties($connection, $row["wine_id"]));
$price = showPricing($connection, $row["wine_id"]);
$template->setVariable("BOTTLE_PRICE", sprintf("%.2f", $price));
$template->setVariable("DOZEN_PRICE", sprintf("%.2f", ($price*12)));
// Add a link to add one wine to the cart
$template->setCurrentBlock("link");
$template->setVariable("SCRIPT", S_ADDTOCART);
$template->setVariable("QTY", "1");
$template->setVariable("WINE_ID", $row["wine_id"]);
$template->setVariable("STRING", "Add a bottle to the cart");
$template->parseCurrentBlock("link");
// Add a link to add a dozen wines to the cart
$template->setVariable("SCRIPT", S_ADDTOCART);
$template->setVariable("QTY", "12");
$template->setVariable("WINE_ID", $row["wine_id"]);
$template->setVariable("STRING", "Add a dozen");
$template->parseCurrentBlock("link");
$template->setCurrentBlock("row");
$template->parseCurrentBlock("row");
}
}
// ---------
session_start( );
$template = new winestoreTemplate(T_HOME);
$connection = DB::connect($dsn, true);
if (DB::isError($connection))
trigger_error($connection->getMessage( ), E_USER_ERROR);
showPanel($connection, $template);
// Add buttons and messages, and show the page
$template->showWinestore(SHOW_ALL, B_ALL & ~B_UPDATE_CART &
~B_HOME & ~B_PASSWORD &
~B_PURCHASE & ~B_EMPTY_CART);
?>

The home page is produced using the template shown in Chapter 16, winestore templates are included at
runtime in the template winestore.tpl skeleton
that's used for all winestore pages; this
functionality is part of the constructor of the
winestoreTemplate class that's
also discussed in Chapter 16.

Example 18-2. The index.tpl home page template

<h1>Here are some Hot New Wines!</h1>
<table width="60%">
<tr>
<td><i>Hugh and Dave's Online Wines is not really a winestore.
It's an application that demonstrates the concepts of web database
applications, and is downloadable source code that you can use freely
under this <a href=">license</a>. It pretends to
give customers from around the world the opportunity to buy over
1000 wines that come from more than 300 wineries throughout
Australia.</i>
</td>
</tr>
</table>
<table border=0>
<!-- BEGIN row -->
<tr>
<td bgcolor="maroon"><b><font color="white">
{YEAR} {WINERY} {WINE} {VARIETIES}</font></b>
</td>
</tr>
<tr>
<td bgcolor="silver"><b>Review: </b>{DESCRIPTION}
</td>
</tr>
<tr>
<td bgcolor="gray">
<b>Our price: </b>${BOTTLE_PRICE} (${DOZEN_PRICE} a dozen)
</td>
</tr>
<tr>
<td align="right">
<!-- BEGIN link -->
<a href=">{STRING}</a>&nbsp;
<!-- END link -->
</td>
</tr>
<tr>
<td></td>
</tr>
<!-- END row -->
</table>

The template page is structured using an HTML table environment to
achieve distinct presentation of the three components for each wine:
the details, the review, and the price. It has the following
features:

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

Different background colorsmaroon, silver, and grayare
set for each table row.

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

A blank row follows the wine for spacing in the presentation.

An embedded link follows each wine that supports parameters being
passed to the
cart/addtocart.php

script.

The row block in the template contains the
placeholders that describe the wine and this is output three times by
the script. It also contains a link block that
produces an embedded link to the one-component querying script
cart/addtocart.php that adds wines to the
shopping cart. The link block is output twice by the script for each
wine: once to produce a link to add one bottle to the shopping cart,
and again to produce a link to add a dozen bottles to the cart.

The link block itself has four placeholders:
SCRIPT is for the name of the script to request,
QTY is the quantity of wine to add,
WINE_ID is the unique identifier of the wine, and
STRING is the textual link description to show the
user.