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

               "-//W3C//DTD HTML 4.0 Transitional//EN"
  <title>Insert a Region</title>
  include '';
  include '';
  // Test for user input
  if (empty($regionName) || empty($description))
  <form method="GET" action="example.6-1.php">
    <input type="text" name="regionName" size=80>
    <textarea name="description" rows=4 cols=80></textarea>
     <input type="submit">
     if (!($connection = @ mysql_connect($hostName,
        die("Could not connect to database");
     if (!mysql_select_db($databaseName, $connection))
        showerror(  );
     $insertQuery = "INSERT INTO region VALUES
                    (NULL, " .
                    "\"" . $regionName . "\", " .
                    "\"" . $description . "\", " .
     if ((@ mysql_query ($insertQuery,
              && @ mysql_affected_rows(  ) == 1)
        echo "<h3>Region successfully inserted</h3>";
        showerror(  );
  } // if else empty(  )

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.