Web Database Applications with PHP and MySQL, 2nd Edition

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 ...