We use the orders and items tables to manage the shopping cart. Alternative approaches include using only PHP sessions, JavaScript on the client, and database tables designed specifically for shopping cart management. The JavaScript approach is the least desirable because-as discussed in Chapter 7-JavaScript and the client are unreliable. PHP sessions are a practical, simple solution, but storing data in disk files results in unnecessary disk activity and relies on the operating system to manage I/O efficiently. The default disk file session store can be replaced with a MySQL session store, as discussed in Appendix D, but the approach is still likely to be less efficient than purpose-built database tables. Designing database tables specifically for shopping-cart management is a good solution, but-as we discuss next-it is unnecessary in the winestore application.
We use the orders and items tables as follows. When a user adds an item to his initially empty shopping cart, a new row is inserted into the orders table with a cust_id of -1 and the next available order_id for this customer. A cust_id of -1 distinguishes between the shopping carts in the winestore and the actual customers: actual customers have cust_id values of 1 or greater, while all shopping carts share the cust_id of -1.
The order_id allocated to the user's cart is then stored as a session variable. The existence of the session variable is used throughout the cart scripts to indicate that the shopping cart has contents, and the value of the variable is used as a key to retrieve the contents. The only practical difference between a completed order and a shopping cart is that in the latter, the customer number is -1, signifying that the items are in a shopping cart and not yet part of an actual order.
Shopping carts can be inspected using the MySQL command interpreter. First, you can inspect how many active shopping carts there are by checking the orders tables:
mysql> SELECT order_id, date
FROM orders WHERE cust_id = -1;
+----------+--------------+
| order_id | date |
+----------+--------------+
| 1 | 011210060918 |
| 2 | 011210061534 |
| 3 | 011210061817 |
| 4 | 011210063249 |
+----------+--------------+
4 rows in set (0.00 sec)
Having found that there are four shopping carts active in the system, you can inspect any cart to check their contents. Consider, for example, the contents of the fourth shopping cart:
mysql> SELECT item_id, wine_id, qty, price
FROM items
WHERE cust_id = -1
AND order_id = 4;
+---------+---------+------+-------+
| item_id | wine_id | qty | price |
+---------+---------+------+-------+
| 1 | 624 | 4 | 22.25 |
| 2 | 381 | 1 | 20.86 |
+---------+---------+------+-------+
2 rows in set (0.00 sec)
From this simple inspection, we know there are four shopping carts, and the owner of the fourth cart has a total of five bottles of two different wines in her cart.
Throughout the rest of this section, we outline how the cart is implemented in PHP and how the cart is updated and emptied. We discuss converting a cart to an order in Chapter 12. Chapter 13 discusses other related topics including how the cart can be automatically emptied if the user doesn't proceed with the order within 24 hours.