The Shopping Cart Architecture (Web Database Applications with PHP & MySQL)

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:

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:

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. The Shopping Cart Architecture

11.2.1. Viewing the Shopping Cart

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

11.2.2. Adding Items to the Shopping Cart

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

11.2.3. Emptying the Shopping Cart

Example 11-4. cart.4 empties the cart

11.2.4. Updating the Shopping Cart Quantities

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