A PHP Application: The Wedding Gift Registry - Learning MySQL - page 504

same time, and they might have selected the same gift first. This gift would then be
marked as taken in the database, but the first user wouldn't know this, because the list
of gifts they loaded in their browser was generated when the gift was still free. This is
another example of where defensive programming is needed; when developing for the
Web, each script is independent, and there are no time limits or controls in our appli-
cation on when a user can request a script.
If we grant the LOCK TABLES privilege to the MySQL user fred , we can use locks for this
part of the code:
1. Run a query to apply a write lock to the gifts table; this prevents any changes to
the data in this table until we release the write lock:
// Lock the gifts table for writing
$query = "LOCK TABLE gifts WRITE";
// Run the query through the connection
if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
2. Read the username associated with the specified gift_id :
// Create a query to retrieve the gift.
$query = "SELECT * FROM gifts WHERE gift_id = {$gift_id}";
// Run the query through the connection
if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
3. If the username associated with the gift is not empty and is not the same as the
current user, create a message to tell the user that the gift has just been taken:
// Has someone already reserved this? (a race condition)
if (!empty($row["username"]) && $row["username"] != $_SESSION['username'])
// Yes. So, record a message to show the user
$message = "Oh dear... Someone just beat you to that gift!";
else
{
// No...reserve the gift for this user
}
4. If the check shows that the gift is still free, we add the current user's username to
the gift data to indicate that the gift has been reserved:
// No. So, create a query that reserves the gift for this user
$query = "UPDATE gifts SET username = '{$_SESSION['username']}' ".
"WHERE gift_id = {$gift_id}";
// Run the query through the connection
if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
5. Finally, unlock the gifts table:
$query = "UNLOCK TABLES";
// Run the query through the connection
if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);