11.6 Writing Data with PHP

only for RuBoard - do not distribute or recompile

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.

Example 11-4. The action.php script reserves gifts or removes them from a shopping list

 <?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.

only for RuBoard - do not distribute or recompile