<a href="example.cart.3.php?qty=1&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_idfor the dummy customer. Theorder_idis then assigned to the session variableorder_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, theitem_idis saved so that the quantity of the item can be updated. If it isn't in the cart, a newitem_idis 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");
?>