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 cust_id attribute is ignored: we set it to -1 for all shopping carts in both the orders and items tables so that we can distinguish shopping carts from actual customers. (In Chapter 19, we explain how to convert a shopping cart into an order, a process that involves a customer taking ownership of a shopping cart.) An alternative way to implement a shopping cart would have been to have two additional tables, say, cart and cart_items, but this isn't necessary if you set the cust_id to -1.
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 unique order_id. The order_id allocated to the user's cart is 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 its contents.
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 its contents. Consider, for example, the contents of the fourth shopping cart with an order_id of 4:
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 quantity (qty) of five bottles of two different wines in her cart.
Using database tables for shopping cart management is a good solution. Alternative approaches to managing shopping carts include using only PHP sessions and JavaScript on the client. The JavaScript approach is the least desirable because (as discussed in Chapter 9) JavaScript and the client should be considered 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 F, but the approach is still likely to be less efficient than dedicated database tables.
Throughout the rest of this section, we outline how the cart is implemented. Automatic emptying of the cart if the user doesn't proceed with the order within 24 hours is discussed in Chapter 15.
Example 18-3 shows the cart/showcart.php script, which displays the contents of the shopping cart.
<?php // This script shows the user the contents of their shopping cart require_once "DB.php"; require_once "../includes/winestore.inc"; require_once "../includes/template.inc"; set_error_handler("customHandler"); // Show the user the contents of their cart function displayCart($connection, &$template) { // If the user has added items to their cart, then // the variable order_no will be registered if (isset($_SESSION["order_no"])) { // Set the action of the <form> $template->setVariable("S_UPDATECART", S_UPDATECART); // Find the items in the cart $cartQuery = "SELECT qty, price, wine_id, item_id FROM items WHERE cust_id = -1 AND order_id = {$_SESSION["order_no"]}"; $result = $connection->query($cartQuery); if (DB::isError($result)) trigger_error($result->getMessage( ), E_USER_ERROR); $cartAmount = 0; $cartCount = 0; // Go through each of the wines in the cart while ($row = $result->fetchRow(DB_FETCHMODE_ASSOC)) { // Keep a running total of the number of items // and dollar-value of the items in the cart $cartCount += $row["qty"]; $lineTotal = $row["price"] * $row["qty"]; $cartAmount += $lineTotal; $template->setCurrentBlock("item"); $template->setVariable("QUANTITY_NAME", $row["item_id"]); $template->setVariable("QUANTITY_VALUE", $row["qty"]); $template->setVariable("WINE", showWine($row["wine_id"], $connection)); $template->setVariable("ITEM_PRICE", sprintf("%-.2f", $row["price"])); $template->setVariable("TOTAL_VALUE", sprintf("%-.2f", $lineTotal)); $template->parseCurrentBlock("item"); } $template->setCurrentBlock("cart"); $template->setVariable("TOTAL_ITEMS", $cartCount); $template->setVariable("TOTAL_COST", sprintf("%-.2f", $cartAmount)); $template->parseCurrentBlock("cart"); } else { // The user has not put anything in the cart $template->setCurrentBlock("emptycart"); $template->setVariable("TEXT", "Your cart is empty"); $template->parseCurrentBlock("emptycart"); } } session_start( ); $template = new winestoreTemplate(T_SHOWCART); $connection = DB::connect($dsn, true); if (DB::isError($connection)) trigger_error($connection->getMessage( ), E_USER_ERROR); // Show the contents of the shopping cart displayCart($connection, $template); $template->showWinestore(SHOW_ALL, B_ALL & ~B_SHOW_CART & ~B_PASSWORD & ~B_DETAILS); ?>
The body of the script is the displayCart( ) function, which queries and displays the contents of the shopping cart. The function checks if the cart has contents by testing for the presence of the session variable $_SESSION["order_no"]. If it's registered, its value is the order_id associated with the shopping cart, and the following query is executed:
$cartQuery = "SELECT qty, price, wine_id, item_id FROM items WHERE cust_id = -1 AND order_id = {$_SESSION["order_no"]}";
The query retrieves the items in the user's cart, and the items are then displayed in an HTML table using the showcart.tpl template shown in Example 18-4. The function showWine( ) that returns the textual details of a wine is part of the winestore.inc include file and is discussed in Chapter 16.
<!-- BEGIN cart --> <h1>Your Shopping Cart</h1> <form action="{S_UPDATECART}" method="GET"> <table border="0" cellpadding="0" cellspacing="5"> <tr> <th>Quantity</th> <th>Wine</th> <th>Unit Price</th> <th>Total</th> </tr> <!-- BEGIN item --> <tr> <td><input type="text" size=3 name="{QUANTITY_NAME}" value="{QUANTITY_VALUE}"></td> <td>{WINE}</td> <td>${ITEM_PRICE}</td> <td>${TOTAL_VALUE}</td> </tr> <!-- END item --> <tr></tr> <tr> <td><b>{TOTAL_ITEMS} items</b></td> <td></td> <td></td> <td><b>${TOTAL_COST}</b></td> </tr> </table> <input type="submit" name="update" value="Update Quantities"> </form> <!-- END cart --> <!-- BEGIN emptycart --> <h1><font color="red">{TEXT}</font></h1> <!-- END emptycart -->
The quantities of each item in the cart are displayed within the table as input elements of a form. For example, consider the following HTML fragment that represents the second item in a user's cart:
<tr> <td><input type="text" size=3 name="2" value="12"></td> <td>1998 Macdonald Hill Wines Archibald Muscat</td> <td>$5.17</td> <td>$62.04</td> </tr>
When rendered in a browser, this item displays a quantity of 12 bottles that can be edited by the user. The name of the input element is name="2", which means it's the second item in the cart. This matches the value of the item_id for the wine that's stored in the shopping cart's items table.
The input widget supports updates to the wine quantities as follows. If the user changes the quantity to 13 bottles and requests the cart/updatecart.php script to update the quantities, then the name and new value are passed to the script with the GET method as the string 2=13. This is then used to update the cart's second item to a quantity of 13 bottles. We discuss this process in more detail later in Section 18.3.4.
Example 18-5 shows the cart/addtocart.php script, which adds items to the shopping cart. The script expects two parameters: a wineId that matches a wine_id in the wine table and a qty (quantity) of the wine to add to the cart. These parameters are supplied by clicking on embedded links on the homepage or the search page discussed in Chapter 19. For example, the homepage discussed earlier in this chapter contains links such as:
<a href="/wda2-winestore/cart/addtocart.php?qty=1&wineId=191"> Add a bottle to the cart</a>
When the user clicks on the link, the cart/addtocart.php script adds a bottle of the wine to the cart, database processing occurs, and the user is redirected to the calling page; the redirection doesn't work on all browsers (such as old versions of Internet Explorer) and we've included a work-around that redirects to the home page only when this problem occurs. This use of one-component querying for adding wines to the cart is discussed in more detail in Chapter 6.
The script in Example 18-5 has several steps:
It checks whether the shopping cart exists. If it does exist, it locks the items table for writing and the inventory table for reading. If the cart doesn't exist, the orders table is also locked for writing.
Locking is required because the script may suffer from the dirty read and lost update concurrency problems discussed in Chapter 8. These problems can occur if, for example, another user is simultaneously creating a shopping cart (without locking, both users may obtain the same cart number).
After locking the required tables, the script tests whether a cart already exists. If it doesn't exist, it is created as a new row in the orders table with the next available order_id. The order_id is then assigned to the session variable $_SESSION["order_no"]. If the cart does exist, the script checks if the item being added to the cart is already one of the items in the cart. If it is, the item_id is saved so that the quantity of the item can be updated. If it isn't in the cart, the next available item_id is assigned to the new wine.
If this is a new item being added to the cart, the script queries to find the cheapest inventory price for the wine. An error is reported if the wine has sold out by registering a message as a session variable; messages are displayed by all scripts that interact with the user through a call to the showMessage( ) method discussed in Chapter 12. It's unusual for wines to sell out: that occurs only if another user purchases all the remaining stock of a wine before this user clicks on the embedded link.
After all checks of the cart and the inventory, the cart item is updated or inserted.
The table locks are released.
Finally, the script redirects to the calling page (or to the home page if the calling page can't be found), completing the one-component add-to-cart script.
<?php // This script adds an item to the shopping cart // It expects a WineId of the item to add and a // quantity (qty) of the wine to be added require_once "DB.php"; require_once "../includes/winestore.inc"; set_error_handler("customHandler"); // Have the correct parameters been provided? if (empty($_GET["wineId"]) || empty($_GET["qty"])) { $_SESSION["message"] = "Incorrect parameters to addtocart.php"; header("Location: {$_SERVER["HTTP_REFERER"]}"); exit; } session_start( ); $connection = DB::connect($dsn, true); if (DB::isError($connection)) trigger_error($connection->getMessage( ), E_USER_ERROR); $wineId = pearclean($_GET, "wineId", 5, $connection); $qty = pearclean($_GET, "qty", 3, $connection); $update = false; // If the user has added items to their cart, then // the variable $_SESSION["order_no"] will be registered // First, decide on which tables to lock // We don't touch orders if the cart already exists if (isset($_SESSION["order_no"])) $query = "LOCK TABLES inventory READ, items WRITE"; else $query = "LOCK TABLES inventory READ, items WRITE, orders WRITE"; // LOCK the tables $result = $connection->query($query); if (DB::isError($result)) trigger_error($result->getMessage( ), E_USER_ERROR); // Second, create a cart if we don't have one yet // or investigate the cart if we do if (!isset($_SESSION["order_no"])) { // Find out the maximum order_id, then // register a session variable for the new order_id // A cart is an order for the customer with cust_id = -1 $query = "SELECT max(order_id) FROM orders WHERE cust_id = -1"; $result = $connection->query($query); if (DB::isError($result)) trigger_error($result->getMessage( ), E_USER_ERROR); // Save the cart number as order_no // This is used in all cart scripts to access the cart $row = $result->fetchRow(DB_FETCHMODE_ASSOC); $_SESSION["order_no"] = $row["max(order_id)"] + 1; // Now, create the shopping cart $query = "INSERT INTO orders SET cust_id = -1, order_id = {$_SESSION["order_no"]}"; $result = $connection->query($query); if (DB::isError($result)) trigger_error($result->getMessage( ), E_USER_ERROR); // Default the item_id to 1 $item_id = 1; } else { // We already have a cart. Check if the customer already // has this item in their cart $query = "SELECT item_id, qty FROM items WHERE cust_id = -1 AND order_id = {$_SESSION["order_no"]} AND wine_id = {$wineId}"; $result = $connection->query($query); if (DB::isError($result)) trigger_error($result->getMessage( ), E_USER_ERROR); // Is the item in the cart already? if ($result->numRows( ) > 0) { $update = true; $row = $result->fetchRow(DB_FETCHMODE_ASSOC); // Save the item number $item_id = $row["item_id"]; } // If this is not an update, find the next available item_id if ($update == false) { // We already have a cart, find the maximum item_id $query = "SELECT max(item_id) FROM items WHERE cust_id = -1 AND order_id = {$_SESSION["order_no"]}"; $result = $connection->query($query); if (DB::isError($result)) trigger_error($result->getMessage( ), E_USER_ERROR); $row = $result->fetchRow(DB_FETCHMODE_ASSOC); // Save the item number of the new item $item_id = $row["max(item_id)"] + 1; } } // Third, add the item to the cart or update the cart if ($update == false) { // Get the cost of the wine // The cost comes from the cheapest inventory $query = "SELECT count(*), min(cost) FROM inventory WHERE wine_id = {$wineId}"; $result = $connection->query($query); if (DB::isError($result)) trigger_error($result->getMessage( ), E_USER_ERROR); $row = $result->fetchRow(DB_FETCHMODE_ASSOC); // This wine could have just sold out - check this // (this happens if another user buys the last bottle // before this user clicks "add to cart") if ($row["count(*)"] == 0) // Register the error as a session variable // This message will then be displayed back on // page where the user adds wines to their cart $_SESSION["message"] = "Sorry! We just sold out of this great wine!"; else { // We still have some of this wine, so save the // cheapest available price $cost = $row["min(cost)"]; $query = "INSERT INTO items SET cust_id = -1, order_id = {$_SESSION["order_no"]}, item_id = {$item_id}, wine_id = {$wineId}, qty = {$qty}, price = {$cost}"; } } else $query = "UPDATE items SET qty = qty + {$qty} WHERE cust_id = -1 AND order_id = {$_SESSION["order_no"]} AND item_id = {$item_id}"; // Either UPDATE or INSERT the item // (Only do this if there wasn't an error) if (empty($_SESSION["message"])) { $result = $connection->query($query); if (DB::isError($result)) trigger_error($result->getMessage( ), E_USER_ERROR); } $result = $connection->query("UNLOCK TABLES"); if (DB::isError($result)) trigger_error($result->getMessage( ), E_USER_ERROR); // HTTP_REFERER isn't set by some browsers. If it isn't, then // redirect to the main page. if (isset($_SERVER["HTTP_REFERER"])) header("Location: {$_SERVER["HTTP_REFERER"]}"); else header("Location: " . S_MAIN); ?>
Example 18-6 lists the cart/emptycart.php script that empties the shopping cart. The script is again a one-component module that carries out its actions, produces no output, and then redirects to the calling page. The script removes the row in the orders table and any rows in the items table that have an order_id equal to the value of the session variable $_SESSION["order_no"]. It then deletes the session variable itself, thus completing the emptying of the cart.
<?php // This script empties the cart and deletes the session variable require_once "DB.php"; require_once "../includes/winestore.inc"; set_error_handler("customHandler"); // Initialise the session - this is needed before // a session can be destroyed session_start( ); // Is there a cart in the database? if (isset($_SESSION["order_no"])) { $connection = DB::connect($dsn, true); if (DB::isError($connection)) trigger_error($connection->getMessage( ), E_USER_ERROR); // First, delete the order $query = "DELETE FROM orders WHERE cust_id = -1 AND order_id = {$_SESSION["order_no"]}"; $result = $connection->query($query); if (DB::isError($result)) trigger_error($result->getMessage( ), E_USER_ERROR); // Now, delete the items $query = "DELETE FROM items WHERE cust_id = -1 AND order_id = {$_SESSION["order_no"]}"; $result = $connection->query($query); if (DB::isError($result)) trigger_error($result->getMessage( ), E_USER_ERROR); // Finally, destroy the session variable unset($_SESSION["order_no"]); } else $_SESSION["message"] = "There is nothing in your cart."; // HTTP_REFERER isn't set by some browsers. If it isn't, then // redirect to the main page. if (isset($_SERVER["HTTP_REFERER"])) header("Location: {$_SERVER["HTTP_REFERER"]}"); else header("Location: " . S_MAIN); ?>
The cart/updatecart.php script, which updates the quantities of items in the shopping cart, is shown in Example 18-7. The script is requested by the cart/showcart.php script and expects GET method parameters of item_id and update quantity pairs. For example, consider the following request for the script:
http://localhost/updatecart.php?1=12&2=13&3=6&update=Update+Quantities
This requests that the quantity of the first item in the cart be updated to 12 bottles, the second item to 13 bottles, and the third item to 6 bottles. The update parameter at the end of the URL is ignored.
The script works as follows:
It untaints the user data using the pearclean( ) function and assigns the results into the array parameters.
It uses the foreach loop statement to iterate through each parameter. For each parameter, it checks to ensure that the item_id and the quantity are both numbers of less than four or three digits in length, respectively. If this test fails, a message is registered as a session variable and displayed after the script redirects back to the cart/showcart.php script.
If the final quantity of the wine is zero, the item is deleted from the cart.
If the final quantity is non-zero, the quantity is updated to the value passed as a parameter.
If the cart is now empty (which happens if all items are set to zero quantities) the cart is deleted by removing the cart row from the orders table.
The script redirects to the cart/showcart.php script.
<?php // This script updates quantities in the cart // It expects parameters of the form XXX=YYY // where XXX is a wine_id and YYY is the new // quantity of that wine that should be in the // cart require_once "DB.php"; require_once "../includes/winestore.inc"; set_error_handler("customHandler"); session_start( ); $connection = DB::connect($dsn, true); if (DB::isError($connection)) trigger_error($connection->getMessage( ), E_USER_ERROR); // Clean up the data, and save the results in an array foreach($_GET as $varname => $value) $parameters[$varname] = pearclean($_GET, $varname, 4, $connection); // Did they want to update the quantities? // (this should be true except if the user arrives here unexpectedly) if (empty($parameters["update"])) { $_SESSION["message"] = "Incorrect parameters to " . S_UPDATECART; header("Location: " . S_SHOWCART); exit; } // If the user has added items to their cart, then // the session variable order_no will be registered // Go through each submitted value and update the cart foreach($parameters as $itemName => $itemValue) { // Ignore the update variable if ($itemName != "update") { // Does this item's name look like a wine_id? if (ereg("^[0-9]{1,4}$", $itemName)) { // Is the update value a number? if (ereg("^[0-9]{1,3}$", $itemValue)) { // If the number is zero, delete the item if ($itemValue == 0) $query = "DELETE FROM items WHERE cust_id = -1 AND order_id = {$_SESSION["order_no"]} AND item_id = {$itemName}"; else // otherwise, update the value $query = "UPDATE items SET qty = {$itemValue} WHERE cust_id = -1 AND order_id = {$_SESSION["order_no"]} AND item_id = {$itemName}"; $result = $connection->query($query); if (DB::isError($result)) trigger_error($result->getMessage( ), E_USER_ERROR); } // if (ereg("^[0-9]{1,3}$", $itemValue)) else $_SESSION["message"] = "A quantity is non-numeric or an incorrect length."; } // if (ereg("^[0-9]{1,4}$", $itemName)) else $_SESSION["message"] = "A wine identifier is non-numeric or an incorrect length."; } // if ($itemName != "update") } // foreach($parameters as $itemName => $itemValue) // The cart may now be empty. Check this. $query = "SELECT count(*) FROM items WHERE cust_id = -1 AND order_id = {$_SESSION["order_no"]}"; $result = $connection->query($query); if (DB::isError($result)) trigger_error($result->getMessage( ), E_USER_ERROR); $row = $result->fetchRow(DB_FETCHMODE_ASSOC); // Are there no items left? if ($row["count(*)"] == 0) { // Delete the order $query = "DELETE FROM orders WHERE cust_id = -1 AND order_id = {$_SESSION["order_no"]}"; $result = $connection->query($query); if (DB::isError($result)) trigger_error($result->getMessage( ), E_USER_ERROR); unset($_SESSION["order_no"]); } // Go back to the cart header("Location: " . S_SHOWCART); ?>