-
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 aSELECTquery can then retrieve the unique customer identifiercust_idof 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.
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");
?>