Writing to Web Databases (Web Database Applications with PHP & MySQL)

6.1. Database Inserts, Updates, and Deletes

Simple database insertions and updates are much the same as queries. We begin this section with a simple case study example that is similar to the querying examples we presented in the last two chapters. However, inserting, updating, and deleting data does require some additional care. After presenting this first example of inserting data, we show a common problem that our first example suffers from—the reload problem—and discuss a solution. After that, we return to further, richer examples of writing to a database and discuss more complex problems and solutions.

Example 6-1 shows a script that presents a <form> for adding a new region to the winestore database and requires the user to provide a new region name and description. The script is similar to the user-driven combined scripts of Chapter 5. If the region name and description are both not empty, an INSERT SQL statement is prepared to insert the new region, using a NULL value for the region_id. As we discussed in Chapter 3, inserting NULL into an auto_increment PRIMARY KEY attribute allocates the next available key value.

If the query is successful—and one row is affected as expected—a success message is printed. If an error occurs, error handling using the method described in Chapter 4 is used. We discuss the function mysql_affected_rows( ) later in Section 6.1.3.

Example 6-1. A combined script to insert a new region in the winestore database

<!DOCTYPE HTML PUBLIC 
               "-//W3C//DTD HTML 4.0 Transitional//EN"
               "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
  <title>Insert a Region</title>
</head>
<body>
<?php
  include 'db.inc';
  include 'error.inc';

  // Test for user input
  if (empty($regionName) || empty($description))
  {
?>
  <form method="GET" action="example.6-1.php">
    Region_name:
    <br>
    <input type="text" name="regionName" size=80>
    <br>Description:
    <br>
    <textarea name="description" rows=4 cols=80></textarea>
    <br>
     <input type="submit">
  </form>
<?php
  }
  else
  {
     if (!($connection = @ mysql_connect($hostName,
                                       $username,
                                       $password))) 
        die("Could not connect to database");

     if (!mysql_select_db($databaseName, $connection))
        showerror( );

     $insertQuery = "INSERT INTO region VALUES 
                    (NULL, " .
                    "\"" . $regionName . "\", " .
                    "\"" . $description . "\", " .
                    "NULL)";

     if ((@ mysql_query ($insertQuery,
                          $connection))     
              && @ mysql_affected_rows( ) == 1)
        echo "<h3>Region successfully inserted</h3>";
     else
        showerror( );
  } // if else empty( )
?>
</body>
</html>

Most write operations can use a format similar to that of Example 6-1. In particular, where database changes are reasonably infrequent and can be performed in one step, most of the more complex issues we describe later in Section 6.2 can be ignored. For the winestore, adding or updating customer details, regions, wineries, and inventory requires almost no more sophistication.

However, as noted earlier, Example 6-1 does have one undesirable side effect that is common in web database applications. The problem isn't really related to modifying the database but rather to the statelessness of the HTTP protocol. We discuss this side effect—the reload problem—and an effective solution in the next section.

6.1.1. Reloading Data and Relocation Techniques

Simple updates using the approach shown in Example 6-1 are susceptible to a common problem of the stateless HTTP protocol that we call the reload problem. Consider what happens when a user successfully enters a new region name and description, and clicks the Submit button. Since the script is a combined script, the same code is executed for a second time, the HTTP encoded variables and values are passed through with the GET method request, a new row is inserted in the region table, and a success message is displayed. So far, everything is going according to plan.

Consider now what happens if the user reloads the success message page with the Reload or Refresh button in the browser. Unfortunately, the variables and values are resubmitted to the same script, and another region row—with the same name and description—is added to the region table. There is no way in this example that the first click of the Submit button to add the first row can be distinguished from a second action that sends the same variables and values to the script. A representation of the reload problem is shown in Figure 6-1.

Figure 6-1. The reload problem

The same reload problem occurs when the user stores the URL as a bookmark or favorite location in her browser and then later requests the URL. Other actions that return to the success page, such as using the Back button, have the same undesirable effect. Perhaps surprisingly, resizing the browser window or printing the page also creates a new HTTP request and causes the reload problem. In our case, each request for the URL adds another identical region to the winestore!

WARNING: The reload problem occurs in many situations. Actions that rerequest a document from the server include pressing the Reload or Refresh buttons, printing, saving the URL in the browser and returning to the page using a bookmark or favorite, using the Back or Forward buttons, pressing the Enter key in the URL Location entry box, and resizing the browser window.

The reload problem isn't always a significant problem. For example, if you use the SQL UPDATE statement to update customer details, and the values are amended with the same correct values repeatedly, there is no data duplication. Indeed, if a row is deleted and the user repeats the operation, the user, at worst, sees a MySQL DBMS error message. However, while some UPDATE and DELETE operations are less susceptible to the reload problem, a well-designed system avoids the problem altogether. Avoidance prevents user confusion and unnecessary DBMS activity. We discuss a solution in a moment.

The HTTP POST method is a little less susceptible to the reload problem than the GET method. If a user reretrieves the script after the first database change, the browser should ask the user whether or not to repost form data as per the HTTP specification. If the user answers OK, the database operation is repeated causing the problem. However, if the user bookmarks the page or reenters the URL at a later time, the <form> is redisplayed because the POST variables and values aren't part of the URL and are lost.

A solution to the reload problem is shown in Figure 6-2, based on the HTTP Location: header, the same header used for one-component querying in Chapter 5.

Figure 6-2. Solving the reload problem with a redirection to a receipt page

The reload solution works as follows:

The user submits the <form> with the variables and values for a database write operation (an SQL INSERT, UPDATE, or DELETE).
The SQL write operation is attempted.
Whether or not the modification is successful, an HTTP Location: header is sent to the browser to redirect the browser to a new, receipt page.

HTTP GET encoded variables and values are usually included with the Location: header to indicate whether the action was successful or not. Additionally, text to display might be sent as part of the redirection URL.
An informative—but harmless—receipt page is displayed to the user, including a success or failure message, and other appropriate text.

The HTTP redirection solves the reload problem. If the user reloads the receipt page the browser has been redirected to, he sees the receipt again, and no database write operations occur. Moreover, since the receipt page receives information about the success or failure of the operation—and any other information identifying the action—encoded in the URL, the receipt page URL can be saved and reloaded in the future without any undesirable effect.

6.1.1.1. Solving the reload problem in practice

A modified version of Example 6-1 with the redirect functionality is shown in Example 6-2. The code is almost identical to that of Example 6-1, with two exceptions.

The first difference in the script in Example 6-2 is that regardless of whether the database insert succeeds or fails, the header( ) function is called. This redirects the browser to the script shown in Example 6-3 by sending a Location: example.6-3.php HTTP header. The difference between the success and failure cases is what is appended to the URL as a query string. In the case of success, status=T and the value of the added region_id attribute are sent. A value of status=F is sent on failure.

The second difference is that the script allows the user to upload a map of the wine region in GIF format for storage in the database. We discuss this functionality in the next section. The script also uses the function mysql_insert_id( ); look for this function in the later section Section 6.1.3.

Example 6-2. An insertion script

<?php
  include 'db.inc';
  include 'error.inc';

  if (empty($regionName) || empty($description))
  {
?>
    <!DOCTYPE HTML PUBLIC
               "-//W3C//DTD HTML 4.0 Transitional//EN"
               "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
      <title>Insert a Region</title>
    </head>
    <body>
    <form enctype="multipart/form-data" 
     action="example.6-2.php" method="post">
    Region_name:
    <br><input type="text" name="regionName" size=80>
    <br>Description:
    <br><textarea name="description" rows=4 cols=80>
    </textarea>
    <input type="hidden" 
      name="MAX_FILE_SIZE" value="100000">
    <br>Region map (GIF format):
    <input name="userfile" type="file">
    <br><input type="submit">
    </form>
    </body>
    </html>
<?php
  }
  else
  {
     $regionName = clean($regionName, 50);
     $description = clean($description, 2048);

     if (!($connection = @ mysql_connect($hostName,
                                       $username,
                                       $password)))
        die("Could not connect to database");

     if (!mysql_select_db($databaseName, $connection))
        showerror( );

     // Was an image file uploaded?
     if (is_uploaded_file($userfile))
     {
        // Open the uploaded file
        $file = fopen($userfile, "r");
    
        // Read in the uploaded file
        $fileContents =
          fread($file, filesize($userfile));

        // Escape special characters in the file
        $fileContents = AddSlashes($fileContents);
     }
     else
       $fileContents = NULL;

    // Insert region data, including the image file
     $insertQuery = "INSERT INTO region VALUES 
                    (NULL, " .  
                    "\"" . $regionName . "\", " .
                    "\"" . $description . "\", " .
                    "\"" . $fileContents . "\")";

     if ((@ mysql_query ($insertQuery,
                         $connection))
        && @ mysql_affected_rows( ) == 1)
           header("Location: example.6-3.php?" .
                 "regionId=". mysql_insert_id($connection) .
                 "&status=T");
        else
           header("Location: example.6-3.php?" .
                  "status=F");
  }
?>

The script in Example 6-3 is a receipt page. When requested with a parameter status=T, it queries the database and displays the details of the newly inserted region. The region is identified by the value of the query string variable regionId. The script also uses another script to display the image of the map inserted by the user; this approach is discussed next. On failure, where status=F, the script displays a database insertion failure message. If the script is unexpectedly called without a status parameter, an error message is displayed.

Example 6-3. The redirection receipt page

<!DOCTYPE HTML PUBLIC
               "-//W3C//DTD HTML 4.0 Transitional//EN"
               "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
  <title>Region Receipt</title>
</head>

<body bgcolor="white">
<?php
  include 'db.inc';
  include 'error.inc';

  $regionId = clean($regionId, 3);
  $status = clean($status, 1);

  // did the insert operation succeed?
  switch ($status)
  {
  case "T":
     // Yes, insert operation succeeded.
     // Show details of the new region as 
     // a receipt page. The new region_id
     // is in the variable $regionId

     $query = "SELECT * FROM region WHERE " .
              "region_id = $regionId";
   
     // Connect to the MySQL DBMS
     if (!($connection = @ mysql_connect($hostName,
                                       $username,
                                       $password)))
        die("Could not connect to database");

     if (!mysql_select_db($databaseName, $connection))
        showerror( );

     // Run the query on the DBMS
     if (!($result = @ mysql_query ($query, $connection)))
        showerror( );

     if ($row = @ mysql_fetch_array($result))
     {
        echo "The following region was added";
        echo "\n<br>Region number: " . $row["region_id"];
        echo "\n<br>Region name: " . $row["region_name"];
        echo "\n<br>Region description: " .
             $row["description"];
        // Use the script example.6-4.php to display 
        // the map GIF
        echo "\n<br>Region map : " .
             "\n<br><img src=\"example.6-4.php?region_id=" .
             $regionId . "\">";
     } // if mysql_fetch_array( )

     // leave the switch statement
     break;

  case "F":
     // No, insert operation failed
     // Show an error message
     echo "The region insert operation failed.";
     echo "<br>Contact the winestore administrator.";

     // leave the switch statement
     break;

  default:
     // User did not provide a status parameter
     echo "You arrived unexpectedly at this page.";
  } // end of switch
?>
</body>
</html>

Several different receipt pages would be developed for an application to informatively display enough information for each different insert, update, and delete operation.

6.1.2. Uploading and Inserting Files into Databases

Example 6-2 and Example 6-3 also show how files can be uploaded from a web browser to a web server, the file data inserted into a database, and the data then retrieved and displayed as part of a web page. In the examples, the file uploaded is a GIF image, but the techniques can be applied to any file or content type.

Files are transferred using the <form> encoding type multipart/form-data and the POST method. Most modern browsers—such as Netscape and Internet Explorer—support this encoding type and the <input> of type file. The <input> of type file displays a widget into which the user can enter a filename; it also displays a Browse button that displays a file dialog for finding files. Therefore, the following fragment from Example 6-2 is all that is needed for a user to select a file and for it to be transferred from a browser to a server:

<form enctype="multipart/form-data" action="example.6-2.php" method="post">
<br>Region map (GIF format):
<input name="userfile" type="file">
<br><input type="submit">
</form>

The uploaded file and information about it can be accessed directly at the web server using PHP. Assuming the <input> widget of type file has a name=userfile, the name of the file on the web server can be accessed as $userfile. The original name of the file on the browser can also be accessed as $userfile_name, the file size as $userfile_size, and the type of the file as $userfile_type.

The following fragment from Example 6-3 checks if a file has been uploaded and, if so, reads the contents of the file into the variable $fileContents:

     // Was an image file uploaded?
     if (is_uploaded_file($userfile))            
     {
       // Open the uploaded file
        $file = fopen($userfile, "r");
    
       // Read in the uploaded file
        $fileContents =
          fread($file, filesize($userfile));

       // Escape special charcters in the file
        $fileContents = AddSlashes($fileContents);
     }  
     else
        $fileContents = NULL;

WARNING: The library function is_uploaded_file( ) should always be used to make sure the file being processed was actually uploaded to the web server. Without the check, a security problem can arise if the user supplies the filename of a file on the web server as a value for userfile using a GET or POST request.

The function fopen( ) opens a file on disk; in this example, it opens the file in read mode by supplying the r flag as the second parameter. The function fread( ) reads the contents of a file, in this case into the variable $fileContents. In this example, the number of bytes read from the file is the file size, determined by using the function filesize( ). After reading the file, any special characters are escaped by adding slashes using the AddSlashes( ) function. It's necessary to do this before the content of the file can be added to the database.

The file data in $fileContents is then inserted in the same way as any other data into the region table:

// Insert region data, including the image file
$insertQuery = "INSERT INTO region VALUES 
               (NULL, " .  
               "\"" . $regionName . "\", " .
               "\"" . $description . "\", " .
               "\"" . $fileContents . "\")";

The end result is that a new region has a name, a textual description, and an associated GIF image stored as the map attribute.

Displaying images from a database is straightforward. The script shown in Example 6-4 retrieves a map image from the region table and outputs the image using the echo statement. The region_id of the required image is supplied as a parameter using the GET method. A header is output to the browser that defines the MIME type of the image, in this case image/gif, and the data follows.

Example 6-4. A script to retrieve GIF images from the region table map attribute

<?
  include 'db.inc';
  include 'error.inc';

  $region_id = clean($region_id, 2);

  if (empty($region_id))
     exit;

  // Connect to the MySQL DBMS
  if (!($connection = @ mysql_pconnect($hostName,
                                       $username,
                                       $password)))
     die("Could not connect to database");

  if (!mysql_select_db($databaseName, $connection))
     showerror( );

  $query = "SELECT map FROM region 
            WHERE region_id = $region_id";

  // Run the query on the DBMS
  if (!($result = @ mysql_query ($query,$connection)))
     showerror;  

  $data = @ mysql_fetch_array($result);

  if (!empty($data["map"]))
  { 
    // Output the GIF MIME header
     header("Content-Type: image/gif");
    // Output the image           
     echo $data["map"]; 
   }
?>

The script in Example 6-4 is requested by an embedded <img> tag in Example 6-3:

echo "\n<br>Region map : " .
     "<img src=\"example.6-4.php?region_id=" .
     $regionId . "\">";

The result is that when the user views the receipt page in Example 6-3, the uploaded image from the database is displayed.

The techniques we have described work for small files such as most GIF images. Several additional configuration steps are required if files larger than a few megabytes are to be uploaded:

As in Example 6-2, an additional hidden <form> field must be added to specify the maximum allowed upload file size, such as:
```
<input type="hidden" name="MAX_FILE_SIZE" value="100000">
```
The memory limit of a PHP script should be greater than the maximum file size. This can be set by adjusting the memory_limit parameter in the php.ini file, which was copied to /usr/local/lib/ in the installation instructions in Appendix A.
The maximum file upload size should be set by modifying the upload_max_filesize parameter in the php.ini file.
The maximum POST size should be set to be greater than the maximum file size by modifying the post_max_size parameter in the php.ini file.
The maximum execution time for a PHP script should set to an appropriate value to allow the upload to complete. The default value is 30 seconds. The parameter can be changed by modifying the max_execution_time parameter in the php.ini file.
The web server must be restarted after any changes, so that the php.ini configuration file is reread. This can be done by executing the command apachectl restart in the directory /usr/local/apache/bin/, assuming the installation instructions in Appendix A were followed.

6.1.3. Inserting, Updating, and Deleting Data

In this section, we complete our discussion of the basics of modifying data by individually considering inserting, updating, and deleting data. We illustrate the principles of each technique in PHP through introductory case study examples; complete examples are presented in Chapter 10 to Chapter 13. Let's begin by looking at two useful PHP functions, both of which have already been used in Example 6-1 and Example 6-2.

6.1.3.1. PHP DML functions for database modifications

The following two functions are used with the MySQL functions described in Chapter 4. The first, mysql_affected_rows( ), is used to insert, delete, and update data. The second, mysql_insert_id( ), is used only for insert operations.

int mysql_affected_rows([resource connection])

Reports the number of rows affected by the last UPDATE, DELETE, or INSERT SQL statement. The function takes as an optional parameter a DBMS connection resource handle. If no parameter is passed, the most recently opened connection is assumed. The function should not be used with SELECT statements; mysql_num_rows( )should be used instead.

For example, if a customer is deleted with the SQL statement:

DELETE FROM customer WHERE CUST_ID=1

then mysql_affected_rows( ) returns a value of 1 if that customer has been successfully deleted. If the query:

INSERT INTO customer SET cust_id = 700

is executed successfully, the function also returns 1.

However, the function may report that zero rows were affected, even if a statement works successfully, because it is possible that an operation may not modify the database. For example, the statement:

UPDATE customer SET zipcode='3053' WHERE city = 'Carlton'

always executes but mysql_affected_rows( ) returns 0 if there are no customers who live in Carlton. Similarly, if a customer row has already been deleted, the function returns 0.

int mysql_insert_id([resource connection])

Returns the AUTO_INCREMENT identifier value associated with the most recently executed SQL INSERT statement. The function is used, for example, to find the cust_id of a new customer when relying on AUTO_INCREMENT to allocate the next available cust_id primary key value after an INSERT INTO customer operation.

The last connection opened is assumed if the connection resource handle parameter is omitted.

This function should be called immediately after the insertion of a row and the result saved in a variable, since the function works for a connection and not on a per-query basis. Subsequent insertions through the same connection make it impossible to retrieve previous key values using this function.

6.1.3.2. Inserting data

We have already illustrated several examples of insertion of data. Let's consider the principles of insertion and a more complex example.

Phase one of the insertion process is data entry. Example 6-5 shows an HTML <form> for capturing data to be inserted into the winestore customer table. The <form> allows entry of customer details into <input type="text"> controls. Only mandatory customer details are entered through this example; the completed customer <form> is presented in Chapter 10.

The date of birth entry—as noted in the instruction before the control—is required in the format DD/MM/YYYY. This requires later conversion to the native MySQL YYYY-MM-DD database format before storing in the database. This conversion is a validation step and, as such, is part of the second phase of insertion that is discussed in detail in Chapter 7. The HTML <form> rendered in a Netscape browser is shown in Figure 6-3.

Example 6-5. An HTML <form> that collects customer data

<!DOCTYPE HTML PUBLIC
   "-//W3C//DTD HTML 4.0 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head><title>Customer Details</title></head>
<body bgcolor="white">
<form method="POST" action="example.6-6.php">
<h1>Customer Details</h1>
<h3>Please fill in the details below to join. 
Fields shown in <font color="red">red</font> are
mandatory.</h3>

<table>
<col span="1" align="right">

<tr>
   <td><font color="red">Surname:</font></td>
   <td><input type="text" name="surname" size=50></td>
</tr>

<tr>
   <td><font color="red">First Name:</font></td>
   <td><input type="text" name="firstName" size=50></td>
</tr>

<tr>
   <td><font color="red">Address:</font></td>
   <td><input type="text" name="address1" size=50></td>
</tr>

<tr>
   <td><font color="red">City:</font></td>
   <td><input type="text" name="city" size=50></td>
</tr>

<tr>
   <td><font color="red">Date of birth (dd/mm/yyyy):</font> </td>
   <td><input type="text" name="dob" size=10></td>
</tr>

<tr>
   <td><font color="red">Email/username:</font></td>
   <td><input type="text" name="email" size=50></td>
</tr>

<tr>
   <td><input type="submit" value="Submit"></td>
</tr>

</table>
</form>
</body>
</html>

Figure 6-3. The customer entry <form> from Example 6-5 rendered in a Netscape browser

The second phase of insertion is data validation and then the database operation itself. Example 6-6 shows the PHP script to insert a new customer. The script has a simple structure, with naive validation that tests only whether values have been supplied for the mandatory fields.

Example 6-6. A validation example that tests for mandatory fields

<?php
  include 'error.inc';
  include 'db.inc';

  // Initialise an error string
  $errorString = "";

  // Clean and trim the POSTed values
  foreach($HTTP_POST_VARS as $varname => $value)
      $formVars[$varname] = trim(clean($value, 50));

  // Validate the firstname
  if (empty($formVars["firstName"]))
      // First name cannot be a null string
      $errorString .=
          "\n<br>The first name field cannot be blank.";
    
  // Validate the Surname
  if (empty($formVars["surname"]))
      // the user's surname cannot be a null string
      $errorString .=
          "\n<br>The surname field cannot be blank.";
    
  // Validate the Address
  if (empty($formVars["address1"]))
      // the user's address cannot be a null string
      $errorString .=
         "\n<br>You must supply at least one address line.";
     
  // Validate the City
  if (empty($formVars["city"]))
      // the user's city cannot be a null string
      $errorString .= "\n<br>You must supply a city.";
    
  // Validate Date of Birth
  if (empty($formVars["dob"]))
   // the user's date of birth cannot be a null string
   $errorString .= "\n<br>You must supply a date of birth.";
    
  elseif (!ereg("^([0-9]{2})/([0-9]{2})/([0-9]{4})$",
          $formVars["dob"], $parts))
      // Check the format
      $errorString .=
        "\n<br>The date of birth is not a valid date " .
        "in the format DD/MM/YYYY";
    
  if (empty($formVars["email"]))
      // the user's email cannot be a null string
      $errorString .= "\n<br>You must supply an " .
                      "email address.";
    
  // Now the script has finished the validation,
  // check if there were any errors
  if (!empty($errorString))
  {       
    // There are errors.  Show them and exit.
?>  
<!DOCTYPE HTML PUBLIC
   "-//W3C//DTD HTML 4.0 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd" >
<html>
<head><title>Customer Details Error</title></head>
<body bgcolor="white">
<h1>Customer Details Error</h1>
<?=$errorString?>
<br>
<a href="example.6-5.php">Return to the customer form</a>
</body>
</html>
<?php
      exit;
  }

  // If we made it here, then the data is valid

  if (!($connection = @ mysql_pconnect($hostName,
                                       $username,
                                       $password)))
     die("Could not connect to database");
    
  if (!mysql_select_db($databaseName, $connection))
     showerror( );
    
  // Reassemble the date of birth into database format
  $dob = " \"$parts[3]-$parts[2]-$parts[1]\"";
    
  // Create a query to insert the customer
  $query = "INSERT INTO customer
      set cust_id = NULL, " .
     "surname = \"" . $formVars["surname"] . "\", " .
     "firstname = \"" . $formVars["firstName"] . "\", " .
     "addressline1 = \"" . $formVars["address1"] . "\", " .
     "city = \"" . $formVars["city"] . "\", " .
     "email = \"" . $formVars["email"] . "\", " .
     "birth_date = $dob";
    
  // Run the query on the customer table
  if (!(@ mysql_query ($query, $connection)))
     showerror( );
    
  // Find out the cust_id of the new customer
  $custID = mysql_insert_id( );
    
  // Now show the customer receipt
  header("Location: customer_receipt.php?custID=$custID");
?>

If an error occurs in the validation process in Example 6-6, the script appends an error description to the string $errorString. The validation of the $dob variable is more complex than that of other fields because the data entry format and database storage format of the field are different, and there are specific requirements for the structure of a date field in a MySQL database table; the techniques used for this reformatting step are discussed in the next chapter.

If an error has occurred, the descriptive string $errorString is output to the browser, followed by an embedded link to allow the user to return to the <form> in Example 6-5. Unfortunately, if the user does click on this link—instead of pressing the Back button—she is returned to an empty <form>. A solution to this problem is presented in Chapter 8.

If the validation succeeds, the final step of the insertion process is completed. Any data that must be reformatted for insertion is modified, and the INSERT query executed. In this implementation, NULL is inserted as the cust_id attribute to use the auto_increment feature and avoid any of the problems discussed in the later section Section 6.2. If the query succeeds, the script redirects to a receipt page that reports the results; we don't discuss the receipt page here, but the complete code is presented in Chapter 10.

6.1.3.3. Updating data

Updating data is usually a more complex process than inserting it. A three-step process for updates is used in most web database applications:

Using a key value, matching data is read from the database.
The data is presented to the user for modification.
The data is updated by writing the modified data to the database, using the key value from the first step.

The first step of this process is usually user-driven: the user provides information that identifies the data to be updated. The information to identify the data—for example, a primary key value such as a cust_id—might be gathered in one of several ways:

It may be entered into a <form> by the user. For example, the user may be asked to type in or select from a list the customer identifier of the customer he wishes to modify.
It may be determined from another user-driven query. For example, the user might provide a surname and a first name through a <form>, and a SELECT query can then retrieve the unique customer identifier cust_id of that customer from the database (assuming the surname and first name combination is unique).
It may be formatted into an embedded link by a script. For example, you can produce a list of descriptions of regions from the winestore, where each entry in the list is a hypertext link that has the unique region identifier encoded as a query string.

These methods of gathering data from the user are discussed in Chapter 5. Here, let's assume that a primary key is provided through one of these techniques, and the value of the primary key has been encoded in an HTTP request that can be processed by the update script.

Step 1 is completed by retrieving the data that matches the primary key value provided by the user. Step 2 is to present the data to the user. To achieve this, a <form> is usually created that contains the values of each attribute that can be modified. In some cases, some attributes may not be presented to the user, and other values may require reformatting from their database representation for presentation. Reformatting is discussed in detail in Chapter 7.

In addition to presenting the data to the user, a method is required to store the primary key value associated with the data, because it is needed in Step 3 as a key to update the data. There are several approaches to maintaining this key across the three-step process, and one simple approach is presented in the next section.

Step 2 is complete when the user submits the <form> containing the modified data. Step 3 updates the database; this uses the same process as inserting new data.

6.1.3.4. Case study: Inserts and updates in practice

Example 6-7 shows a modified version of Example 6-5 that supports database updates. The script implements the first two steps of the three-step update process from the previous section. We discuss the third step later in this section.

Example 6-7. Allowing entry of new customer details and displaying customer details

<?php
  include 'db.inc';
  include 'error.inc';

  $custID = clean($custID, 5);

  // Has a custID been provided? 
  // If so, retrieve the customer details for editing.
  if (!empty($custID))
  {
     if (!($connection = @ mysql_pconnect($hostName,
                                         $username,
                                         $password)))
        die("Could not connect to database");

     if (!mysql_select_db($databaseName, $connection))
        showerror( );
  
     $query = "SELECT * FROM customer  
               WHERE cust_id = " . $custID;
    
     if (!($result = @ mysql_query($query, $connection)))
        showerror( );
  
     $row = mysql_fetch_array($result);
  
     // Reset $formVars, since we're loading from
     // the customer table
     $formVars = array( );
  
     // Load all the form variables with customer data
     $formVars["surname"] = $row["surname"];
     $formVars["firstName"] = $row["firstname"];
     $formVars["address1"] = $row["addressline1"];
     $formVars["city"] = $row["city"];
     $formVars["email"] = $row["email"];
     $formVars["dob"] = $row["birth_date"];
     $formVars["dob"] = substr($formVars["dob"], 8, 2) . 
                        "/" .
                        substr($formVars["dob"], 5, 2) . 
                        "/" .
                        substr($formVars["dob"], 0, 4);
  }
?>
<!DOCTYPE HTML PUBLIC
   "-//W3C//DTD HTML 4.0 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd" >
<html>
<head><title>Customer Details</title></head>
<body bgcolor="white">
<form method="post" action="example.6-8.php">
<h1>Customer Details</h1>
<h3>Please fill in the details below to join. 
    Fields shown in <font color="red">red</font> are
    mandatory.</h3>
<table>
<col span="1" align="right">

<tr>
   <td><input type="hidden" name="custID"
   value="<? echo $custID;?>"></td>
</tr>

<tr>
   <td><font color="red">First name:</font></td>
   <td><input type="text" name="firstName"
   value="<? echo $formVars["firstName"]; ?>" size=50></td>
</tr>

<tr>
   <td><font color="red">Surname:</font></td>
   <td><input type="text" name="surname"
   value="<? echo $formVars["surname"]; ?>" size=50></td>
</tr>

<tr>
   <td><font color="red">Address:</font></td>
   <td><input type="text" name="address1"
   value="<? echo $formVars["address1"]; ?>" size=50></td>
</tr>

<tr>
   <td><font color="red">City:</font></td>
   <td><input type="text" name="city"
   value="<? echo $formVars["city"]; ?>" size=20></td>
</tr>

<tr>
   <td><font color="red">Date of birth (dd/mm/yyyy):</font> </td>
   <td><input type="text" name="dob"
   value="<? echo $formVars["dob"]; ?>" size=10></td>
</tr>

<tr>
   <td><font color="red">Email/username:</font></td>
   <td><input type="text" name="email" 
   value="<? echo $formVars["email"]; ?>" size=50></td>
</tr>

<tr>
   <td><input type="submit" value="Submit"></td>
</tr>

</table>
</form>
</body>
</html>

Step 1 of the update process works as follows. The script in Example 6-7 can process a custID passed through with an HTTP request. If the variable is set—for example, custID=1—this is an update operation. For an update, the script queries the database for the matching customer row and initializes variables with the results of the query. For example, when a surname is retrieved for a customer, the variable $formVars["surname"] is initialized with data from the database using:

$formVars["surname"] = $row["surname"]

This initialization of variables completes the first step of the update process.

The second step of the process—displaying the retrieved data for modification by the user—is achieved by modifying the <form>. We include throughout the <form> code in Example 6-7 short PHP scripts that initialize each <input> widget by setting the value attribute. For example, consider the HTML and PHP code fragment:

<tr>
   <td><font color="red">Surname:</font></td>
   <td><input type="text" name="surname"
   value="<? echo $formVars["surname"]; ?>" size=50></td>
</tr>

This fragment creates a text input widget to enter a surname and uses a short PHP fragment to prefill the widget with the value of the variable $formVars["surname"]. If the variable was initialized and isn't empty, the database value is displayed for editing by the user.

The second step of the process is completed by embedding the value of $custID in the <form> as a hidden input element. The $custID is embedded so it can be passed to the next script, where it then constructs the SQL query to perform the update operation. There are other ways this value can be passed through the three steps; these techniques are the subject of Chapter 8.

Example 6-8 implements the third step. The process is the same as inserting new data, with the exception of the SQL query that uses the $custID from the customer <form> to identify the row to be updated. The script not only supports updates but also supports the insert functionality of Example 6-6; if $custID isn't set, the data is inserted as a new row. As previously, after the database operation, the browser is redirected to a receipt page to avoid the reload problem. However, the update process is now susceptible to other problems that are described in the later section Section 6.2.

Example 6-8. Updating existing and inserting new customer rows

<?php
  include 'error.inc';
  include 'db.inc';

  $custID = clean($custID, 5);

  // Initialise an error string
  $errorString = "";

  // Clean and trim the POSTed values
  foreach($HTTP_POST_VARS as $varname => $value)
      $formVars[$varname] = trim(clean($value, 50));

  // Validate the firstname
  if (empty($formVars["firstName"]))
      // First name cannot be a null string
      $errorString .=
          "\n<br>The first name field cannot be blank.";

  // Validate the Surname
  if (empty($formVars["surname"]))
      // the user's surname cannot be a null string
      $errorString .=
          "\n<br>The surname field cannot be blank.";

  // Validate the Address
  if (empty($formVars["address1"]))
      // the user's address cannot be a null string
      $errorString .=
       "\n<br>You must supply at least one address line.";

  // Validate the City
  if (empty($formVars["city"]))
      // the user's city cannot be a null string
      $errorString .= "\n<br>You must supply a city.";

  // Validate Date of Birth
  if (empty($formVars["dob"]))
      // the user's date of birth cannot be a null string
   $errorString .= "\n<br>You must supply a date of birth.";

  elseif (!ereg("^([0-9]{2})/([0-9]{2})/([0-9]{4})$",
                 $formVars["dob"], $parts))
      // Check the format
      $errorString .=
        "\n<br>The date of birth is not a valid date " .
        " in the format DD/MM/YYYY";

  if (empty($formVars["email"]))
      // the user's email cannot be a null string
      $errorString .= 
        "\n<br>You must supply an email address.";

  // Now the script has finished the validation,
  // check if there were any errors
  if (!empty($errorString))
  {
      // There are errors.  Show them and exit.
?>
<!DOCTYPE HTML PUBLIC
   "-//W3C//DTD HTML 4.0 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd" >
<html>
<head><title>Customer Details Error</title></head>
<body bgcolor="white">
<h1>Customer Details Error</h1>
<?=$errorString?>
<br>
<a href="example.6-7.php">Return to the customer form</a>
</body>
</html>
<?php
      exit;
  }

  // If we made it here, then the data is valid

  if (!($connection = @ mysql_pconnect($hostName,
                                       $username,
                                       $password)))
     die("Could not connect to database");

  if (!mysql_select_db($databaseName, $connection))
     showerror( );

  // Reassemble the date of birth into database format
  $dob = " \"$parts[3]-$parts[2]-$parts[1]\"";

  // Is this an update?
  if (!empty($custID))
  {
     // Create a query to update the customer
     $query = "UPDATE customer SET ". 
      "surname = \"" . $formVars["surname"] . "\", " .
      "firstname = \"" . $formVars["firstName"] . "\", " .
      "addressline1 = \"" . $formVars["address1"] . "\", " .
      "city = \"" . $formVars["city"] . "\", " .
      "email = \"" . $formVars["email"] . "\", " .
      "birth_date = " . $dob . 
      " WHERE cust_id = $custID";
  }
  else
     // Create a query to insert the customer
     $query = "INSERT INTO customer
       set cust_id = NULL, " .
      "surname = \"" . $formVars["surname"] . "\", " .
      "firstname = \"" . $formVars["firstName"] . "\", " .
      "addressline1 = \"" . $formVars["address1"] . "\", " .
      "city = \"" . $formVars["city"] . "\", " .
      "email = \"" . $formVars["email"] . "\", " .
      "birth_date = $dob";

  // Run the query on the customer table
  if (!(@ mysql_query ($query, $connection)))
     showerror( );

  // Is this an insert?
  if (empty($custID))
     // Find out the cust_id of the new customer
     $custID = mysql_insert_id( );

  // Now show the customer receipt
  header("Location: customer_receipt.php?custID=$custID");
?>

6.1.3.5. Deleting data

The basic principle of deletion is a two-step process: first, identify the row or rows to be deleted; and second, remove the data with an SQL DELETE statement.As in an update, the first step requires a key value be provided, and any technique described for capturing keys in updates can be used. We assume here that a unique, primary key value for the row to be deleted is available.

Deleting rows using a primary key value is a minor modification to the update functionality of the script in Example 6-8. For example, the following fragment creates and runs a query to delete a specified customer identified by the value of $custID:

  // Connect to the database, clean, and validate data

  // We have a custID. Set up a delete query
  $query = "DELETE FROM customer 
            WHERE cust_id = $custID";

  if ( (@ mysql_query ($query, $connection))
        && @ mysql_affected_rows( ) == 1)
  {
    // Query ran ok

    // Relocate to the receipt page with status=T
    header("Location: " .
           "delete_receipt.php?" .
           "cust_id=$custID" .
           "&status=T");
  }
  else
  {
    // Failed to delete customer row.
    // Relocate to the status page with status=F
    header("Location: " .
           "delete_receipt.php?" .
           "status=F");
  }