| only for RuBoard - do not distribute or recompile | |
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.
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.
<?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>
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&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.
<?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");
?>
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.
<?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");
?>
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.
<?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 | |