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.