In this chapter, we’ve covered the basic techniques for connecting to and reading data from a MySQL DBMS using PHP. In this section, we extend this to writing data.
Example 11-4 shows the
action.php
script that adds and removes gifts from
a guest’s shopping list. The script uses the MySQL
library functions we discussed earlier. If the user-supplied
$action
variable has the value
insert
, an attempt is made to reserve the gift
with the value in $present_id
for the current
guest. If $action
is set to
delete
, an attempt is made to remove the gift from
the guest’s shopping list. As discussed in the
previous section, the guest’s
people_id
is maintained in the
$user
session variable.
The script first checks the status of the gift with the identifier
$present_id
. If the gift is already reserved, the
current guest can’t reserve it; this can happen if
another guest is using the application and beats the current guest to
it. Likewise, the script checks that the gift is reserved by the
current guest before actually unreserving it; this check should never
fail, unless the same user is logged in twice. Defensive programming,
or thinking through all the possibilities that can occur, is wise
when developing for the Web, since each script is independent, and
there are no time limits or controls in our application on when a
user can request a script.
<?php // Add or remove a gift from the user's shopping list // // This script expects two parameters: // (1) The $present_id of the present they'd like to reserve // or remove from their shopping list // (2) The $action to carry out: insert or delete // It carries out its requested action, and then redirects back // to presents.php. This script produces no output. // Include the DBMS credentials include "db.inc"; // Check if the user is logged in // (this also starts the session) logincheck( ); // Secure the user data $present_id = clean($present_id, 5); $action = clean($action,6); // Connect to the MySQL DBMS if (!($connection = @ mysql_pconnect($hostName, $username, $password))) showerror( ); // Use the wedding database if (!mysql_select_db($databaseName, $connection)) showerror( ); // LOCK the presents table for writing $query = "LOCK TABLES presents WRITE"; // Run the query if (!($result = @ mysql_query($query, $connection))) showerror( ); // Create a query to retrieve the gift. $query = "SELECT * FROM presents WHERE present_id = {$present_id}"; // Run the query if (!($result = @ mysql_query($query, $connection))) showerror( ); // Get the matching gift row (there's only one) $row = @ mysql_fetch_array($result); // Does the user want to add a new item to their shopping list? if ($action == "insert") { // Yes, an insert. // Has someone already reserved this? (a race condition) if (!empty($row["people_id"]) && $row["people_id"] != $user) // Yes. So, record a message to show the user $message = "Oh dear... Someone just beat you to that present!"; else { // No. So, create a query that reserves the gift for this user $query = "UPDATE presents SET people_id = "{$user}" WHERE present_id = {$present_id}"; // Run the query if (!($result = @ mysql_query($query, $connection))) showerror( ); // Create a message to show the user if (mysql_affected_rows( ) == 1) $message = "Reserved the present for you, {$user}"; else $message = "There was a problem updating. Please contact the administrator."; } } else { // No, it's a delete action. // Double-check they actually have this gift reserved if (!empty($row["people_id"]) && $row["people_id"] != $user) // They don't, so record a message to show the user $message = "That's not your present, {$user}!"; else { // They do have it reserved. Create a query to unreserve it. $query = "UPDATE presents SET people_id = NULL WHERE present_id = {$present_id}"; // Run the query. if (!($result = @ mysql_query($query, $connection))) showerror( ); // Create a message to show the user if (mysql_affected_rows( ) == 1) $message = "Removed the present from your shopping list, {$user}"; else $message = "There was a problem updating. Please contact the administrator."; } } // UNLOCK the presents table $query = "UNLOCK TABLES"; // Run the query if (!($result = @ mysql_query($query, $connection))) showerror( ); // Redirect the browser back to presents.php header("Location: presents.php?message=" . urlencode($message)); ?>
The script uses the MySQL library function
mysql_affected_rows(
)
.
The function reports the number of rows affected by the most recently
executed SQL UPDATE
, DELETE
, or
INSERT
statement. In the script in Example 11-4, the function should return 1 in both cases,
since one row is updated when the people_id
is
changed; if the returned value isn’t 1, an
unexpected error has occurred.
In other applications, mysql_affected_rows( )
can
report that zero rows were affected, even if an SQL statement
executes successfully. This occurs if, for example, an
UPDATE
statement doesn’t actually
change the database, or there are no matching rows to
DELETE
. The function can’t be
used with SELECT
statements;
mysql_num_rows(
)
should be used instead.
The header(
)
function is used to redirect the web browser back to the
presents.php script. So when the user clicks on
a link on the presents page, the action.php
script is requested, and the browser is redirected to the presents
page. The overall impression the user has is that she never left the
presents page.
A message
is sent back to the presents page as
part of the URL. The urlencode(
)
library function is used to convert characters that are not permitted
in URLs—such as spaces, tabs, and reserved
characters—into hexadecimal values that can be sent safely.
This is necessary because the message
contains
spaces.