11.2 The Shopping Cart Architecture

only for RuBoard - do not distribute or recompile

11.2 The Shopping Cart Architecture

In Chapter 1, 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 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.

11.2.1 Viewing the Shopping Cart

Example 11-2 shows the cart.2 script, which displays the contents of the shopping cart. Using the same approach as in Example 11-1, the script displays the user login status, any errors or notices for the user, and a set of buttons to allow the user to request other scripts in the winestore. The body of the script is the displayCart( ) function, which queries and displays the contents of the shopping cart.

displayCart( ) checks if the cart has contents by testing for the presence of the session variable order_no . If order_no is 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 = $order_no";

The query retrieves the items in the user's cart, and the items are then displayed in an HTML <table> environment. The quantities of each item are displayed within the <table> as <input> elements of a <form> . Each element has an associated name attribute that is set to the item_id of the item, and the value of the attribute is set to the quantity of wine in the cart. 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="13"></td> <td>1982 Grehan's Vineyard Galti Cabernet Sauvignon</td> <td>$20.86</td> <td>$271.18</td> </tr>

When rendered in a browser, this item displays a quantity of 13 bottles that can be edited by the user. If the user changes the quantity and clicks on the Update Quantities button, a request is made for the cart.6 script to update the quantities. The request includes the item_id of 2 as the GET method attribute and the new quantity as its value. We discuss the cart.6 script later in this section.

Example 11-2. cart.2 displays the contents of the user's shopping cart


<?php // This script shows the user the contents of // their shopping cart include 'include.inc'; set_error_handler("errorHandler"); // Show the user the contents of their cart function displayCart($connection) { global $order_no; // If the user has added items to their cart, then // the variable order_no will be registered if (session_is_registered("order_no")) { $cartQuery = "SELECT qty, price, wine_id, item_id FROM items WHERE cust_id = -1 AND order_id = $order_no"; // Retrieve the item details of the cart items if (!($result = @ mysql_query($cartQuery, $connection))) showerror( ); $cartAmount = 0; $cartCount = 0; // Create some headings for the cart echo "<table border=\"0\" " . "

cellpadding

=\"0\" cellspacing=\"5\">"; echo "\n<tr>"; echo "\n\t<th>Quantity </th>"; echo "\n\t<th>Wine</th>"; echo "\n\t<th>Unit Price</th>"; echo "\n\t<th>Total</th>"; echo "\n</tr>"; // Go through each of the wines in the cart while ($row = @ mysql_fetch_array($result)) { // 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; // Show the quantity of this item in a text // input widget. The user can alter the quantity // and update it echo "\n<tr>"; echo "\n\t<td>" . "<input type=\"text\" size=3 name=\"" . $row["item_id"] . "\" value = \"" . $row["qty"] . "\"></td>"; // Show the wine details of the item echo "\n\t<td>"; echo showWine($row["wine_id"], $connection); echo "</td>"; // Show the per-

bottle

price printf("\n\t<td>$%.2f</td>", $row["price"]); // Show the total price of this item printf("\n\t<td>$%.2f</td>", $lineTotal); echo "\n</tr>"; } echo "\n<tr></tr>"; // Show the user the total number of bottles // and the total cost of the items in the cart printf("\n<tr>\n\t<td><b>%d items</b></td>", $cartCount); echo "\n\t<td></td>\n\t<td></td>"; printf("\n\t<td><b>$%.2f</b></td>\n</tr>", $cartAmount); echo "\n</table>"; } else { // The session variable $order_no is not // registered. Therefore, the user has not // put anything in the cart echo "<h3><font

color

=\"red\">" . "Your cart is empty</font></h3>"; } } // --------- // Open a connection to the DBMS if (!($connection = @ mysql_pconnect($hostName, $username, $password))) showerror( ); if (!mysql_select_db($databaseName, $connection)) showerror( ); // Initialize a session. This call either creates // a new session or re-establishes an existing one. session_start( ); ?> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html401/loose.dtd"> <html> <head> <title>Hugh and Dave's Online Wines</title> </head> <body

bgcolor

="white"> <?php // Show the user login status showLogin( ); ?> <h1>Your Shopping Cart</h1> <?php // Show the user any messages showMessage( ); ?> <form action="example.cart.5.php" method="GET"> <?php // Show the contents of the shopping cart displayCart($connection); ?> <table> <tr> <td><input type="submit" name="home" value="Home"></td> <?php // If the user has items in their cart, offer the // chance to update quantities or empty the cart or // finalize the purchase (if they're logged in) if (session_is_registered("order_no")) { echo "\n\t<td><input type=\"submit\" " . "name=\"update\" value=\"Update Quantities\"></td>"; echo "\n\t<td><input type=\"submit\" " . "name=\"empty\" value=\"Empty Cart\"></td>"; if (session_is_registered("loginUsername")) echo "\n\t<td><input type=\"submit\" " . "name=\"buy\" value=\"Make Purchase\"></td>"; } // Show the user the search screen button echo "\t<td><input type=\"submit\" " . "name=\"search\" value=\"Search\"></td>\n"; // Show login or logout button loginButtons( ); ?> </tr> </table> </form> <br><a href="http://validator.w3.org/check/referer"> <img src="http://www.w3.org/Icons/valid-html401" height="31" width="88" align="right" border="0" alt="Valid HTML 4.01!"></a> </body> </html>

11.2.2 Adding Items to the Shopping Cart

Example 11-3 shows the cart.3 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 home or search pages. For example, the home page contains links such as:


<a href="example.cart.3.php?qty=1&amp;wineId=624"> Add a bottle to the cart</a>

When the user clicks on the link, the cart.3 script adds a bottle of the wine to the cart, database processing occurs, and the user is redirected back to the calling page. This use of one-component querying for adding wines to the cart is discussed in more detail in Chapter 5.

cart.3 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 since the script may suffer from the dirty read and lost update concurrency problems discussed in Chapter 6. These problems can occur if another user is simultaneously creating a shopping cart—without locking, both users may obtain the same cart number—or if an inventory is sold out while an item is being added to the cart, in which case the item price in the cart may be wrong.
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 for the dummy customer. The order_id is then assigned to the session variable 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, a new 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( ) function incorporated in include.inc . Wines selling out is an unusual occurrence: it 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, completing the one-component add-to-cart script.

Example 11-3. cart.3 adds a quantity of a specific wine to the shopping cart


<?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 include 'include.inc'; set_error_handler("errorHandler"); // Have the correct parameters been provided? if (empty($wineId) && empty($qty)) { session_register("message"); $message = "Incorrect parameters to example.cart.3.php"; // Redirect the browser back to the calling page header("Location: $HTTP_REFERER"); exit; } // Re-establish the existing session session_start( ); $wineId = clean($wineId, 5); $qty = clean($qty, 3); $update = false; // Open a connection to the DBMS if (!($connection = @ mysql_pconnect($hostName, $username, $password))) showerror( ); if (!mysql_select_db($databaseName, $connection)) showerror( ); // If the user has added items to their cart, then // the variable order_no will be registered // First, decide on which tables to lock // We don't touch orders if the cart already exists if (session_is_registered("order_no")) $query = "LOCK TABLES inventory READ, items WRITE"; else $query = "LOCK TABLES inventory READ, orders WRITE, items WRITE"; // LOCK the tables if (!(@ mysql_query ($query, $connection))) showerror( ); // Second, create a cart if we don't have one yet // or investigate the cart if we do if (!session_is_registered("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"; if (!($result = @ mysql_query ($query, $connection))) showerror( ); // Save the cart number as order_no // This is used in all cart scripts to access the cart session_register("order_no"); $row = @ mysql_fetch_array($result); $order_no = $row["max(order_id)"] + 1; // Now, create the shopping cart $query = "INSERT INTO orders SET cust_id = -1, order_id = $order_no"; if (!(@ mysql_query ($query, $connection))) showerror( ); // 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 = $order_no AND wine_id = $wineId"; if (!($result = @ mysql_query ($query, $connection))) showerror( ); // Is the item in the cart already? if (mysql_num_rows($result) > 0) { $update = true; $row = @ mysql_fetch_array($result); // 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 = $order_no"; if (!($result = @ mysql_query ($query, $connection))) showerror( ); $row = @ mysql_fetch_array($result); // 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"; if (!($result = @ mysql_query ($query, $connection))) showerror( ); $row = @ mysql_fetch_array($result); // 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_register("message"); $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 = $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 = $order_no AND item_id = $item_id"; // Either UPDATE or INSERT the item // (Only do this if there wasn't an error) if (empty($message) && (!(@ mysql_query ($query, $connection)))) showerror( ); // Last, UNLOCK the tables $query = "UNLOCK TABLES"; if (!(@ mysql_query ($query, $connection))) showerror( ); // Redirect the browser back to the calling page, // using the HTTP response header "Location:" // and the PHP environment variable $HTTP_REFERER header("Location: $HTTP_REFERER"); ?>

11.2.3 Emptying the Shopping Cart

Example 11-4 lists the cart.4 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 back 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 order_no . It then deletes the session variable itself, thus completing the emptying of the cart.

Example 11-4. cart.4 empties the cart


<?php // This script empties the cart and deletes the session include 'include.inc'; set_error_handler("errorHandler"); // Initialise the session - this is needed before // a session can be

destroyed

session_start( ); // Is there a cart in the database? if (session_is_registered("order_no")) { // Open a connection to the DBMS if (!($connection = @ mysql_connect($hostName, $username, $password))) showerror( ); if (!mysql_select_db($databaseName, $connection)) showerror( ); // First, delete the order $query = "DELETE FROM orders WHERE cust_id = -1 AND order_id = $order_no"; if (!(@ mysql_query ($query, $connection))) showerror( ); // Now, delete the items $query = "DELETE FROM items WHERE cust_id = -1 AND order_id = $order_no"; if (!(@ mysql_query ($query, $connection))) showerror( ); // Finally, destroy the session variable session_unregister("order_no"); } else { session_register("message"); $message = "There is nothing in your cart."; } // Redirect the browser back to the calling page. if (session_is_registered("referer")) { session_unregister("referer"); header("Location: $referer"); exit; } else header("Location: $HTTP_REFERER"); ?>

11.2.4 Updating the Shopping Cart Quantities

The cart.6 script, which updates the quantities of items in the shopping cart, is shown in Example 11-5. The script is requested by the cart.2 script and expects GET method parameters of item_id and update quantity pairs. For example, consider the following request for the script:


http://localhost/example.cart.6.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 script works as follows:

It untaints the user data using the clean( ) function and assigns the results into the array parameters .
It uses the foreach loop statement to iterate through each parameter. For each parameter that isn't the update 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.2 script.
If the quantity of the wine is zero, the item is deleted from the cart.
If the 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 back to the cart.2 script.

Example 11-5. cart.6 updates the quantities of wines in the shopping cart


<?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 include 'include.inc'; set_error_handler("errorHandler"); // Re-establish the existing session session_start( ); // Clean up the data, and save the results // in an array foreach($HTTP_GET_VARS as $varname => $value) $parameters[$varname] = clean($value, 4); // Did they want to update the quantities? // (this should be true except if the user arrives // here unexpectedly) if (empty($parameters["update"])) { session_register("message"); $message = "Incorrect parameters to ". "example.cart.6.php"; // Redirect the browser back to the calling page header("Location: $HTTP_REFERER"); exit; } // Open a connection to the DBMS if (!($connection = @ mysql_connect($hostName, $username, $password))) showerror( ); if (!mysql_select_db($databaseName, $connection)) showerror( ); // If the user has added items to their cart, then // the 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") { // The item's name must look like a wine_id if (ereg("^[0-9]{1,4}$", $itemName)) { // The update value must be 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 = $order_no AND item_id = $itemName"; else //

otherwise

, update the value $query = "UPDATE items SET qty = $itemValue WHERE cust_id = -1 AND order_id = $order_no AND item_id = $itemName"; if (!(@ mysql_query ($query, $connection))) showerror( ); } // if (ereg("^[0-9]{1,3}$", $itemValue)) else { session_register("message"); $message = "There was an error updating " . "your quantities. Try again."; } } // if (ereg("^[0-9]{1,4}$", $itemName)) else { session_register("message"); $message = "There was an error updating " . "quantities. Try again."; } } // 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 = $order_no"; if (!($result = @ mysql_query ($query, $connection))) showerror( ); $row = mysql_fetch_array($result); // Are there no items left? if ($row["count(*)"] == 0) { // Delete the order $query = "DELETE FROM orders WHERE cust_id = -1 AND order_id = $order_no"; if (!(@ mysql_query ($query, $connection))) showerror( ); session_unregister("order_no"); } // Go back to the cart header("Location: example.cart.2.php"); exit; ?>

We have now completed our discussion of the shopping cart implementation. Converting a shopping cart to an order is discussed in Chapter 12. In the next section, we discuss how redirection is managed in the winestore application.

only for RuBoard - do not distribute or recompile