Many web database systems aren’t only information resources for users but are also tools for storing new information. In our online winestore, users and administrators write data to the database in several situations. Users can purchase wines by creating an order, they can become members, they can manage a shopping cart, and the winestore administrator can manage the stock.
Writing data in web database applications requires different techniques than reading data. Issues of transactions and concurrency become important, and we introduce these issues and the principles of dealing with them in this chapter. The introduction is practical: we focus on the basic management techniques of locking and unlocking tables, and how to safely implement simple database writes in MySQL when there is more than one user simultaneously accessing a database. Most importantly, we identify when special approaches are required, and when these can be safely omitted from a web database application.
We begin by discussing a
<form>
designed to capture input for
database writes. We also include more simple example scripts that
illustrate more about PHP and its use in processing
<form>
input. We discuss some of the
problems of <form>
submission and validation
further in the next chapter.
We also include in this chapter an example illustrating the reload problem, where variables and values are resubmitted when a web page in a browser is, for example, resized. This has practical problems—such as inadvertently buying two bottles of wine—and we discuss a solution that uses HTTP headers.
We then discuss how files can be uploaded from a web browser to a web server and the data then inserted into a MySQL table. We use as an example the uploading of GIF images of maps of wine regions. We also show how these images can be displayed using SQL queries.
By the conclusion of this chapter, we will have covered the skills to build a simple but complete web database application. Several advanced topics remain, including validation of user-supplied data, adding state to a web database application, and authenticating users. We cover these three topics in the next three chapters.
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.
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.
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!
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.
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.
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 " <br>Region number: " . $row["region_id"]; echo " <br>Region name: " . $row["region_name"]; echo " <br>Region description: " . $row["description"]; // Use the script example.6-4.php to display // the map GIF echo " <br>Region map : " . " <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.
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;
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 " <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.
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.
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.
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>
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 .= " <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 .= " <br>The surname field cannot be blank."; // Validate the Address if (empty($formVars["address1"])) // the user's address cannot be a null string $errorString .= " <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 .= " <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 .= " <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 .= " <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 .= " <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.
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.
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 .= " <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 .= " <br>The surname field cannot be blank."; // Validate the Address if (empty($formVars["address1"])) // the user's address cannot be a null string $errorString .= " <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 .= " <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 .= " <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 .= " <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 .= " <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"); ?>
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"); }
3.22.74.232