New Page 1
PHP-Finalizing Orders
When a user finishes adding items to his cart, he usually proceeds to a purchase. Finalizing an order requires several steps that include checking that sufficient inventory is available to complete the order, converting the shopping cart to an order, and deducting the wines sold from the inventory. These tasks require locking of the database and are examples of moderately complex query processing. The script order.3 shown in Example 12-1 performs these tasks.
The script works as follows:
It tests that the user is logged in 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 calling 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 is 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 6, 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 user’s cart qty—an error message is also registered. On error, the script also updates the user’s cart so that the quantity (qty) of wine in the user’s cart matches the quantity that is on_hand. In the case of an error, the script uses the function showWine( ) to show the user the details of the wine. This function 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.
If the inventory checks succeed, the script proceeds to convert the user’s cart to be a customer’s order. This process is straightforward:
Determine the cust_id from the loginUsername session variable using the function getCustomerID( ).
Find the maximum order_id for this customer.
Update the orders and items rows by replacing the cust_id of -1 with the customer’s cust_id and the order_id with the next available order_id for this customer.
After the database has been updated, the cart is emptied using session_unregister( ) to remove the order_no session variable.
Having completed the order and checked the inventory, the script finishes the ordering process by reducing the inventory. This can never fail, since all required tables are locked, and you’ve checked that sufficient quantities are available. The process is similar to checking the cart: you iterate through each item and, for each one, you 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. 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 shipping.1 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.
Example 12-1. order.3 finalizes the user’s purchase
<?php
// This script finalizes a purchase
// It expects that a cart has contents and that the
// user is logged in
include ‘include.inc’;
set_error_handler(“errorHandler”);
// Re-establish the existing session
session_start( );
// Check if a cart exists – this should never fail
// unless the script is run directly
if (!session_is_registered(“order_no”))
{
session_register(“message”);
$message =
“There are no items in your shopping cart!”;
// Redirect the browser back to the calling page
header(“Location: $HTTP_REFERER”);
exit;
}
// Check if the user is logged in – this should
// never fail unless the script is run directly
if (!session_is_registered(“loginUsername”))
{
session_register(“message”);
$message =
“You must login to finalize your purchase.”;
// Redirect the browser back to the calling page
header(“Location: $HTTP_REFERER”);
exit;
}
// Open a connection to the DBMS
if (!($connection = @ mysql_pconnect($hostName,
$username,
$password)))
showerror( );
if (!mysql_select_db($databaseName, $connection))
showerror( );
// Several tables must be locked to finalize a purchase.
// We avoid locking four other tables by
// using another DBMS connection to produce the wine
// information
$query = “LOCK TABLES inventory WRITE,
orders WRITE,
items WRITE,
users READ,
customer READ”;
// LOCK the tables
if (!(@ mysql_query ($query, $connection)))
showerror( );
// 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 = $order_no”;
// Initialise an empty error message
$message = “”;
if (!($result = @ mysql_query ($query, $connection)))
showerror( );
// Get the next wine in the cart
for ($winesInCart = 0;
$winesInCart < mysql_num_rows($result);
$winesInCart++)
{
$cartRow[$winesInCart] = @ mysql_fetch_array($result);
// 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"];
if (!($stockResult = @ mysql_query ($query,
$connection)))
showerror( );
$on_hand = @ mysql_fetch_array($stockResult);
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 (!session_is_registered(“message”))
session_register(“message”);
if ($available == 0)
$message .= “Sorry! We just sold out of ” .
showWine($cartRow[$winesInCart]["wine_id"],
NULL) .
“\n<br>”;
else
$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 = $order_no
AND item_id = ” .
$cartRow[$winesInCart]["item_id"];
if (!(@ mysql_query ($query, $connection)))
showerror( );
}
} // for $winesInCart < mysql_num_rows($result);
// 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 think about purchasing the lesser
// amount.
if (empty($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.
$custID = getCustomerID($loginUsername, NULL);
$query = “SELECT max(order_id)
FROM orders
WHERE cust_id = $custID”;
if (($result = @ mysql_query ($query, $connection)))
$row = mysql_fetch_array($result);
else
showerror( );
$newOrder_no = $row["max(order_id)"] + 1;
// Now, change the cust_id and order_id of their cart!
$query = “UPDATE orders
SET cust_id = $custID , ” .
“order_id = ” . $newOrder_no .
” WHERE order_id = $order_no”;
if (!(@ mysql_query ($query, $connection)))
showerror( );
$query = “UPDATE items
SET cust_id = $custID , ” .
“order_id = ” . $newOrder_no .
” WHERE order_id = $order_no”;
if (!(@ mysql_query ($query, $connection)))
showerror( );
// Officially empty the cart
session_unregister(“order_no”);
// Now we have to do 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 inventories for this wine, oldest first
$query = “SELECT inventory_id, on_hand
FROM inventory
WHERE wine_id = ” .
$currentRow["wine_id"] .
” ORDER BY date_added”;
if (!($result = @ mysql_query ($query,
$connection)))
showerror( );
// While there are still bottles to be deducted
while($currentRow["qty"] > 0)
{
// Get the next-oldest inventory
$row = @ mysql_fetch_array($result);
// 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
if (!(@ mysql_query ($query, $connection)))
showerror( );
}
}
}
else
$message .= “\n<br>The quantities in your cart ” .
“have been updated\n.”;
// Last, UNLOCK the tables
$query = “UNLOCK TABLES”;
if (!(@ mysql_query ($query, $connection)))
showerror( );
// Redirect to the email confirmation page if
// everything is ok
// (supply the custID and orderID to the script)
// otherwise go back to the cart page and show a message
if (empty($message))
{
header(“Location: example.shipping.1.php?” .
“custID=$custID&orderID=$newOrder_no”);
exit;
}
else
header(“Location: example.cart.2.php”);
?>
More PHP Tutorial
