After entering valid credit card details and optional delivery instructions, the next step in the ordering process is to turn the shopping cart into an order that's owned by a customer. This requires several steps that include checking that sufficient inventory is available to complete the order and deducting the wines sold from the inventory.
The order finalization requires locking of the database and is an example of the moderately complex query processing that described in Chapter 8. Locking prevents several undesirable problems. For example, locking prevents the same inventory being purchased by two users at the same time. It also prevents unlikely problems such as the same user finalizing two separate orders at the same time in two browser windows and confusing the application.
The script order/order-step3.php shown in Example 19-3, later in this section, performs the ordering process. The script works as follows:
It tests that the user is logged in (using the sessionAuthenticate( ) function discussed in Chapter 20) and that the cart has contents. These tests should never fail, as the Make Purchase button is shown only when the user is viewing the cart, is logged in, and the cart has contents. If either test fails, an error message is registered, and the script redirects to the cart/showcart.php script.
The inventory, items, and orders tables are locked for writing, and the users and customer tables are locked for reading. The inventory, items, and orders tables are all updated in the purchasing process, and they must be locked because the inventory is first checked to ensure that sufficient quantities of wine are available and then later updated.
Without locking, it's possible for another user to purchase the wine while this script is running, resulting in more wine being sold than is in stock. This is an example of the dirty read concurrency problem discussed in Chapter 8, and locking must be used to avoid the problem.
Each item in the cart is then processed, and the inventory is checked to ensure that enough wine is available. If no wine is available (the count( ) of the matching inventory rows is zero) an error message is registered. Similarly, if less wine is available than the user wants (the sum( ) of the on_hand quantity of the matching rows is less than the shopping cart qty) an error message is also registered. On error, the script also updates the shopping cart so that the quantity (qty) of wine in the cart matches the quantity that is on_hand.
In the case of an error, the script uses the function showWine( ) to find the details of the wine. It opens its own connection to the DBMS so that the wine, wine_variety, winery, and grape_variety tables don't need to be locked for reading in the order/order-step3.php script. The function is part of winestore.inc and is discussed in Chapter 16.
If the inventory checks succeed, the script proceeds to convert the shopping cart to be a customer order. This process is straightforward, and is encapsulated in the next three steps.
The first step of converting a cart to an order is to determine the customer's cust_id from the $_SESSION["loginUsername"] variable using the function getCust_id( ) that's described in Chapter 16.
The second step is to find the maximum order_id for this customer so that the next order_id can be assigned to the new order.
The third and final step is to update the orders and items rows by replacing the cust_id of -1 with the customer's cust_id (obtained in Step 5) and the order_id with the next available order_id (obtained in Step 6) for this customer.
After the database has been updated, the cart is emptied using unset($_SESSION["order_no"]); to remove the session variable.
Having completed the order and checked the inventory, the script finishes the ordering process by reducing the inventory. This can never fail, because all required tables are locked and we've checked that sufficient quantities are available.
The process is similar to checking the cart as described in Step 3: we iterate through each item and, for each one, we update the inventory. The inventories are processed from oldest to newest. Consider an example in which the user wants to purchase 24 bottles of a wine. Suppose there are two inventories of this wine: the first has 13 bottles and was added in May 2000; the second has 25 bottles and was added in September 2001. To satisfy the order, the oldest inventory of 13 bottles is emptied and deleted, and the second inventory is reduced by 11 bottles.
With the process complete, the tables are unlocked. If there are no errors, the script redirects to the order/order-step4.php script to confirm the order, and the cust_id and order_id are passed as GET method parameters. If there are errors, the user is returned to the cart view page, cart/showcart.php.
<?php // This script finalizes a purchase // It expects that a cart has contents and that the // user is logged in require_once "DB.php"; require_once "../includes/winestore.inc"; require_once "../includes/authenticate.inc"; set_error_handler("customHandler"); session_start( ); // Connect to a authenticated session sessionAuthenticate(S_SHOWCART); // Check that the cart isn't empty if (!isset($_SESSION["order_no"])) { $_SESSION["message"] = "Your cart is empty!"; header("Location: " . S_SHOWCART); exit; } $connection = DB::connect($dsn, true); if (DB::isError($connection)) trigger_error($connection->getMessage( ), E_USER_ERROR); // Several tables must be locked to finalize a purchase. $query = "LOCK TABLES inventory WRITE, orders WRITE, items WRITE, users READ, customer READ"; $result = $connection->query($query); if (DB::isError($result)) trigger_error($result->getMessage( ), E_USER_ERROR); // Process each wine in the cart and find out if there is sufficient // stock available in the inventory $query = "SELECT * FROM items WHERE cust_id = -1 AND order_id = {$_SESSION["order_no"]}"; // initialize an empty error message $_SESSION["message"] = ""; $result = $connection->query($query); if (DB::isError($result)) trigger_error($result->getMessage( ), E_USER_ERROR); // Get the next wine in the cart for ($winesInCart = 0; $winesInCart < $result->numRows( ); $winesInCart++) { $cartRow[$winesInCart] = $result->fetchRow(DB_FETCHMODE_ASSOC); // Is there enough of this wine on hand? $query = "SELECT COUNT(on_hand), SUM(on_hand) FROM inventory WHERE wine_id = {$cartRow[$winesInCart]["wine_id"]}"; $stockResult = $connection->query($query); if (DB::isError($stockResult)) trigger_error($stockResult->getMessage( ), E_USER_ERROR); $on_hand = $stockResult->fetchRow(DB_FETCHMODE_ASSOC); if ($on_hand["COUNT(on_hand)"] == 0) $available = 0; else $available = $on_hand["SUM(on_hand)"]; // Is there more wine in the cart than is for sale? if ($cartRow[$winesInCart]["qty"] > $available) { if ($available == 0) $_SESSION["message"] = "Sorry! We just sold out of " . showWine($cartRow[$winesInCart]["wine_id"], NULL) . "\n<br>"; else $_SESSION["message"] .= "Sorry! We only have {$on_hand["SUM(on_hand)"]} bottles left of " . showWine($cartRow[$winesInCart]["wine_id"], NULL) . "\n<br>"; // Update the user's quantity to match the available amount $query = "UPDATE items SET qty = {$available} WHERE cust_id = -1 AND order_id = {$_SESSION["order_no"]} AND item_id = {$cartRow[$winesInCart]["item_id"]}"; $result = $connection->query($query); if (DB::isError($result)) trigger_error($result->getMessage( ), E_USER_ERROR); } } // for $winesInCart < $result->numRows( ) // We have now checked if there is enough wine available. // If there is, we can proceed with the order. If not, we // send the user back to the amended cart to consider whether // to proceed with the order. if (empty($_SESSION["message"])) { // Everything is ok - let's proceed then! // First of all, find out the user's cust_id and // the next available order_id for this customer. $cust_id = getCust_id($_SESSION["loginUsername"], $connection); $query = "SELECT max(order_id) FROM orders WHERE cust_id = {$cust_id}"; $result = $connection->query($query); if (DB::isError($result)) trigger_error($result->getMessage( ), E_USER_ERROR); $row = $result->fetchRow(DB_FETCHMODE_ASSOC); $newOrder_no = $row["max(order_id)"] + 1; // Now, change the cust_id and order_id of their cart! $query = "UPDATE orders SET cust_id = {$cust_id}, order_id = {$newOrder_no} WHERE order_id = {$_SESSION["order_no"]} AND cust_id = -1"; $result = $connection->query($query); if (DB::isError($result)) trigger_error($result->getMessage( ), E_USER_ERROR); $query = "UPDATE items SET cust_id = {$cust_id}, order_id = {$newOrder_no} WHERE order_id = {$_SESSION["order_no"]} AND cust_id = -1"; $result = $connection->query($query); if (DB::isError($result)) trigger_error($result->getMessage( ), E_USER_ERROR); // Empty the cart unset($_SESSION["order_no"]); // Now we have to update the inventory. // We do this one cart item at a time. // For all items, we know that there *is* // sufficient inventory, since we've checked earlier foreach($cartRow as $currentRow) { // Find the inventory rows for this wine, oldest first $query = "SELECT inventory_id, on_hand FROM inventory WHERE wine_id = {$currentRow["wine_id"]} ORDER BY date_added"; $result = $connection->query($query); if (DB::isError($result)) trigger_error($result->getMessage( ), E_USER_ERROR); // While there are still bottles to be deducted while($currentRow["qty"] > 0) { // Get the next-oldest inventory $row = $result->fetchRow(DB_FETCHMODE_ASSOC); // Is there more wine in this inventory than the user wants? if ($row["on_hand"] > $currentRow["qty"]) { // Reduce the inventory by the amount the user ordered $query = "UPDATE inventory SET on_hand = on_hand - {$currentRow["qty"]} WHERE wine_id = {$currentRow["wine_id"]} AND inventory_id = {$row["inventory_id"]}"; // The user doesn't need any more of this wine $currentRow["qty"] = 0; } else { // Remove the inventory - we sold the remainder to // this user $query = "DELETE FROM inventory WHERE wine_id = {$currentRow["wine_id"]} AND inventory_id = {$row["inventory_id"]}"; // This inventory reduces the customer's required // amount by at least 1, but we need to process more // inventory $currentRow["qty"] -= $row["on_hand"]; } // UPDATE or DELETE the inventory $result = $connection->query($query); if (DB::isError($result)) trigger_error($result->getMessage( ), E_USER_ERROR); } } } else $_SESSION["message"] .= "\n<br>The quantities in your cart have been updated\n."; // Last, UNLOCK the tables $result = $connection->query("UNLOCK TABLES"); if (DB::isError($result)) trigger_error($result->getMessage( ), E_USER_ERROR); // Redirect to the email confirmation page if everything is ok // (supply the cust_id and order_id to the script) // otherwise go back to the cart page and show a message if (empty($_SESSION["message"])) { header("Location: " . S_ORDER_4 . "?cust_id={$cust_id}&order_id={$newOrder_no}"); exit; } else header("Location: " . S_SHOWCART); ?>