The script in Example 6-9 is designed to reward loyal customers. If the customer has spent a significant amount on an order at the winestore, a percentage discount is applied to her order. The function updateDiscount( ) forms the body of the script. It takes as parameters a cust_id, an order_id for that customer, a discount to apply to that order, and a threshold total. If the total amount spent by the user exceeds the threshold total, the discount is applied to the order.
Example 6-9. The updateDiscount function in which locking is required
function updateDiscount($custId, $orderId,
$discount, $minimum,
$connection)
{
$ok = false;
// Lock all tables required in this transaction
$query = "LOCK TABLES items READ,
orders WRITE, customer READ";
if (!mysql_query($query, $connection))
showerror( );
// Run query to find out how much a user
// has spent in this purchase
$query = "SELECT SUM(price*qty)
FROM items, orders, customer
WHERE customer.cust_id =
orders.cust_id
AND orders.order_id = items.order_id
AND items.cust_id = orders.cust_id
AND orders.order_id = $orderId
AND customer.cust_id = $custId";
if (!($result = mysql_query($query, $connection)))
showerror( );
// Get the $row with the total spent
$row = mysql_fetch_array($result);
// Is the amount spent more than the threshold?
if ($row["SUM(price*qty)"] > $minimum)
{
// Yes, so give the customer a discount
// for this order
$query = "UPDATE orders
SET discount = $discount
WHERE cust_id = $custId
AND order_id = $orderId";
if (!mysql_query($query, $connection))
showerror( );
$ok = true;
}
// Unlock the tables
$query = "UNLOCK TABLES";
if (!mysql_query($query, $connection))
showerror( );
// Return whether the discount was given or not
return $ok;
}
The locking of items, orders, and customer is performed before the query, and the UNLOCK TABLES statement is issued after the database update of the discount. As discussed in the last section, all tables and aliases that are used must be locked for either READ or WRITE. MySQL reports an error if, for example, items is accessed but not locked while orders and customer were locked. If an unlocked table needs to be accessed-or locking must be avoided for a particular table-a second DBMS connection can be opened and used.