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.