Web Database Applications with PHP and MySQL, 2nd Edition

The Shopping Cart Implementation

In Chapter 16, we introduced the requirements of the winestore shopping cart. A shopping cart is analogous to an incomplete order, in which each item in the cart is one or more bottles of a particular wine. Users can select any wine that is in stock to add to the cart, and wines in the cart can be purchased for up to one day after they have been added. The quantities of the wines can be updated by the user, and items in the cart can be deleted. In addition, the entire cart can be emptied.

We use the orders and items tables in the winestore database to manage the shopping cart. The orders table stores the date and time that the cart was created and a unique identifier for the cart. The items table stores the wine identifiers (wine_id values) of the wines in the cart, the quantity of each wine, and the price that the user has been offered (which is the cheapest price from any of the inventories for that wine). The tables have the following structure:

CREATE TABLE items (
  cust_id int(5) NOT NULL,
  order_id int(5) NOT NULL,
  item_id int(3) NOT NULL,
  wine_id int(4) NOT NULL,
  qty int(3),
  price decimal(5,2),
  PRIMARY KEY (cust_id,order_id,item_id)
) type=MyISAM;

CREATE TABLE orders (
  cust_id int(5) NOT NULL,
  order_id int(5) NOT NULL,
  date timestamp(12),
  PRIMARY KEY (cust_id,order_id)
) type=MyISAM;

We've omitted three attributes from the orders table that are only used in the ordering and shipping module discussed in Chapter 19. Also, for the shopping cart, the ...