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 wines?those with a description that isn't NULL?are 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 Example 18-2. As explained 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.txt">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="{SCRIPT}?qty={QTY}&amp;wineId={WINE_ID}">{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 colors?maroon, silver, and gray?are 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.