Learning MySQL (2007)
Part IV. Web Database Applications with PHP
Chapter 15. A PHP Application: The Wedding Gift Registry
In the previous chapter, we looked at the basics of the PHP language and how it can interact with a MySQL database. In this chapter, we explore how to access and exchange information with a MySQL server from within a practical PHP application. As a running example, we present a simple wedding gift registry application that allows wedding guests to log in, view a list of gifts wanted by the bride and groom, and reserve gifts that they plan to purchase by putting them on a shopping list.
In the process, we show how the common database functions are used in practice, including how to:
§ Call PHP library functions to connect to the MySQL DBMS and handle MySQL errors with PHP
§ Manage DBMS credentials with include files
§ Execute queries through the DBMS connection, and retrieve query result sets
§ Present query results using HTML
§ Create HTML form environments using PHP
§ Interact with the user and preprocess user data to minimize security risks
§ Add session support to an application so that a user can log in and log out
§ Pass data between scripts by creating embedded hypertext links in HTML
§ Use HTTP headers
The database and script files for this example are available from the book’s web site; we recommend you download these and look at them while reading through this chapter. Even better, set up the application on your system and try it out. The program source code is also listed in the Appendix.
Designing and Creating the Wedding Database
Let’s say Jack and Jill are getting married and would rather not receive the traditional 7 toasters and 11 electric kettles. Instead, they’ve decided to lists things they actually do want on a web site and let wedding guests select something useful to buy from the list.
Thinking about the problem carefully, we come across several requirements. Our application should:
§ Allow Jack and Jill to add to or modify the list of required gifts
§ Allow users to view the gifts that can be selected
§ Allow users to select gifts to buy, or to deselect gifts they have previously reserved
§ Not allow users to select gifts that are already reserved
§ Authenticate users to identify them and prevent one user modifying the selections of another
There are two entities here: users and gifts. Each user has a unique username and password, while each gift has a unique gift ID number, a description, desired quantity, color, place of purchase, and price. A gift may be reserved by a user, so each gift record can have a username associated with it. A one-to-many relationship is maintained between the two tables: each gift can be reserved by only one user; each user can reserve zero or more gifts. Figure 15-1 shows the ER model in the MySQL Workbench.
Figure 15-1. The wedding registry ER model using the MySQL Workbench
Our database needs to contains only two tables—users, which stores a unique username and password for each wedding guest:
CREATE TABLE `users` (
`username` VARCHAR(30) NOT NULL,
`password` VARCHAR(30) NOT NULL,
PRIMARY KEY (`username`)
);
and gifts, which stores data about gifts:
CREATE TABLE `gifts` (
`gift_id` SMALLINT NOT NULL AUTO_INCREMENT,
`description` VARCHAR(255) NOT NULL,
`shop` VARCHAR(100) NOT NULL,
`quantity` SMALLINT NOT NULL,
`color` VARCHAR(30) DEFAULT NULL,
`price` VARCHAR(30) DEFAULT NULL,
`username` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY (`gift_id`)
);
The username is unique for each user and is used as a foreign key in the gifts table. Since this is a relatively simple application, we won’t use a separate user ID field; for a complex application with heavy usage, it would be more efficient to have a small user ID field in the declaration, as shown here:
CREATE TABLE `users` (
`username` VARCHAR(30) NOT NULL,
`password` VARCHAR(30) NOT NULL,
`user_id` INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`user_id`)
);
and use the user_id field as a foreign key in other tables.
Instead of typing in the CREATE statements, you can conveniently create the database by running the create_wedding_database.sql file, which you can download from the book’s home page:
$ mysql --user=root --password=the_mysql_root_password \
< create_wedding_database.sql
You’ll find these lines near the beginning of the file:
DROP DATABASE IF EXISTS wedding;
CREATE DATABASE wedding;
USE wedding;
These delete any existing wedding database before creating the new one from scratch.
We should also allow access to the database from a client; in our application, the client is primarily the PHP engine that executes our code. We can create the MySQL user fred who has a password shhh. This user is set up using the following SQL GRANT statement:
GRANT SELECT, INSERT, DELETE, UPDATE
ON wedding.*
TO fred@localhost
IDENTIFIED by 'shhh';
You can enter this statement in the MySQL monitor, or download the grant_privileges.sql from the book’s home page and run it:
$ mysql --user=root --password=the_mysql_root_password < grant_privileges.sql
The Login Form
As the start page for our application, we will show a login form asking users for their username and password. The HTML source for the login form is show below, and Figure 15-2 shows the page rendered in a web browser:
<html>
<head>
<title>Jack and Jill's Wedding Gift Registry</title>
</head>
<body bgcolor='LIGHTBLUE'>
<h2>Jack and Jill's Wedding Gift Registry</h2>
(if you've not logged in before, make up a username and password)
<form action="process.php" method="POST">
<br />Please enter a username: <input type="text" name="username" />
<br />Please enter a password: <input type="password" name="password" />
<br /><input type="submit" value="Log in" />
</form>
</body>
</html>
Figure 15-2. The wedding registry login page
When the user types in information and clicks the Submit button, the form passes the data to the script specified in the form action attribute; in our example, the data is sent to the script process.php.
Our form has three input fields: username, password, and submit. Since the form submits the data using the POST method, we can look inside the $_POST superglobal array in process.php to access the corresponding values as $_POST["username"], $_POST["password"], and$_POST["submit"]. The data from the submit field will just be Log In (as specified in the form), and this field isn’t very useful; however, it’s important to know that it exists.
Using One Script for the Form and for Processing
It’s common to use a single PHP script to both generate the HTML form and to receive the submitted form data. When the user submits the form, the browser sends the user data along with the request. The script can decide what to do by checking for the user data: if there’s no user data, it generates and sends the HTML login form; if there is some user data, it processes it. We can use the count() function to count the number of entries in the $_POST array. If the count is nonzero, we know that some data has been submitted using the POST method:
// If the number of elements in the $_POST array is nonzero...
if(count($_POST))
{
// Process form data
}
// otherwise...
else
{
// Generate HTML form
}
?>
We can improve this code a bit. If a user clicks the Submit button without typing in a username or password, the $_POST array will still contain one item: the data from the Submit button itself ($_POST["submit"]). We can try to read the username and password from the $_POST array; if the form has not been submitted, this array will be uninitialized, and so the username and password entries will be empty. The username and password can also be left blank by the user. In either case, our script will detect an empty username or password, and will show the form. If the username and password aren’t empty, the script will process the data and try to log the user in to the application:
// Pre-process the authentication data from the form for security
// and assign the username and password to local variables
if(count($_POST))
{
$username = clean($_POST["username"], 30);
$password = clean($_POST["password"], 30);
}
// If no username or password has been entered,
// show the login page
if(empty($username) ||
empty($password) )
{
// Generate HTML form
}
else
{
// Process form data
}
?>
In script files that both generate the form and process the submitted data, the form action is set to the name of the script file itself. As our login form is the start page of our application, we’ll call the script index.php so that it’s the index file. We described index files in Web Server Index Files” in Chapter 13. The form action in the index.php script is then also index.php:
<form action="index.php" method="POST">
Instead of typing the name of the script into the form action field, we can simply access the PHP_SELF entry from the $_SERVER superglobal array that we first saw in The PHP Predefined Superglobal Variables” in Chapter 14:
<form action="<?php echo $_SERVER["PHP_SELF"];?>" method="POST">
When the PHP script is processed, the form action is set to the address of the script before the HTML is sent to the browser.
Passing a Message to a Script
If the user submits the form without providing any authentication details, or submits details that are incorrect, we should display the form again with an appropriate error message. We can modify our index.php script to display the login form if a message has been passed to it for display, in addition to the previous check for an empty username or password:
// If no username or password has been entered, or there's a message
// to display, show the login page
if(empty($username) || empty($password) || isset($message) )
{
// Display the message (if any), and the login form
}
else
{
// Try to authenticate the user against the database
...
// If unsuccessful, pass an error message and call the form again.
}
?>
Here, we’ve used the empty() function to ensure that the $username and $password variables are not empty, and the isset() function to check whether the $message variable has been initialized. Note that these functions are slightly different: a variable can be initialized (set) to an empty string. Since we set the first two variables earlier in the script, they will always be initialized, so we need to check whether their contents are empty or not. The $message variable will be initialized if a message has been passed to us for display; let’s see how this is done.
To pass nonsensitive information from one script to another, we can create our own GET request by adding a query string to the name of the target script. The query string consists of list of value assignments separated by ampersands, and is separated from the address of the script by a question mark symbol (?). For example, we can pass the value Problem to the script index.php by assigning it to the variable message in this way:
index.php?message=Problem
We can call this URL using the header() function we first saw in The Reload Problem” in Chapter 14.
In the target script, we can then access these values through the $_GET superglobal array. Even though we’re creating this message, we depend on the browser to send it to us. Since it arrives from the client, it can be manipulated, and so we must treat it with caution and should apply theclean() function before using it:
// Pre-process the message data for security
if(count($_GET))
{
$message = clean($_GET["message"], 128);
echo "The message is: ".$message;
}
To avoid confusing the browser with symbols such as spaces and tabs that have special meaning in URL strings, we can process messages with the PHP urlencode() function before appending them to the requested URL. The encoded string can then be safely used as part of a URL string. For example, the following two lines:
$message="Please choose a username and password that are ".
"at least four characters long";
$target_URL="index.php?message=".urlencode($message);
produce the $target_URL variable with the value:
index.php?message=Please+choose+a+username+and+password+that+are+
at+least+four+characters+long
If we want to pass multiple values, we can use the ampersand symbol (&) as a separator between each variable name and value pair, as in this example:
search.php?search_term=truth&display_results=50&language=english
Most web search engines use this technique as part of their Previous and Next links in the search results page.
Logging Users In and Out
In our application, we can check the username and password obtained from the login form against the authentication details in the database; if we find a matching row, the user is authorized to access the system. Given the limited need for security in our application, we can reasonably have it automatically register new users the first time they enter their authentication details. The next time they visit, the application will authenticate them against the stored data.
Before trying to authenticate the user, we can check to ensure that the entered username and password aren’t too short; in general, the longer the authentication strings, the harder they are to subvert by a malicious user. If either the username or the password are less than four characters long, we redirect the browser to the login page with an appropriate error message:
// Check that the username and password are each at least three
// characters long.
if( (strlen($username)<4) ||
(strlen($password)<4) )
{
// No, they're not; create an error message and redirect
// the browser to the index page to display the message
$message = "Please choose a username and password that are ".
"at least four characters long";
header("Location: index.php?message=" . urlencode($message));
exit;
}
Once we’re happy with the length of the username and password, we can check whether the username already exists in the database; if it does, we check to see whether the provided password is correct. If no such username exists, we add the new username and password pair to the database.
To access the database using PHP, we first set up a connection to the MySQL server using the mysqli_connect() function. This takes four arguments: the name of the host the MySQL server is running on, the username and password to use to connect to the server (these are the same username and password that are used to access the DBMS though the command-line monitor), and the database to use:
$DB_hostname = "localhost";
$DB_username = "fred";
$DB_password = "shhh";
$DB_databasename = "wedding";
$connection=mysqli_connect($DB_hostname, $DB_username, $DB_password,
$DB_databasename);
As described in Accessing MySQL Using PHP” in Chapter 14, the mysqli_connect() function can additionally take parameters that specify the port number and socket path to use.
In our environment, the web server and the MySQL DBMS are running on the same host machine, so the user needs access only from localhost. Having the DBMS and web server on the same machine is a good decision for small- to medium-size web database applications because there is no network communications overhead between the DBMS and the web server. For high-traffic or complex web database applications, it may be desirable to have dedicated hardware for each application.
Verifying New Users
In our simple application, we’ve allowed users to create new accounts for themselves. For applications where security is more important, new accounts might need to be added or approved by the system administrator. To ensure that the email address is valid and owned by the person requesting the account, you can also ask new users to authenticate themselves through an email verification step. For each new account request, you can generate and store a random verification key, and then send an email with a verification link, specifying the user and key:
http://www.invyhome.com/verify.php?user_id=313&key=b114bcf8e4a110a786f19f5
When the user reads the email and opens this address in their browser, the application can check that the key matches the one stored in the database for this user; if so, the account can be activated. This is still vulnerable to a brute-force attack, where an attacker tries all possible permutations of characters to find the correct one—rather like trying all possibilities on a combination lock. For added security, you can count how many times you receive verification attempts for a particular user and block the account (and notify the administrator) if there are more than, say, 10 attempts.
Authenticating the User
Once we have successfully created an active connection to the MySQL server, we can use it in conjunction with other PHP functions to run queries on the database and retrieve data. We can execute an SQL query on the MySQL server using the mysqli_query() function. This function takes two parameters: the DBMS connection to use and the query to execute.
The query does not need to be terminated with a semicolon. For a successful query that returns no answer rows, it returns TRUE; for a successful SELECT, SHOW, DESCRIBE, or EXPLAIN query, it returns the query results for later processing. For an unsuccessful query, it returns FALSE:
// Create a query to find any rows that match the provided username
$query = "SELECT username, password FROM users WHERE username = '$username'";
// Run the query through the connection
if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
An SQL query may be successful but return no matching rows. The mysqli_num_rows() function returns the number of rows that have been returned by a SELECT query; we can use this to test whether our search for the specified username returned any rows:
// Were there any matching rows?
if (mysqli_num_rows($result) == 0)
{
// No, so insert the new username and password into the table
...
}
else
{
// Yes, so check that the supplied password is correct
...
}
We store the username and password pair in the users table, but with a twist; instead of storing the actual password, we store the result of passing the password through the crypt() one-way encryption function. It’s impossible to reverse the one-way encryption function to recover the password from the stored encrypted data.
This approach is more secure, as an attacker who gets hold of the database will not be able to determine what the passwords are and will need to try encrypting different possible passwords—perhaps using a “dictionary” of words—to find ones that appear in the table. We can further complicate any attack by using a variety of ways to encrypt the passwords; the attacker would then need to encrypt the dictionary as many times as the number of encryption methods used.
To add some variation to the one-way encryption, we use a salt in conjunction with the crypt() function. The salt is a parameter to the encryption routine that modifies the encryption behavior; we can use the first two characters from the username to obtain a reasonable number of variations to make it more difficult to use a dictionary attack on all the passwords. For example:
echo crypt("My secret password", "ss");
returns sstCejlom2fqI, while:
echo crypt("My secret password", "st");
returns steYkLCBz8Ir.. Note that the salt is prepended to the start of the encrypted string returned by the crypt() function.
To verify that the user has provided the correct password, we take the password, apply the same one-way encryption, and compare the result with the encrypted password stored in the database; if they match, we know that the user has provided the correct password—even though we don’t know what the correct password is. This is similar to the way MySQL stores passwords for its own users.
WARNING
Encrypted passwords on the server are only part of the overall security picture. With an ordinary web server, usernames and passwords are transferred between the web browser and web server as unencrypted text, so it’s trivial for an attacker to listen in on the communications to determine the authentication details—without the need to grapple with the encrypted passwords. For an application that has higher security requirements, communications should be encrypted by requiring a Secure Sockets Layer (SSL) connection between the web browser and web server.
To implement this authentication process with PHP, we use the mysqli_fetch_array() function to retrieve the rows of data one at a time from the result-set handle returned by the mysqli_query() function. The function returns false when there are no more rows to fetch. Since the username field is the primary key of the users table, we expect at most one matching row, and so need to call the mysqli_fetch_array() function only once. We place the retrieved row data in a variable—here we use the variable $row—and access the fields associatively using the field names. For example, the username field can be accessed through the variable $row["username"].
If the authentication step fails, we redirect the browser to the same login page but ask PHP to display the error message we pass using the query string:
// Were there any matching rows?
if (mysqli_num_rows($result) == 0)
{
// No, so insert the new username and password into the table
$query = "INSERT INTO users SET username =".
"'$username', password='".crypt($password, substr($username, 0, 2))."'";
// Run the query through the connection
if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
}
else
{
// Yes, so check that the supplied password is correct
// Fetch the matching row
// If we don't get exactly one answer, then we have a problem
$matchedrows=0;
while($row = @ mysqli_fetch_array($result))
$matchedrows++;
if($matchedrows!=1)
die("We've just experienced a technical problem - ".
"please notify the administrator.");
// Does the user-supplied password match the password in the table?
if (crypt($password, substr($username, 0, 2)) != $row["password"])
{
// No, so redirect the browser to the login page with a
// message
$message = "This user exists, but the password is incorrect. ".
" Choose another username, or fix the password.";
header("Location: index.php?message=" . urlencode($message));
exit;
}
}
We can be pretty certain that we’ll get only a single match when we search for the user and password pair, since username is the primary key of the users table. However, we add an extra check to count the number of rows retrieved; if we get more than one, something odd has happened, and we stop processing. This is an example of defensive programming—thinking through all the possibilities that can occur and trying to ensure that your code can handle problems gracefully. The more effort you put into inserting checks into your code, the easier it will be to identify problems before they cause irretrievable damage to your data or your relationship with your customers!
Incidentally, you could rewrite these three lines:
$matchedrows=0;
while($row = @ mysqli_fetch_array($result))
$matchedrows++;
in a for loop with an empty body (but note the semicolon at the end):
for($matchedrows=0; ($row = @ mysqli_fetch_array($result)) ; $matchedrows++);
In this loop, $matchedrows is initialized to 0, and the loop is repeated as long as the condition:
($row = @ mysqli_fetch_array($result))
is true (not zero). This will be the case as long as mysqli_fetch_array() finds another row to fetch from the results. Each time the loop iterates, the value of matchedrows is incremented by one. This code is more compact, but is also slightly harder to understand. Try to avoid writing code that’s too difficult to understand, and always add clear comments to explain what the code is doing. It’s very hard to understand badly commented code, even if you wrote the code yourself.
Starting the User Session
After inserting a new username and password pair into the users table, or after verifying that the provided username and password pair is correct, we know that the user is authorized to access the system. We start a new session with the session_start() function and store the username in a session variable. We then redirect the browser to the gift list page with a welcome message:
// Everything went OK. Start a session, store the username in a session variable,
// and redirect the browser to the gift list page with a welcome message.
session_start();
$_SESSION['username']=$username;
$message = "Welcome {$_SESSION['username']}! ".
"Please select gift suggestions from the list to add to your shopping list!";
header("Location: list.php?message=" . urlencode($message));
exit;
We can use the $_SESSION['username'] variable to determine whether the guest is logged in and, if so, what their username is. Since we’d like to check that the user is authorized to check each page, we can define a PHP function logincheck() to verify that the user is logged in, and redirect the user to the login page if they are attempting to access a page without being logged in, or after their session has expired:
// Check if the user is logged in. If not, send them to the login
// page
function logincheck()
{
session_start();
if (empty($_SESSION["username"]))
{
// redirect to the login page
header("Location: index.php");
exit;
}
}
We call this function near the beginning of each script in our application to prevent people from sidestepping the authentication process; we could include an error message saying something like “You must log in to access that page.”
Logging the User Out
To end a user’s session with our gift registry application, we can have a “log out” link that calls the logout.php script. This script initializes the session and then destroys it. It then redirects the browser to the application main page:
<?php
// Log out of the system by ending the session and load the main
// page
session_start();
session_destroy();
// Redirect to the main page
header("Location: index.php");
?>
As you can see, the script doesn’t produce any HTML output. Instead, it uses the header() function to send a Location header line to the browser. When the browser receives this, it loads the specified web page—in this case, the index.php file in the same directory as the logout.php script.
The db.php Include File
As discussed in Chapter 14 in Modularizing Code,” include files can be used to provide a single definition for variable values or function declarations used by multiple scripts in an application. In our application, all our scripts need to use the same connection and authentication credentials to communicate with the database. We have previously described the custom clean() and logincheck() functions in Untainting User Data,” in Chapter 14 and Authenticating the User,” earlier in this chapter, and before that we wrote the showerror() function in Handling MySQL Errors” in Chapter 14. Since these functions are used across several PHP scripts, we can place the function definitions in a common include file that is loaded by the scripts that need it. This helps to keep code easy to maintain; any change to data or definitions in an include file is automatically in effect for the scripts that use it.
We can place the function definitions, along with the database connection details, in the file db.php:
<?php
// These are the DBMS credentials and the database name
$DB_hostname = "localhost";
$DB_username = "fred";
$DB_password = "shhh";
$DB_databasename = "wedding";
// Show an error and stop the script
function showerror($connection)
{
// Was there an error during connection?
if(mysqli_connect_errno())
// Yes; display information about the connection error
die("Error " . mysqli_connect_errno($connection) .
" : " .mysqli_connect_error($connection));
else
// No; display the error information for the active connection
die("Error ".mysqli_errno($connection) . " : "
.mysqli_error($connection));
}
// Secure the user data by escaping characters and shortening the
// input string
function clean($input, $maxlength)
{
// Access the MySQL connection from outside this function.
global $connection;
// Limit the length of the string
$input = substr($input, 0, $maxlength);
// Escape semicolons and (if magic quotes are off) single and
// double quotes
if(get_magic_quotes_gpc())
$input = stripslashes($input);
$input = mysqli_real_escape_string($connection, $input);
return $input;
}
// Check if the user is logged in. If not, send them to the login
// page
function logincheck()
{
session_start();
if (empty($_SESSION["username"]))
{
// redirect to the login page
header("Location: index.php");
exit;
}
}
?>
We can incorporate this file as required using the require_once() directive; for example, we can add the line:
require_once("db.php");
in the file index.php to have the db.php file included in it.
Editing the List of Gifts
Jack and Jill, our bride and groom, need to set up the list of gifts for wedding guests to choose from. Our application includes an edit.php file that allows the user to add or remove gifts, or modify existing gifts. In this section, we describe how we can prevent users other than Jack and Jill from accessing the editing page, and how the script enables gifts to be added, updated, and deleted.
Restricting Edit Access
To prevent unauthorized access, we ensure that only the users jack and jill can access this file; other users attempting to access this page are redirected to the gift list page list.php:
<?php
// edit.php: Show the user the available gifts and the gifts in
// their shopping list
// Include database parameters and related functions
require_once('db.php');
// Check if the user is logged in
// (this also starts the session)
logincheck();
// Check that the user is Jack or Jill (username is 'jack' or
// 'jill'); other users are not allowed to edit the gifts.
if($_SESSION['username']!="jack" && $_SESSION['username']!="jill")
{
$message = "You are not authorized to edit the gift details. ".
" Please select gift suggestions from the list to add to your shopping list!";
header("Location: list.php?message=".urlencode($message));
exit;
}
// Other code to display and edit the gifts
...
?>
Note that our application automatically creates an account and grants access the first time a user registers a particular username. It’s technically possible for a user other than the real Jack or Jill to access the application first and create an account with the username jack or jill, and so gain access to the edit page. Since the username is the primary key to the users table, new accounts can’t be added with the privileged usernames jack and jill once both these accounts have been created. If the user provides a username that already exists, our code assumes that they are trying to log in and checks whether the provided password matches the stored password for that username. Obviously, this is not particularly secure but is probably sufficient for the level of security our application requires.
Role-Based Authentication
We’ve hardcoded the usernames jack and jill into the script; if, for example, we wanted to grant edit access to another user, we would need to modify the code in the edit.php file. A more scalable solution is to authorize users according to their roles; for example, users who have anadministrator role could be allowed to edit the gifts, while users who have a normal_user role could be allowed only to choose gifts for the couple. This would require us to add an extra role field to the users table and to modify edit.php to allow access to users who are administrators.
Role-based authentication helps maintainability, since we don’t have to change program code according to individual users. In a large organization with people frequently changing roles or perhaps leaving the organization altogether, we need only to change the role of a particular user to change the level of access. For our application, we can consider two roles: administrator and guest. Jack and Jill are administrators, and everyone else is a guest. In role-based authentication, the test for the users jack and jill:
// Check that the user is Jack or Jill (username is 'jack' or
// 'jill'); other users are not allowed to edit the gifts.
if($_SESSION['username']!="jack" && $_SESSION['username']!="jill")
...
would instead be something like this:
// Look up this user's role in the database
$query="SELECT role FROM users WHERE username=".$_SESSION['username'];
if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
// Fetch the matching row
// If we don't get exactly one answer, then we have a problem
for($matchedrows=0; ($row = @ mysqli_fetch_array($result)); $matchedrows++);
if($matchedrows!=1)
die("We've just experienced a technical problem - ".
"please notify the administrator.");
// Save the role into a session variable for use
// in other parts of the application
$_SESSION['role']=$row['role'];
// Check that the user is an administrator;
// other users are not allowed to edit the gifts.
if($_SESSION['role']!="administrator")
...
In this example, we’ve assumed that the users table has a role column. Role-based authentication is probably overkill for our simple wedding gift registry, but it’s a good approach for most other applications.
The Gift Editing Form
edit.php The edit.php script displays a list of the gifts in the system for editing or deletion, and a blank gift entry to allow a new gift item to be added. Figure 15-3 shows the application gift editing page loaded in a web browser.
Figure 15-3. The wedding registry gift editing page
The HTML source of the file includes a link to the logout.php script that ends the user session and a link to the application main page. The form then incorporates the output of the showgiftsforedit() function that we describe next:
<!DOCTYPE HTML PUBLIC
"-//W3C//DTD HTML 4.0 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Jack and Jill's Wedding Gift Registry</title>
</head>
<body bgcolor="LIGHTBLUE">
<?php
// Show a logout link and a link to the main page
echo "<a href='logout.php'>Logout</a> | <a href='list.php'>Gift list</a>";
echo "\n<h3>Gift editing page</h3>";
// Show the existing gifts for editing
showgiftsforedit($connection);
?>
</body>
</html>
Note the \n in the HTML <h3> header string; this adds a newline in the HTML source to help when developing the application, but you can safely omit it.
The core text of the edit page is produced by the showgiftsforedit() function. This generates an HTML form with an action set to $_SERVER["PHP_SELF"]; this means that the form data is passed back to the edit.php script, which must then process it:
// Create an HTML form pointing back to this script
echo "\n<form action='{$_SERVER["PHP_SELF"]}' method='POST'>";
...
echo "</form>";
The form input fields are prefilled with the gift details retrieved from the database; for neatness, we create an HTML table and arrange the form fields in the table.
// Create an HTML table to neatly arrange the form inputs
echo "\n<table border='1'>";
// Create the table headings
echo "\n<tr>" .
"\n\t<th bgcolor='LIGHTGREEN'>ID</th>" .
"\n\t<th bgcolor='LIGHTGREEN'>Description</th>" .
"\n\t<th bgcolor='LIGHTGREEN'>Quantity</th>" .
"\n\t<th bgcolor='LIGHTGREEN'>Color</th>" .
"\n\t<th bgcolor='LIGHTGREEN'>Available from</th>" .
"\n\t<th bgcolor='LIGHTGREEN'>Price</th>" .
"\n\t<th bgcolor='LIGHTGREEN'>Delete?</th>" .
"\n</tr>";
...
echo "</table>";
Once we’ve created the table, we run an SQL query to list all the gifts in the gifts table, and order the results by the alphabetical order of the gift descriptions. If no gifts are found, we display a suitable message; if any gifts are found, we display them for editing:
// Create an SQL query to list the gifts in the database
$query = "SELECT * FROM gifts ORDER BY description";
// Run the query through the connection
if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
// Check whether we found any gifts
if(!mysqli_num_rows($result))
// No; display a notice
echo "\n\t<tr><td colspan='7' align='center'>".
"There are no gifts in the database</td></tr>";
else
// Display the results for editing...
To display the results, we execute a while() loop that repeatedly calls the mysqli_fetch_array() function to fetch the results a row at a time as an associative array. We assign this array to the $row variable, allowing us to access the result fields by using the field name as the array index. For example, we can access the description field data for a gift row as $row["description"]. When no more rows are available, the function returns FALSE; when our while loop encounters this value, it ends the loop.
For each gift item, we compose an HTML table row with form input fields, and prefill the fields with the gift data from the database. We have multiple gifts, each with a description, quantity, and so on; we could create the HTML as below:
<tr>
<td>6</td>
<td><input name='description' value='Avanti Twin Wall Mixing Bowls 2.8 Ltr'
size='60' /></td>
<td><input name='quantity' value='2' /></td>
<td><input name='color' value='Silver' /></td>
<td><input name='shop' value='Myer' size='30' /></td>
<td><input name='price' value='41.65ea (83.30 total)' /></td>
</tr>
<tr>
<td>10</td>
<td><input name='description' value='Baileys Comet 6 Ladder' size='60' /></td>
<td><input name='quantity' value='1' /></td>
<td><input name='color' value='Silver' /></td>
<td><input name='shop' value='Bunnings' size='30' /></td>
<td><input name='price' value='97.50' /></td>
</tr>
This form contains as many description, quantity, color, shop, and price fields as there are gifts. However, we can access only one set through $_POST["description"], $_POST["quantity"], and so on. An easy way to resolve this problem is to name the form fields as arrays, with the ID of each gift used as the index of the array. Thus, for example, the form input for the description of gift 6 is named description[6].
This is the PHP code to generate the HTML form:
// Yes; fetch the gift details a row at a time
while($row = @ mysqli_fetch_array($result))
// Compose the data for this gift into a row of form inputs in the table.
// Add a delete link in the last column of the row.
echo "\n<tr>" .
"\n\t<td>{$row["gift_id"]}</td>".
"\n\t<td><input name='description[{$row['gift_id']}]' ".
"value='{$row["description"]}' size='60' /></td>".
"\n\t<td><input name='quantity[{$row['gift_id']}]' ".
"value='{$row["quantity"]}' /></td>".
"\n\t<td><input name='color[{$row['gift_id']}]' ".
"value='{$row["color"]}' /></td>".
"\n\t<td><input name='shop[{$row['gift_id']}]' ".
"value='{$row["shop"]}' size='30' /></td>".
"\n\t<td><input name='price[{$row['gift_id']}]' ".
"value='{$row["price"]}' /></td>".
"\n\t<td><a href='{$_SERVER['PHP_SELF']}?action=delete&".
"gift_id={$row["gift_id"]}'>Delete</a></td>".
"\n</tr>";
In the last column of each table row, we create an HTML link to the same script with a query string containing values for two variables: the action is set to delete, and the gift_id is set to the ID of the gift displayed in the current row. Clicking on this link will cause the script to delete the gift with that ID; we describe how this is done later in this chapter.
A sample output of the while loop for two gifts—with IDs 6 and 10—is shown below:
<tr>
<td>6</td>
<td><input name='description[6]'
value='Avanti Twin Wall Mixing Bowls 2.8 Ltr' size='60' /></td>
<td><input name='quantity[6]'
value='2' /></td>
<td><input name='color[6]'
value='Silver' /></td>
<td><input name='shop[6]'
value='Myer' size='30' /></td>
<td><input name='price[6]'
value='41.65ea (83.30 total)' /></td>
<td><a href='edit.php?action=delete&gift_id=6'>Delete</a></td>
</tr>
<tr>
<td>10</td>
<td><input name='description[10]'
value='Baileys Comet 6 Ladder' size='60' /></td>
<td><input name='quantity[10]'
value='1' /></td>
<td><input name='color[10]'
value='Silver' /></td>
<td><input name='shop[10]'
value='Bunnings' size='30' /></td>
<td><input name='price[10]'
value='97.50' /></td>
<td><a href='edit.php?action=delete&gift_id=10'>Delete</a></td>
</tr>
Note that the whitespace we added to make the source code more readable doesn’t affect the formatting of the output. We’ve also shown each value starting on a new line for readability.
After displaying all the existing gifts, we add a row of empty form input fields to the table; Jack or Jill can enter gift attributes into these fields and submit the form to add a new gift to the database. Since the gift_id field in our database is an auto-incremented field that starts from 1, we can conveniently use the index 0 to identify fields for the new gift. We could use a different name for the new gift fields—for example, newgift_description, newgift_quantity, and so on, but we’d then need to add more code to process these additional variables on the action page. Once we’ve printed this last table row, we end the table, add a Submit button, and end the form. This ends our showgiftsforedit() function:
...
echo "\n<table border='1'>";
...
// Display a row with blank form inputs to allow a gift to be added
echo "\n<tr><td></td>" .
"\n\t<td><input name='description[0]' size='60' /></td>".
"\n\t<td><input name='quantity[0]' /></td>".
"\n\t<td><input name='color[0]' /></td>".
"\n\t<td><input name='shop[0]' size='30' /></td>".
"\n\t<td><input name='price[0]' /></td>".
"\n</tr>";
// End the table
echo "\n</table>";
// Display a submit button and end the form.
echo "\n<input name='update' type='submit' value='Update data' />";
echo "</form>";
Deleting a Gift
If the user clicks on a Delete link for a form, the same edit.php script is called with the query string action=delete&gift_id=the_gift_id; the script must detect that some data has been passed to it to process. As we mentioned earlier in Logging Users In and Out,” the query-string attribute and value pairs are available in the called script as elements in the $_GET superglobal array. The edit.php script checks whether the $_GET array contains any data; if it does, the script creates and executes an SQL query to delete the corresponding gift from the database. Once the query has been executed, the program proceeds to display the existing gifts for editing as before:
// See if we've arrived here after clicking the delete link
if(count($_GET) && (clean($_GET['action'], 10)=='delete'))
{
// Yes; compose a query to delete the specified gift from the gifts table
$query = "DELETE FROM gifts WHERE gift_id=".clean($_GET['gift_id'], 10);
// Run the query through the connection
if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
}
We must also call the logincheck() function at the top of the edit.php script to identify the user and use this information to prevent anybody but Jack or Jill from deleting gifts.
Processing the Submitted Form
When the user clicks on the “Update data” button to submit the gift editing form, the data is sent by the POST method to the same edit.php script. The form data is accessible through the $_POST superglobal array. For example, the Submit button was created in the HTML form as:
<input name='update' type='submit' value='Update data' />
When the form is submitted, this is available as the variable $_POST["update"], which has the value Update data. We can assume that when the $_POST["update"] variable exists (is set), the user has just submitted the HTML form. If this is the case, we can try to update the information in the gifts table. The script iterates through all existing gift IDs in the database and checks whether there’s any corresponding data in the $_POST array. If there is any submitted data for a given gift ID, we call the update_or_insert_gift() function to update the database.
Normally, each existing gift ID will have a corresponding entry in the HTML form, and so there will be data for it in the $_POST array. However, we have to check for this; we should ensure we don’t run into problems if no data is submitted for a particular gift.
This might happen if you add a new gift and submit the form, then make your browser resubmit the original form data again by reloading the form action page (most browsers warn you if you try to do this with a POST form); the ID of the new gift will not be in the submitted form data. It could also happen if another user adds a new gift between the time that you load the form and the time that you submit it.
To avoid problems, we use the isset() function on each of the POST fields for each of the gift IDs that appear in the database. Since we’ve named the form fields as array elements with the gift ID as the array index, the data items we obtain from the $_POST array are themselves arrays. For example, to access the quantity entered for the gift with ID 7, we would use the variable $_POST["quantity"][7].
After updating the existing gifts, we can call the update_or_insert_gift() function with the fake gift ID of 0 to read any entered information for a new gift and add it to the database:
// See if we've arrived here after clicking the update button;
// if so, update the gift details.
if(isset($_POST['update']))
{
// Define an SQL query to list the gift IDs in the database
$query = "SELECT gift_id FROM gifts";
// Run the query through the connection
if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
// Process the submitted data for each gift ID in the database
while($row = @ mysqli_fetch_array($result))
{
$gift_id=$row["gift_id"];
// Update an existing gift if there is corresponding data
// submitted from the form
if(
isset($_POST["quantity" ][$gift_id]) &&
isset($_POST["description"][$gift_id]) &&
isset($_POST["color" ][$gift_id]) &&
isset($_POST["shop" ][$gift_id]) &&
isset($_POST["price" ][$gift_id])
)
update_or_insert_gift_data($connection, $gift_id);
}
// Process the data submitted in the form fields for the new
// gift; we had assigned this the index 0 in the HTML form.
update_or_insert_gift_data($connection, 0);
}
Now let’s look at update_or_insert_gift(). This function extracts and cleans the data of the various form fields from the $_POST array. It then verifies that all the fields contain some data; if any field is empty, an error message is displayed, and the database is not updated. The error message is not displayed if the data items belong to the last table row—which is a blank row allowing the user to optionally enter data for a new gift—and all the fields are empty. We check for this by concatenating all the fields with the period or dot symbol (.). We display the message if we’re processing any row but the last one, and one of the fields is empty, or if it is the last row and the combined length of all the concatenated strings is not zero.
If some data is available for each gift attribute, the script runs a REPLACE INTO query to update the database. A REPLACE INTO query replaces any existing data for a given key, or automatically creates a new entry if no data already exists for that key:
// Update the data for a gift with the specified gift ID; for a
// gift ID of 0, add a new gift to the database.
function update_or_insert_gift_data($connection, $gift_id)
{
// Extract the data items for the gift attributes from the $_POST array
$quantity =clean($_POST["quantity" ][$gift_id], 5);
$description=clean($_POST["description"][$gift_id], 255);
$color =clean($_POST["color" ][$gift_id], 30);
$shop =clean($_POST["shop" ][$gift_id], 100);
$price =clean($_POST["price" ][$gift_id], 30);
// If the gift_id is 0, this is a new gift, so set the
// gift_id to be empty; MySQL will automatically assign a
// unique gift_id to the new gift.
if($gift_id==0)
$gift_id='';
// If any of the attributes are empty, don't update the database.
if(
!strlen($quantity ) ||
!strlen($description) ||
!strlen($color ) ||
!strlen($shop ) ||
!strlen($price )
)
{
// If this isn't the blank row for optionally adding a new gift,
// or if it is the blank row and the user has actually typed something in,
// display an error message.
if(!empty($gift_id)
||
strlen(
$quantity.
$description.
$color.
$shop.
$price)
)
echo "<font color='red'>There must be no empty fields - not updating:<br />".
"([$quantity], [$description], [$color], [$shop], [$price]</font><br />";
}
else
{
// Add or update the gifts table
$query = "REPLACE INTO gifts ".
"(gift_id, description,shop,quantity,color,price,username)".
" values ('$gift_id', '$description', '$shop', $quantity,
'$color', '$price', NULL)";
// Run the query through the connection
if (@ mysqli_query($connection, $query)==FALSE)
showerror($connection);
}
}
Notice that in our SQL query, we explicitly list field names before specifying the values. The order of the values must match the order that the field names are listed in, but these don’t need to match the order of the fields in the database table; if we had omitted the initial list of field names, the field values would need to be in the same order as the fields in the table. Note also that we haven’t stored the result of the REPLACE INTO query as we would have done for a SELECT query, since only SELECT queries return an answer set.
Loading Sample Gifts
Earlier in Editing the List of Gifts,” we saw how the administrator can manually add gifts to the database. You can, of course, add gifts by running SQL INSERT queries using the MySQL monitor. For example, you can type:
mysql> INSERT INTO gifts VALUES
-> (NULL,'Acme 48-piece dinner set','SomeShop',1,'White','102.10',NULL);
Specifying a NULL value for the first field, gift_id, lets the MySQL server automatically assign an auto-incremented ID; the first gift inserted into the table will have a gift_id of 1, the next one will have a gift_id of 2, and so on.
We also specify a NULL value for the last field, username, since gifts newly loaded in the database are not reserved by any user. When a user reserves a gift, her username is stored in the username field for that gift.
To help save you some typing, we’ve generated a few sample gifts that you can load into your database by running the SQL queries in the populate_wedding_database.sql file that you can download from the book’s home page:
$ mysql --user=fred --password=shhh < populate_wedding_database.sql
Note that this file includes the statement:
DELETE FROM gifts;
to first delete any existing gift entries from the database.
Listing Gifts for Selection
After logging in to the application, users are sent to the list.php page that displays all the gifts that are still available (not yet reserved by a wedding guest). The page also displays the gifts that the user has already reserved.
As with the gift editing page, we create the outline of an HTML page and call on a custom function—in this case, the function showgifts()—to read the gift information from the database and generate the required HTML:
<!DOCTYPE HTML PUBLIC
"-//W3C//DTD HTML 4.0 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Jack and Jill's Wedding Gift Registry</title>
</head>
<body bgcolor='LIGHTBLUE'>
<?php
// Show a logout link
echo "<a href='logout.php'>Logout</a>";
// Check whether the user is Jack or Jill (username is 'jack' or
// 'jill'); if so, show a link to the gift editing page.
if($_SESSION['username']=="jack" || $_SESSION['username']=="jill")
echo " | <a href='edit.php'>Edit gifts</a>";
// Connect to the MySQL DBMS and use the wedding database -
// credentials are in the file db.php
if(!($connection= @ mysqli_connect(
$DB_hostname, $DB_username, $DB_password, $DB_databasename)))
showerror($connection);
// Pre-process the message data for security
if(count($_GET))
$message = clean($_GET["message"], 128);
// If there's a message to show, output it
if (!empty($message))
echo "\n<h3><font color=\"red\"><em>".
urldecode($message)."</em></font></h3>";
echo "\n<h3>Here are some gift suggestions</h3>";
// Show the gifts that are still unreserved
showgifts($connection, SHOW_UNRESERVED_GIFTS);
echo "\n<h3>Your Shopping List</h3>";
// Show the gifts that have been reserved by this user
showgifts($connection, SHOW_GIFTS_RESERVED_BY_THIS_USER);
?>
</body>
</html>
The showgifts() function is defined as:
// Show the user the gifts
//
// Parameters:
// (1) An open connection to the DBMS
// (2) Whether to show the available gifts or the current user's
// shopping list.
function showgifts($connection, $show_user_selection)
{
// Show the gifts...
}
and takes two arguments: an open connection to the MySQL server, and a value to indicate whether to show available gifts (these can be added to the user’s selection) or the gifts reserved by this user (these can be removed from the user’s selection). We could use 0 and 1 for this indicator value. However, these values are not meaningful in this context; does 0 mean we want to show reserved gifts or unreserved gifts? You might be tempted to use the constants FALSE and TRUE here; this doesn’t solve the problem, however, since there’s no clear connection between either of these values and the reserved or unreserved status of a gift.
One useful technique in writing code that is self-documenting—that is, code that is easy to read and understand on its own—is to define and use constants that have meaningful names. For example, instead of 0 and 1, we can use SHOW_UNRESERVED_GIFTS andSHOW_GIFTS_RESERVED_BY_THIS_USER. In Handling errors in production code” in Chapter 14, we used the define() function to store the administrator’s email address in a constant. We can also use this function to define the two constants we need here:
define("SHOW_UNRESERVED_GIFTS", 0);
define("SHOW_GIFTS_RESERVED_BY_THIS_USER", 1);
To show the gifts that are unreserved, and then the gifts that have been reserved by the current user, we call the function once with the last parameter set to SHOW_UNRESERVED_GIFTS, and once with it set to SHOW_GIFTS_RESERVED_BY_THIS_USER.
The function itself first tests whether there are any gifts in the database and displays an error message if there aren’t any:
// Show the user the gifts
//
// Parameters:
// (1) An open connection to the DBMS
// (2) Whether to show the available gifts or the current user's
// shopping list.
// Define constants for use when calling showgifts
define("SHOW_GIFTS_RESERVED_BY_THIS_USER", TRUE);
define("SHOW_UNRESERVED_GIFTS", FALSE);
function showgifts($connection, $show_user_selection)
{
// See whether there are any gifts in the system
$query = "SELECT * FROM gifts";
// Run the query through the connection
if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
// Check whether any gifts were found
if (@ mysqli_num_rows($result) == 0)
// No; print a notice
echo "\n<h3><font color=\"red\">".
"There are no gifts described in the system!</font></h3>";
else
{
// Yes; display the gifts
}
}
In normal operation, there will be gift descriptions in the system; depending on how the function was called, we run a query to list all the unreserved gifts (those that have their username field set to NULL), or all the gifts reserved by the current user (those that have their username field set to$_SESSION['username']):
// If we're showing the available gifts, then set up
// a query to show all unreserved gifts (where username IS NULL)
if ($show_user_selection == SHOW_UNRESERVED_GIFTS)
$query = "SELECT * FROM gifts WHERE username IS NULL ORDER BY description";
else
// Otherwise, set up a query to show all gifts reserved by
// this user
$query = "SELECT * FROM gifts WHERE username = '".
$_SESSION['username']."' ORDER BY description";
// Run the query through the connection
if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
If the query doesn’t retrieve any results, we display a message indicating that no unreserved gifts are available or that the user has not reserved any gifts, depending on which list we’re showing:
// Did we get back any rows?
if (@ mysqli_num_rows($result) == 0)
{
// No data was returned from the query.
// Show an appropriate message
if ($show_user_selection == SHOW_UNRESERVED_GIFTS)
echo "\n<h3><font color=\"red\">No gifts left!</font></h3>";
else
echo "\n<h3><font color=\"red\">Your Basket is Empty!</font></h3>";
}
else
{
// Yes, so show the gifts as a table
}
If we do find some gifts to show, we compose an HTML table and iterate through the results to display the gift attributes:
echo "\n<table border=1 width=100%>";
// Create some headings for the table
echo "\n<tr>" .
"\n\t<th>Quantity</th>" .
"\n\t<th>Gift</th>" .
"\n\t<th>Colour</th>" .
"\n\t<th>Available From</th>" .
"\n\t<th>Price</th>" .
"\n\t<th>Action</th>" .
"\n</tr>";
// Fetch each database table row of the results
while($row = @ mysqli_fetch_array($result))
{
// Display the gift data as a table row
echo "\n<tr>" .
"\n\t<td>{$row["quantity"]}</td>" .
"\n\t<td>{$row["description"]}</td>" .
"\n\t<td>{$row["color"]}</td>" .
"\n\t<td>{$row["shop"]}</td>" .
"\n\t<td>{$row["price"]}</td>";
// Display a link to allow an unreserved gift to be added or
// a reserved gift to be removed...
echo "\n</tr>";
}
echo "\n</table>";
In the last column of each row, we show a link to “Add to Shopping List” if we’re displaying unreserved gifts, or a link to “Remove from Shopping List” if we’re displaying gifts reserved by the user:
// Are we showing the list of gifts reserved by the
// user?
if ($show_user_selection == SHOW_UNRESERVED_GIFTS)
// No. So set up an embedded link that the user can click
// to add the gift to their shopping list by running
// action.php with action=add
echo "\n\t<td><a href=\"action.php?action=add&" .
"gift_id={$row["gift_id"]}\">Add to Shopping List</a></td>";
else
// Yes. So set up an embedded link that the user can click
// to remove the gift from their shopping list by running
// action.php with action=remove
echo "\n\t<td><a href=\"action.php?action=remove&" .
"gift_id={$row["gift_id"]}\">Remove from Shopping list</a></td>";
The links include a query string to specify the action to perform (add an unreserved gift or remove a reserved gift) and the ID of the gift to process; for example, we might have a link:
<a href="action.php?action=add&gift_id=10">Add to Shopping List</a>
Clicking on the link calls the action.php script with this action and gift ID. We describe the operation of this script in the next section.
Selecting and Deselecting Gifts
Users add gifts to their shopping list or remove them by clicking on links in the list.php page. The links call the action.php script with the gift ID and the action parameter set to add or remove. For add, the script attempts to reserve the gift with the specified gift_id for the current guest. Similarly, for remove, the script attempts to remove the gift with the specified gift_id from the current guest’s shopping list. The user is identified by the username session variable ($_SESSION['username']).
The script checks that the user is authenticated using the logincheck() function and that the URL requested by the browser includes attributes and values in a query string. As discussed earlier, the query-string attributes can be accessed as elements of the $_GET superglobal array. Theaction.php script first cleans the values in $_GET['gift_id'] and $_GET['action'] and assigns them to the variables $gift_id and $action:
<?php
// action.php: Add or remove a gift from the user's shopping list
// Include database parameters and related functions
require_once("db.php");
// Check if the user is logged in
// (this also starts the session)
logincheck();
// Secure the user data
if(count($_GET))
{
// Connect to the MySQL DBMS and use the wedding database
// - credentials are in the file db.php
if(!($connection= @ mysqli_connect(
$DB_hostname, $DB_username, $DB_password, $DB_databasename)))
showerror($connection);
$gift_id = clean($_GET['gift_id'], 5);
$action = clean($_GET['action'] , 6);
// ...
The script then checks whether the requested action is either add or remove. If it isn’t, we stop processing to avoid corrupting the database, and also to block an attacker trying to manipulate the behavior of our script. The script will proceed beyond this point only if a valid action has been requested, so we don’t need to add an else clause to the if statement:
// Is the action something we know about?
if($action != "add" && $action != "remove")
// No, it's not; perhaps someone's trying to manipulate the
// URL query string?
die("Unknown action: ".$action);
// The program should reach this point only if the action is add
// or remove, since otherwise processing stops with the die()
// instruction.
// What did the user want us to do?
if ($action == "add")
{
// The user wants to add a new item to their shopping list.
...
}
else // The action is not add, so it must be remove
{
// The user wants to remove an existing item from their shopping list.
}
// Redirect the browser back to list.php
header("Location: list.php?message=" . urlencode($message));
exit;
At the end of the script, after adding or removing the gift, we redirect the user’s browser back to the gift selection page (list.php) with the message we prepared earlier indicating the success or failure of the operation. In practice, when the user clicks on the add or remove link in list.php, the browser requests the action.php script; this quietly performs the update and redirects the browser to the list.php page, leaving the user with the impression that they never left the list page:
// Redirect the browser back to list.php
header("Location: list.php?message=" . urlencode($message));
exit;
Figure 15-4 shows the list of gifts and reserved gifts.
Figure 15-4. The wedding registry list page showing reserved gifts
Adding a Gift
If the requested action is to add the gift to the user’s shopping list, we should ensure that we reserve the gift only if it is still free. The gift was free when it was listed by the list.php script; however, another user could have been viewing the list of gifts at the same time, and they might have selected the same gift first. This gift would then be marked as taken in the database, but the first user wouldn’t know this, because the list of gifts they loaded in their browser was generated when the gift was still free. This is another example of where defensive programming is needed; when developing for the Web, each script is independent, and there are no time limits or controls in our application on when a user can request a script.
If we grant the LOCK TABLES privilege to the MySQL user fred, we can use locks for this part of the code:
1. Run a query to apply a write lock to the gifts table; this prevents any changes to the data in this table until we release the write lock:
2. // Lock the gifts table for writing
3. $query = "LOCK TABLE gifts WRITE";
4. // Run the query through the connection
5. if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
6. Read the username associated with the specified gift_id:
7. // Create a query to retrieve the gift.
8. $query = "SELECT * FROM gifts WHERE gift_id = {$gift_id}";
9. // Run the query through the connection
10.if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
11.If the username associated with the gift is not empty and is not the same as the current user, create a message to tell the user that the gift has just been taken:
12.// Has someone already reserved this? (a race condition)
13.if (!empty($row["username"]) && $row["username"] != $_SESSION['username'])
14. // Yes. So, record a message to show the user
15. $message = "Oh dear... Someone just beat you to that gift!";
16.else
17.{
18. // No...reserve the gift for this user
}
19.If the check shows that the gift is still free, we add the current user’s username to the gift data to indicate that the gift has been reserved:
20.// No. So, create a query that reserves the gift for this user
21.$query = "UPDATE gifts SET username = '{$_SESSION['username']}' ".
22. "WHERE gift_id = {$gift_id}";
23.
24.// Run the query through the connection
25.if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
26.Finally, unlock the gifts table:
27.$query = "UNLOCK TABLES";
28.// Run the query through the connection
29.if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
When developing an application, you should carefully consider whether you can design the process to minimize the duration that locks are held, or avoid locking altogether. This is particularly important if you’re using MyISAM tables, since MyISAM locks are table-level. If you apply a write lock to a MyISAM table, nobody else can write to that table or read from it until you release the lock.
In the approach we’ve just discussed, we lock the gifts table, ensure that the specified gift is not reserved, update the gifts table to reserve the specified gift, and then unlock the gifts table. Thinking through the operation, we realize that we’re in fact worried about trying to reserve a gift that is reserved between the time that our application lists it as unreserved and the time that the application tries to reserve it for a particular user. We can design a single SQL query that will only UPDATE the gifts table if the gift is not reserved:
$query = "UPDATE gifts SET username = '{$_SESSION['username']}' ".
"WHERE gift_id = {$gift_id} AND username IS NULL";
A gift that is reserved by another user—or by the current user—will have a username that is not NULL. If the MySQL server finds a row with the specified gift_id and with no username, it will update it to set the username to that of the current user.
We can check whether the gift was reserved by examining the number of rows affected by the update. Only one row, the one for the gift with the specified gift_id, should be modified. If we identify that the number of affected rows is not 1, then there are two possibilities: the gift was already reserved by another user or by the current user. We can run an additional query to see which of these it is:
// If we found the row and updated it, create a confirmation
// message to show the user
if (mysqli_affected_rows($connection) == 1)
{
$message = "Reserved the gift for you, {$_SESSION['username']}";
}
else // Couldn't reserve the gift because it wasn't free;
{
// Check whether it's already booked by someone other
// than the current user.
$query = "SELECT * FROM gifts ".
"WHERE gift_id = {$gift_id} ".
"AND username = '{$_SESSION['username']}'";
// Run the query through the connection
if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
// Create a message to show the user
if (mysqli_num_rows($result))
$message = "The gift is already reserved for you, ".
"{$_SESSION['username']}";
else
$message = "Oh dear... someone just beat you to that gift!";
}
Removing a Gift
If the requested action is not add, it can only be remove, since only these two values are accepted for further processing by our script. The script checks that the gift is actually reserved by the current guest before freeing it; this check should never fail in practice, unless the same user is logged in twice. This is another example of defensive programming.
As with the add operation, we prepare a message confirming that the gift has been removed if the number of affected rows is one, and an error message if it isn’t:
// Create a query to retrieve the gift.
$query = "SELECT * FROM gifts WHERE gift_id = {$gift_id}";
// Run the query through the connection
if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
// Get the matching gift row;
// (there's only one since the gift_id is the primary key)
// If we don't get exactly one answer, then we have a problem
for($matchedrows=0;($row = @ mysqli_fetch_array($result));$matchedrows++);
if($matchedrows!=1)
die("We've just experienced a technical problem - ".
"please notify the administrator.");
// Double-check they actually have this gift reserved
if (!empty($row["username"]) && $row["username"] != $_SESSION['username'])
// They don't, so record a message to show the user
$message = "That's not your gift, {$_SESSION['username']}!";
else
{
// They do have it reserved. Create a query to unreserve it.
$query = "UPDATE gifts SET username = NULL WHERE gift_id = {$gift_id}";
// Run the query through the connection
if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
// Create a message to show the user
if (mysqli_affected_rows($connection) == 1)
$message = "Removed the gift from your shopping list, ".
"{$_SESSION['username']}";
else
$message = "There was a problem updating. ".
"Please contact the administrator.";
}
An alternative approach would be to include a check for the username in the UPDATE statement, and to execute this statement first. We could then determine whether the gift was in fact reserved by this user by counting the number of affected rows:
// Try to unreserve the gift with the matching username and gift ID
$query = "UPDATE gifts SET username = NULL WHERE gift_id = {$gift_id}".
" AND username='{$_SESSION['username']}'";
// Run the query through the connection
if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
// Create a message to show the user
if (mysqli_affected_rows($connection) == 1)
$message = "Removed the gift from your shopping list, ".
"{$_SESSION['username']}";
else
$message = "Couldn't unreserve the gift - perhaps you hadn't reserved it?";
Resources
There are many excellent resources available for you to learn more about PHP, its libraries, web servers, and building web database applications. We have listed several of these resources at the end of the previous two chapters. You might find the following additional resources helpful:
§ Web Database Applications with PHP and MySQL by Hugh E. Williams and David Lane (O’Reilly)
§ A Programmer’s Introduction to PHP 4.0 by W. Jason Gilmore (Apress)
§ PHP Black Book by Peter Moulding (The Coriolis Group)
§ PHP Functions Essential Reference by Zak Greant et al. (Sams)
§ PHP and MySQL Web Development by Luke Welling and Laura Thomson (Sams)
§ Professional PHP4 Programming by Deepak Thomas et al. (Wrox Press)
§ PHP Developer’s Cookbook by Sterling Hughes and Andrei Zmievski (Sams)
There are also many useful web sites that include tutorials, sample code, online discussion forums, and links to sample PHP applications. The official PHP site has an excellent manual at http://www.php.net/manual, and the links page at http://www.php.net/links.php points to many of these sites, including:
§ http://www.phpbuilder.com
§ http://www.devshed.com/Server_Side/PHP
§ http://www.hotscripts.com/PHP
§ http://php.resourceindex.com
Exercises
1. How can you refer to a PHP script file from within itself?
2. Would you use the urlencode() function with data submitted through the POST method?
3. Why is it a good idea to use the crypt() function on user passwords before storing them?
4. Why is it better to avoid locks where possible?