10. Common Programming Techniques
Sending Values to a Script 300
Now that you have a little PHP and MySQL interaction under your belt, it’s time to take things up a notch. This chapter is similar to Chapter 3, “Creating Dynamic Web Sites,” in that it covers myriad independent topics. But what all of these have in common is that they demonstrate common PHP-MySQL programming techniques. You won’t learn any new functions here; instead, you’ll see how to use the knowledge you already possess to create standard Web functionality.
The examples themselves will broaden the Web application started in the preceding chapter by adding new, popular features. You’ll see several tricks for managing database information, in particular editing and deleting records using PHP. At the same time, a couple of new ways of passing data to your PHP pages will be introduced. The final sections of the chapter add features to the view_users.php page.
Sending Values to a Script
In the examples so far, all of the data received in the PHP script came from what the user entered in a form. There are, however, two different ways you can pass variables and values to a PHP script, both worth knowing.
The first method is to make use of HTML’s hidden input type:
<input type="hidden" name="do" value="this" />
As long as this code is anywhere between the form tags, the variable $_POST['do'] will have a value of this in the handling PHP script, assuming that the form uses the POST method. If the form uses the GET method, then $_GET['do'] would have that value. With that in mind, you can actually skip the creation of the form, and just directly append a name=value pair to the URL:
www.example.com/page.php?do=this
Again, with this specific example, page.php receives a variable called $_GET['do'] with a value of this.
To demonstrate this GET method trick, a new version of the view_users.php script, first created in the last chapter, will be written. This one will provide links to pages that will allow you to edit or delete an existing user’s record. The links will pass the user’s ID to the handling pages, both of which will also be written in this chapter.
To manually send values to a PHP script
1. Open view_users.php (Script 9.6) in your text editor or IDE.
2. Change the SQL query to read (Script 10.1):
$q = "SELECT last_name, first_name, DATE_FORMAT (registration_date, '%M %d, %Y') AS dr, user_id FROM users ORDER BY registration_date ASC";
Script 10.1. The view_users.php script, started in Chapter 9, “Using PHP with MySQL,” now modified so that it presents Edit and Delete links, passing the user’s ID number along in each URL.
1 <?php # Script 10.1 - view_users.php #3
2 // This script retrieves all the records from the users table.
3 // This new version links to edit and delete pages.
4
5 $page_title = 'View the Current Users';
6 include ('includes/header.html'),
7 echo '<h1>Registered Users</h1>';
8
9 require_once ('../mysqli_connect.php'),
10
11 // Define the query:
12 $q = "SELECT last_name, first_name, DATE_FORMAT(registration_date, '%M %d, %Y') AS dr,
user_id FROM users ORDER BY registration_date ASC";
13 $r = @mysqli_query ($dbc, $q);
14
15 // Count the number of returned rows:
16 $num = mysqli_num_rows($r);
17
18 if ($num > 0) { // If it ran OK, display the records.
19
20 // Print how many users there are:
21 echo "<p>There are currently $num registered users.</p>
";
22
23 // Table header:
24 echo '<table align="center" cellspacing="3" cellpadding="3" width="75%">
25 <tr>
26 <td align="left"><b>Edit</b></td>
27 <td align="left"><b>Delete</b></td>
28 <td align="left"><b>Last Name</b></td>
29 <td align="left"><b>First Name</b></td>
30 <td align="left"><b>Date Registered</b></td>
31 </tr>
32 ';
33
34 // Fetch and print all the records:
35 while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
36 echo '<tr>
37 <td align="left"><a href="edit_user.php?id=' . $row['user_id'] . '">Edit</a></td>
38 <td align="left"><a href="delete_user.php?id=' . $row['user_id'] . '">Delete</a></td>
39 <td align="left">' . $row['last_name'] . '</td>
40 <td align="left">' . $row['first_name'] . '</td>
41 <td align="left">' . $row['dr'] . '</td>
42 </tr>
43 ';
44 }
45
46 echo '</table>';
47 mysqli_free_result ($r);
48
49 } else { // If no records were returned.
50 echo '<p class="error">There are currently no registered users.</p>';
51 }
52
53 mysqli_close($dbc);
54
55 include ('includes/footer.html'),
56 ?>
The query has been changed in a couple of ways. First, the first and last names are selected separately, not concatenated together. Second, the user_id is also now being selected, as that value will be necessary in creating the links.
3. Add three more columns to the main table:
echo '<table align="center" cellspacing="3" cellpadding="3" width="75%">
<tr>
<td align="left"><b>Edit</b> </td>
<td align="left"><b>Delete</b> </td>
<td align="left"><b>Last Name</b> </td>
<td align="left"><b>First Name</b> </td>
<td align="left"><b>Date Registered</b> </td>
</tr>
';
In the previous version of the script, there were only two columns: one for the name and another for the date the user registered. The name column has been separated into its two parts and two new columns added: one for the Edit link and another for the Delete link.
4. Change the echo statement within the while loop to match the table’s new structure:
echo '<tr>
<td align="left"><a href="edit_user.php?id=' . $row['user_id'] . '">Edit</a></td>
<td align="left"><a href="delete_user.php?id=' . $row['user_id'] . '">Delete</a></td>
<td align="left">' . $row['last_name'] . '</td>
<td align="left">' . $row['first_name'] . '</td>
<td align="left">' . $row['dr'] . '</td>
</tr>
';
For each record returned from the database, this line will print out a row with five columns. The last three columns are obvious and easy to create: just refer to the returned column name.
For the first two columns, which provide links to edit or delete the user, the syntax is slightly more complicated. The desired end result is HTML code like <a href="edit_user.php?id=X">Edit</a>, where X is the user’s ID. Knowing this, all the PHP code has to do is print $row['user_id'] for X, being mindful of the quotation marks to avoid parse errors.
Because the HTML attributes use a lot of double quotation marks and this echo statement requires a lot of variables to be printed, I find it easiest to use single quotes for the HTML and then to concatenate the variables to the printed text.
5. Save the file as view_users.php, place it in your Web directory, and run it in your Web browser 
.
The revised version of the view_users.php page, with new columns and links.
There’s no point in clicking the new links, though, as those scripts have not yet been created.
6. If you want, view the HTML source of the page to see each dynamically generated link 
.
Part of the HTML source of the page (see 
) shows how the user’s ID is added to each link’s URL.
To append multiple variables to a URL, use this syntax: page.php?name1=value1& name2=value2&name3=value3. It’s simply a matter of using the ampersand, plus another name=value pair.
One trick to adding variables to URLs is that strings should be encoded to ensure that the value is handled properly. For example, the space in the string Elliott Smith would be problematic. The solution then is to use the urlencode( ) function:
$url = 'page.php?name=' . urlencode('Elliott Smith'),
You only need to do this when programmatically adding values to a URL. When a form uses the GET method, it automatically encodes the data.
Using Hidden Form Inputs
In the preceding example, a new version of the view_users.php script was written. This one now includes links to the edit_user.php and delete_user.php pages, passing each a user’s ID through the URL. This next example, delete_user.php, will take the passed user ID and allow the administrator to delete that user. Although you could have this page simply execute a DELETE query as soon as the page is accessed, for security purposes (and to prevent an inadvertent deletion), there should be multiple steps :
1. The page must check that it received a numeric user ID.
2. A message will confirm that this user should be deleted.
3. The user ID will be stored in a hidden form input.
4. Upon submission of this form, the user will actually be deleted.
This graphic outlines the steps to be executed by the user deletion script.
To use hidden form inputs
1. Begin a new PHP document in your text editor or IDE, to be named delete_user.php (Script 10.2):
<?php # Script 10.2 - delete_user.php
2. Include the page header:
$page_title = 'Delete a User';
include ('includes/header.html'),
echo '<h1>Delete a User</h1>';
This document will use the same template system as the other pages in the application. See Chapters 9 and 3 for clarification, if needed.
3. Check for a valid user ID value:
if ( (isset($_GET['id'])) && (is_numeric($_GET['id'])) ) {
$id = $_GET['id'];
} elseif ( (isset($_POST['id'])) && (is_numeric($_POST['id'])) ) {
$id = $_POST['id'];
} else {
echo '<p class="error">This page has been accessed in error.</p>';
include ('includes/footer.html'),
exit( );
}
Script 10.2. This script expects a user ID to be passed to it through the URL. It then presents a confirmation form and deletes the user upon submission.
1 <?php # Script 10.2 - delete_user.php
2 // This page is for deleting a user record.
3 // This page is accessed through view_
users.php.
4
5 $page_title = 'Delete a User';
6 include ('includes/header.html'),
7 echo '<h1>Delete a User</h1>';
8
9 // Check for a valid user ID, through
GET or POST:
10 if ( (isset($_GET['id'])) && (is_numeric
($_GET['id'])) ) { // From view_users.php
11 $id = $_GET['id'];
12 } elseif ( (isset($_POST['id'])) &&
(is_numeric($_POST['id'])) ) { // Form
submission.
13 $id = $_POST['id'];
14 } else { // No valid ID, kill the script.
15 echo '<p class="error">This page has
been accessed in error.</p>';
16 include ('includes/footer.html'),
17 exit( );
18 }
19
20 require_once ('../mysqli_connect.php'),
21
22 // Check if the form has been submitted:
23 if ($_SERVER['REQUEST_METHOD'] = = 'POST') {
24
25 if ($_POST['sure'] = = 'Yes') {
// Delete the record.
26
27 // Make the query:
28 $q = "DELETE FROM users WHERE
user_id=$id LIMIT 1";
29 $r = @mysqli_query ($dbc, $q);
30 if (mysqli_affected_rows($dbc) = =
1) { // If it ran OK.
31
32 // Print a message:
33 echo '<p>The user has been
deleted.</p>';
34
35 } else { // If the query did not
run OK.
36 echo '<p class="error">The user
could not be deleted due to a
system error.</p>'; // Public
message.
37 echo '<p>' . mysqli_error($dbc)
. '<br />Query: ' . $q . '</p>';
// Debugging message.
38 }
39
40 } else { // No confirmation of deletion.
41 echo '<p>The user has NOT been
deleted.</p>';
42 }
43
44 } else { // Show the form.
45
46 // Retrieve the user's information:
47 $q = "SELECT CONCAT(last_name, ',
', first_name) FROM users WHERE
user_id=$id";
48 $r = @mysqli_query ($dbc, $q);
49
50 if (mysqli_num_rows($r) = = 1) {
// Valid user ID, show the form.
51
52 // Get the user's information:
53 $row = mysqli_fetch_array ($r,
MYSQLI_NUM);
54
55 // Display the record being deleted:
56 echo "<h3>Name: $row[0]</h3>
57 Are you sure you want to delete
this user?";
58
59 // Create the form:
60 echo '<form action="delete_user.
php" method="post">
61 <input type="radio" name="sure"
value="Yes" /> Yes
62 <input type="radio" name="sure"
value="No" checked="checked" /> No
63 <input type="submit" name="submit"
value="Submit" />
64 <input type="hidden" name="id"
value="' . $id . '" />
65 </form>';
66
67 } else { // Not a valid user ID.
68 echo '<p class="error">This page
has been accessed in error.</p>';
69 }
70
71 } // End of the main submission conditional.
72
73 mysqli_close($dbc);
74
75 include ('includes/footer.html'),
76 ?>
This script relies upon having a valid user ID, to be used in a DELETE query’s WHERE clause. The first time this page is accessed, the user ID should be passed in the URL (the page’s URL will end with delete_user.php?id=X), after clicking the Delete link in the view_users.php page. The first if condition checks for such a value and that the value is numeric.
As you will see, the script will then store the user ID value in a hidden form input. When the form is submitted (back to this same page), the script will receive the ID through $_POST. The second condition checks this and, again, that the ID value is numeric.
If neither of these conditions is TRUE, then the page cannot proceed, so an error message is displayed and the script’s execution is terminated .
If the page does not receive a number ID value, this error is shown.
4. Include the MySQL connection script:
require_once ('../mysqli_connect.php'),
Both of this script’s processes—showing the form and handling the form—require a database connection, so this line is outside of the main submit conditional (Step 5).
5. Begin the main submit conditional:
if ($_SERVER['REQUEST_METHOD'] = = 'POST') {
To test for a form submission, the script uses the same conditional first explained in Chapter 3, and also used in Chapter 9.
6. Delete the user, if appropriate:
if ($_POST['sure'] = = 'Yes') {
$q = "DELETE FROM users WHERE user_id=$id LIMIT 1";
$r = @mysqli_query ($dbc, $q);
The form 
will force the user to click a radio button to confirm the deletion. This little requirement prevents any accidents. Thus, the handling process first checks that the correct radio button was selected. If so, a basic DELETE query is defined, using the user’s ID in the WHERE clause. A LIMIT clause is added to the query as an extra precaution.
The page confirms the user deletion using this simple form.
7. Check if the deletion worked and respond accordingly:
if (mysqli_affected_rows($dbc) = = 1) {
echo '<p>The user has been deleted.</p>';
} else {
echo '<p class="error">The user could not be deleted due to a system error.</p>';
echo '<p>' . mysqli_error($dbc) . '<br />Query: ' . $q . '</p>';
}
The mysqli_affected_rows( ) function checks that exactly one row was affected by the DELETE query. If so, a happy message is displayed 
. If not, an error message is sent out.
If you select Yes in the form (see 
) and click Submit, this should be the result.
Keep in mind that it’s possible that no rows were affected without a MySQL error occurring. For example, if the query tries to delete the record where the user ID is equal to 42000 (and if that doesn’t exist), no rows will be deleted but no MySQL error will occur. Still, because of the checks made when the form is first loaded, it would take a fair amount of hacking by the user to get to that point.
8. Complete the $_POST['sure'] conditional:
} else {
echo '<p>The user has NOT been deleted.</p>';
}
If the user did not explicitly check the Yes button, the user will not be deleted and this message is displayed 
.
If you do not select Yes in the form, no database changes are made.
9. Begin the else clause of the main submit conditional:
} else {
The page will either handle the form or display it. Most of the code prior to this takes effect if the form has been submitted (if $_SERVER['REQUEST_METHOD'] equals POST). The code from here on takes effect if the form has not yet been submitted, in which case the form should be displayed.
10. Retrieve the information for the user being deleted:
$q = "SELECT CONCAT(last_name, ', ', first_name) FROM users WHERE user_id=$id";
$r = @mysqli_query ($dbc, $q);
if (mysqli_num_rows($r) = = 1) {
$row = mysqli_fetch_array ($r, MYSQLI_NUM);
To confirm that the script received a valid user ID and to state exactly who is being deleted (refer back to ), the to-be-deleted user’s name is retrieved from the database 
.
Running the same SELECT query in the mysql client.
The conditional—checking that a single row was returned—ensures that a valid user ID was provided to the script. If so, that one record is fetched into the $row variable.
11. Display the record being deleted:
echo "<h3>Name: $row[0]</h3>
Are you sure you want to delete this user?";
To help prevent accidental deletions of the wrong record, the name of the user to be deleted is first displayed. That value is available in $row[0], because the mysqli_fetch_array( ) function (in Step 10), uses the MYSQLI_NUM constant, thereby assigning the returned record to $row as an indexed array. The user’s name is the first, and only, column in the returned record, so it’s indexed at 0 (as arrays normally begin indexing at 0).
12. Create the form:
echo '<form action="delete_user.php" method="post">
<input type="radio" name="sure" value="Yes" /> Yes
<input type="radio" name="sure" value="No" checked="checked" /> No
<input type="submit" name="submit" value="Submit" />
<input type="hidden" name="id" value="' . $id . '" />
</form>';
The form posts back to this same page. It contains two radio buttons, with the same name but different values, a submit button, and a hidden input. The most important step here is that the user ID ($id) is stored as a hidden form input so that the handling process can also access this value 
.
13. Complete the mysqli_num_rows( ) conditional:
} else {
echo '<p class="error">This page has been accessed in error.</p>';
}
If no record was returned by the SELECT query (because an invalid user ID was submitted), this message is displayed.
If you see this message when you test this script but don’t understand why, apply the standard debugging steps outlined at the end of Chapter 8, “Error Handling and Debugging.”
14. Complete the PHP page:
}
mysqli_close($dbc);
include ('includes/footer.html'),
?>
The closing brace finishes the main submission conditional. Then the MySQL connection is closed and the footer is included.
15. Save the file as delete_user.php and place it in your Web directory (it should be in the same directory as view_users.php).
16. Run the page by first clicking a Delete link in the view_users.php page.
Hidden form elements don’t display in the Web browser but are still present in the HTML source code 
. For this reason, never store anything there that must be kept truly secure.
Using hidden form inputs and appending values to a URL are just two ways to make data available to other PHP pages. Two more methods—cookies and sessions—are thoroughly covered in Chapter 12, “Cookies and Sessions.”
The user ID is stored as a hidden input so that it’s available when the form is submitted.
Script 10.3. The edit_user.php page first displays the user’s current information in a form. Upon submission of the form, the record will be updated in the database.
1 <?php # Script 10.3 - edit_user.php
2 // This page is for editing a user
record.
3 // This page is accessed through view_
users.php.
4
5 $page_title = 'Edit a User';
6 include ('includes/header.html'),
7 echo '<h1>Edit a User</h1>';
8
9 // Check for a valid user ID, through
GET or POST:
10 if ( (isset($_GET['id'])) && (is_numeric($_
GET['id'])) ) { // From view_users.php
11 $id = $_GET['id'];
12 } elseif ( (isset($_POST['id'])) &&
(is_numeric($_POST['id'])) ) { // Form
submission.
13 $id = $_POST['id'];
14 } else { // No valid ID, kill the script.
15 echo '<p class="error">This page has
been accessed in error.</p>';
16 include ('includes/footer.html'),
17 exit( );
18 }
19
20 require_once ('../mysqli_connect.php'),
21
22 // Check if the form has been submitted:
23 if ($_SERVER['REQUEST_METHOD'] = = 'POST') {
24
25 $errors = array( );
26
27 // Check for a first name:
28 if (empty($_POST['first_name'])) {
29 $errors[ ] = 'You forgot to enter
your first name.';
30 } else {
31 $fn = mysqli_real_escape_string($dbc,
trim($_POST['first_name']));
32 }
33
34 // Check for a last name:
35 if (empty($_POST['last_name'])) {
36 $errors[ ] = 'You forgot to enter
your last name.';
37 } else {
38 $ln = mysqli_real_escape_string($dbc,
trim($_POST['last_name']));
39 }
40
41 // Check for an email address:
42 if (empty($_POST['email'])) {
43 $errors[ ] = 'You forgot to enter
your email address.';
44 } else {
45 $e = mysqli_real_escape_string
($dbc, trim($_POST['email']));
46 }
47
48 if (empty($errors)) { // If
everything's OK.
49
50 // Test for unique email address:
51 $q = "SELECT user_id FROM users
WHERE email='$e' AND user_id != $id";
52 $r = @mysqli_query($dbc, $q);
53 if (mysqli_num_rows($r) = = 0) {
54
55 // Make the query:
56 $q = "UPDATE users SET first_
name='$fn', last_name='$ln',
email='$e' WHERE user_id=$id
LIMIT 1";
57 $r = @mysqli_query ($dbc, $q);
58 if (mysqli_affected_rows($dbc)
= = 1) { // If it ran OK.
59
60 // Print a message:
61 echo '<p>The user has been
edited.</p>';
62
63 } else { // If it did not run OK.
64 echo '<p class="error">The user
could not be edited due to a
system error. We apologize for
any inconvenience.</p>';
// Public message.
65 echo '<p>' . mysqli_error($dbc)
. '<br />Query: ' . $q . '</
p>'; // Debugging message.
66 }
67
68 } else { // Already registered.
69 echo '<p class="error">The
email address has already been
registered.</p>';
70 }
71
72 } else { // Report the errors.
73
74 echo '<p class="error">The
following error(s) occurred:<br />';
75 foreach ($errors as $msg) {
// Print each error.
76 echo " - $msg<br />
";
77 }
78 echo '</p><p>Please try again.</p>';
79
80 } // End of if (empty($errors)) IF.
81
82 } // End of submit conditional.
83
84 // Always show the form...
85
86 // Retrieve the user's information:
87 $q = "SELECT first_name, last_name,
email FROM users WHERE user_id=$id";
88 $r = @mysqli_query ($dbc, $q);
89
90 if (mysqli_num_rows($r) = = 1) { // Valid
user ID, show the form.
91
92 // Get the user's information:
93 $row = mysqli_fetch_array ($r,
MYSQLI_NUM);
94
95 // Create the form:
96 echo '<form action="edit_user.php"
method="post">
97 <p>First Name: <input type="text"
name="first_name" size="15" maxlength="15"
value="' . $row[0] . '" /></p>
98 <p>Last Name: <input type="text"
name="last_name" size="15" maxlength="30"
value="' . $row[1] . '" /></p>
99 <p>Email Address: <input type="text"
name="email" size="20" maxlength="60"
value="' . $row[2] . '" /> </p>
100 <p><input type="submit" name="submit"
value="Submit" /></p>
101 <input type="hidden" name="id" value="' .
$id . '" />
102 </form>';
103
104 } else { // Not a valid user ID.
105 echo '<p class="error">This page has
been accessed in error.</p>';
106 }
107
108 mysqli_close($dbc);
109
110 include ('includes/footer.html'),
111 ?>
Editing Existing Records
A common practice with database-driven Web sites is having a system in place so that you can easily edit existing records. This concept seems daunting to many beginning programmers, but the process is surprisingly straightforward. For the following example—editing registered user records—the process combines skills the book has already taught:
• Making sticky forms
• Using hidden inputs
• Validating registration data
• Executing simple queries
This next example is generally very similar to delete_user.php and will also be linked from the view_users.php script (when a person clicks Edit). A form will be displayed with the user’s current information, allowing for those values to be changed . Upon submitting the form, if the data passes all of the validation routines, an UPDATE query will be run to update the database.
The form for editing a user’s record.
To edit an existing database record
1. Begin a new PHP document in your text editor or IDE, to be named edit_user.php (Script 10.3):
<?php # Script 10.3 - edit_user.php
$page_title = 'Edit a User';
include ('includes/header.html'),
echo '<h1>Edit a User</h1>';
2. Check for a valid user ID value:
if ( (isset($_GET['id'])) && (is_numeric($_GET['id'])) ) {
$id = $_GET['id'];
} elseif ( (isset($_POST['id'])) && (is_numeric($_POST['id'])) ) {
$id = $_POST['id'];
} else {
echo '<p class="error">This page has been accessed in error.</p>';
include ('includes/footer.html'),
exit( );
}
This validation routine is exactly the same as that in delete_user.php, confirming that a numeric user ID has been received, whether the page has first been accessed from view_users.php (the first condition) or upon submission of the form (the second condition).
3. Include the MySQL connection script and begin the main submit conditional:
require_once ('../mysqli_connect. php'),
if ($_SERVER['REQUEST_METHOD'] = = 'POST') {
$errors = array( );
Like the registration examples you have already done, this script makes use of an array to track errors.
if (empty($_POST['first_name'])) {
$errors[ ] = 'You forgot to enter your first name.';
} else {
$fn = mysqli_real_escape_string ($dbc, trim($_POST ['first_name']));
}
The form is like a registration page but without the password fields (see the second tip). The form data can therefore be validated by applying the same techniques used in a registration script. As with a registration example, the validated data is trimmed and then run through mysqli_real_escape_string( ) for security.
5. Validate the last name and email address:
if (empty($_POST['last_name'])) {
$errors[ ] = 'You forgot to enter your last name.';
} else {
$ln = mysqli_real_escape_string ($dbc, trim($_POST ['last_name']));
}
if (empty($_POST['email'])) {
$errors[ ] = 'You forgot to enter your email address.';
} else {
$e = mysqli_real_escape_string ($dbc, trim($_POST['email']));
}
6. If there were no errors, check that the submitted email address is not already in use:
if (empty($errors)) {
$q = "SELECT user_id FROM users WHERE email='$e' AND user_id != $id";
$r = @mysqli_query($dbc, $q);
if (mysqli_num_rows($r) = = 0) {
The integrity of the database and of the application as a whole partially depends upon having unique email address values in the users table. That requirement guarantees that the login system, which uses a combination of the email address and password (to be developed in Chapter 12), works. Because the form allows for altering the user’s email address (see ), special steps have to be taken to ensure uniqueness of that value across every database record. To understand this query, consider two possibilities....
In the first, the user’s email address is being changed. In this case you just need to run a query making sure that that particular email address isn’t already registered: SELECT user_id FROM users WHERE email='$e'.
In the second possibility, the user’s email address will remain the same. In this case, it’s okay if the email address is already in use, because it’s already in use for this user.
To write one query that will work for both possibilities, don’t check to see if the email address is being used, but rather see if it’s being used by anyone else, hence:
SELECT user_id FROM users WHERE
email='$e' AND user_id != $id
If this query returns no records, it’s safe to run the UPDATE query.
7. Update the database:
$q = "UPDATE users SET first_name='$fn', last_name= '$ln', email='$e' WHERE user_id= $id LIMIT 1";
$r = @mysqli_query ($dbc, $q);
The UPDATE query is similar to examples you could have seen in Chapter 5, “Introduction to SQL.” The query updates three fields—first name, last name, and email address—using the values submitted by the form. This system works because the form is preset with the existing values. So, if you edit the first name in the form but nothing else, the first name value in the database is updated using this new value, but the last name and email address values are “updated” using their current values. This system is much easier than trying to determine which form values have changed and updating just those in the database.
8. Report on the results of the update:
if (mysqli_affected_rows($dbc) = = 1) {
echo '<p>The user has been edited.</p>';
} else {
echo '<p class="error">The user could not be edited due to a system error. We apologize for any inconvenience.</p>';
echo '<p>' . mysqli_error($dbc) . '<br />Query: ' . $q . '</p>';
}
The mysqli_affected_rows( ) function will return the number of rows in the database affected by the most recent query. If any of the three form values was altered, then this function will return the value 1. This conditional tests for that and prints a message indicating success or failure.
Keep in mind that the mysqli_affected_rows( ) function will return a value of 0 if an UPDATE command successfully ran but didn’t actually affect any records. Therefore, if you submit this form without changing any of the form values, a system error is displayed, which may not technically be correct. Once you have this script effectively working, you could change the error message to indicate that no alterations were made if mysqli_affected_rows( ) returns 0.
9. Complete the email conditional:
} else {
echo '<p class="error">The email address has already been registered.</p>';
}
This else completes the conditional that checked if an email address was already being used by another user. If so, that message is printed.
10. Complete the $errors conditional:
} else {
echo '<p class="error">The following error(s) occurred:<br />';
foreach ($errors as $msg) {
echo " - $msg<br />
";
}
echo '</p><p>Please try again.</p>';
}
The else is used to report any errors in the form (namely, a lack of a first name, last name, or email address), just like in the registration script.
11. Complete the submission conditional:
} // End of submit conditional.
The final closing brace completes the main submit conditional. In this example, the form will be displayed whenever the page is accessed. After submitting the form, the database will be updated, and the form will be shown again, now displaying the latest information.
12. Retrieve the information for the user being edited:
$q = "SELECT first_name, last_name, email FROM users WHERE user_id=$id";
$r = @mysqli_query ($dbc, $q);
if (mysqli_num_rows($r) = = 1) {
$row = mysqli_fetch_array ($r, MYSQLI_NUM);
In order to populate the form elements, the current information for the user must be retrieved from the database. This query is similar to the one in delete_user.php. The conditional—checking that a single row was returned—ensures that a valid user ID was provided.
13. Display the form:
echo '<form action="edit_user.php" method="post">
<p>First Name: <input type="text" name="first_name" size="15" maxlength="15" value="' . $row[0] . '" /></p>
<p>Last Name: <input type="text" name="last_name" size="15" maxlength="30" value="' . $row[1] . '" /></p>
<p>Email Address: <input type="text" name="email" size="20" maxlength="60" value="' . $row[2] . '" /></p>
<p><input type="submit" name="submit" value="Submit" /></p>
<input type="hidden" name="id" value="' . $id . '" />
</form>';
The form has but three text inputs, each of which is made sticky using the data retrieved from the database. Again, the user ID ($id) is stored as a hidden form input so that the handling process can also access this value.
14. Complete the mysqli_num_rows( ) conditional:
} else {
echo '<p class="error">This page has been accessed in error.</p>';
}
If no record was returned from the database, because an invalid user ID was submitted, this message is displayed.
mysqli_close($dbc);
include ('includes/footer.html'),
?>
16. Save the file as edit_user.php and place it in your Web directory (in the same folder as view_users.php).
17. Run the page by first clicking an Edit link in the view_users.php page and .
The new values are displayed in the form after successfully updating the database (compare with the form values in ).
If you try to change a record to an existing email address or if you omit an input, errors are reported.
As written, the sticky form always shows the values retrieved from the database. This means that if an error occurs, the database values will be used, not the ones the user just entered (if those are different). To change this behavior, the sticky form would have to check for the presence of $_POST variables, using those if they exist, or the database values if not.
This edit page does not include the functionality to change the password. That concept was already demonstrated in password.php (Script 9.7). If you would like to incorporate that functionality here, keep in mind that you cannot display the current password, as it is stored in a hashed format (i.e., it’s not decryptable). Instead, just present two boxes for changing the password (the new password input and a confirmation). If these values are submitted, update the password in the database as well. If these inputs are left blank, do not update the password in the database.
Paginating Query Results
Pagination is a concept you’re familiar with even if you don’t know the term. When you use a search engine like Google, it displays the results as a series of pages and not as one long list. The view_users.php script could benefit from this feature.
Paginating query results makes extensive use of the LIMIT SQL clause introduced in Chapter 5. LIMIT restricts which subset of the matched records is actually returned. To paginate the returned results of a query, each iteration of the page will run the same query using different LIMIT parameters. The first page viewing will request the first X records; the second page viewing, the second group of X records; and so forth. To make this work, two values must be passed from page to page in the URL, like the user IDs passed from the view_users.php page. The first value is the total number of pages to be displayed. The second value is an indicator of which records the page should display with this iteration (i.e., where to begin fetching records).
Another, more cosmetic technique will be demonstrated here: displaying each row of the table—each returned record—using an alternating background color . This effect will be achieved with ease, using the ternary operator (see the sidebar “The Ternary Operator”).
Alternating the table row colors makes this list of users more legible (every other row has a light gray background).
There’s a lot of good, new information to be covered here, so to make it easier to follow along, let’s write this version from scratch instead of trying to modify Script 10.1.
To paginate view_users.php
1. Begin a new PHP document in your text editor or IDE, to be named view_users.php (Script 10.4):
<?php # Script 10.4 - view_users.php #4
$page_title = 'View the Current Users';
include ('includes/header.html'),
echo '<h1>Registered Users</h1>';
require_once ('../mysqli_connect. php'),
2. Set the number of records to display per page:
$display = 10;
By establishing this value as a variable here, you’ll make it easy to change the number of records displayed on each page at a later date. Also, this value will be used multiple times in this script, so it’s best represented as a single variable (you could also represent this value as a constant, if you’d rather).
3. Check if the number of required pages has been already determined:
if (isset($_GET['p']) && is_numeric ($_GET['p'])) {
$pages = $_GET['p'];
} else {
Script 10.4. This new version of view_users.php incorporates pagination so that the users are listed over multiple Web browser pages.
1 <?php # Script 10.4 - view_users.php #4
2 // This script retrieves all the records
from the users table.
3 // This new version paginates the query
results.
4
5 $page_title = 'View the Current Users';
6 include ('includes/header.html'),
7 echo '<h1>Registered Users</h1>';
8
9 require_once ('../mysqli_connect.php'),
10
11 // Number of records to show per page:
12 $display = 10;
13
14 // Determine how many pages there are...
15 if (isset($_GET['p']) && is_numeric
($_GET['p'])) { // Already been
determined.
16
17 $pages = $_GET['p'];
18
19 } else { // Need to determine.
20
21 // Count the number of records:
22 $q = "SELECT COUNT(user_id) FROM
users";
23 $r = @mysqli_query ($dbc, $q);
24 $row = @mysqli_fetch_array ($r,
MYSQLI_NUM);
25 $records = $row[0];
26
27 // Calculate the number of pages...
28 if ($records > $display) { // More
than 1 page.
29 $pages = ceil ($records/$display);
30 } else {
31 $pages = 1;
32 }
33
34 } // End of p IF.
35
36 // Determine where in the database to
start returning results...
37 if (isset($_GET['s']) && is_numeric
($_GET['s'])) {
38 $start = $_GET['s'];
39 } else {
40 $start = 0;
41 }
42
43 // Define the query:
44 $q = "SELECT last_name, first_name,
DATE_FORMAT(registration_date, '%M
%d, %Y') AS dr, user_id FROM users
ORDER BY registration_date ASC LIMIT
$start, $display";
45 $r = @mysqli_query ($dbc, $q);
46
47 // Table header:
48 echo '<table align="center" cellspacing="0"
cellpadding="5" width="75%">
49 <tr>
50 <td align="left"><b>Edit</b></td>
51 <td align="left"><b>Delete</b></td>
52 <td align="left"><b>Last Name</b></td>
53 <td align="left"><b>First Name</b></
td>
54 <td align="left"><b>Date Registered
</b></td>
55 </tr>
56 ';
57
58 // Fetch and print all the records....
59
60 $bg = '#eeeeee'; // Set the initial
background color.
61
62 while ($row = mysqli_fetch_array($r,
MYSQLI_ASSOC)) {
63
64 $bg = ($bg= ='#eeeeee' ? '#ffffff' :
'#eeeeee'), // Switch the
background color.
65
66 echo '<tr bgcolor="' . $bg . '">
67 <td align="left"><a href="edit_
user.php?id=' . $row['user_id'] .
'">Edit</a></td>
68 <td align="left"><a href="delete_
user.php?id=' . $row['user_id'] .
'">Delete</a></td>
69 <td align="left">' . $row['last_
name'] . '</td>
70 <td align="left">' . $row['first_
name'] . '</td>
71 <td align="left">' . $row['dr'] .
'</td>
72 </tr>
73 ';
74
75 } // End of WHILE loop.
76
77 echo '</table>';
78 mysqli_free_result ($r);
79 mysqli_close($dbc);
80
81 // Make the links to other pages, if
necessary.
82 if ($pages > 1) {
83
84 // Add some spacing and start a
paragraph:
85 echo '<br /><p>';
86
87 // Determine what page the script is
on:
88 $current_page = ($start/$display)
+ 1;
89
90 // If it's not the first page, make a
Previous link:
91 if ($current_page != 1) {
92 echo '<a href="view_users.
php?s=' . ($start - $display) .
'&p=' . $pages . '">Previous</a> ';
93 }
94
95 // Make all the numbered pages:
96 for ($i = 1; $i <= $pages; $i+ +) {
97 if ($i != $current_page) {
98 echo '<a href="view_users.
php?s=' . (($display *
($i - 1))) . '&p=' . $pages .
'">' . $i . '</a> ';
99 } else {
100 echo $i . ' ';
101 }
102 } // End of FOR loop.
103
104 // If it's not the last page, make a
Next button:
105 if ($current_page != $pages) {
106 echo '<a href="view_users.
php?s=' . ($start + $display) .
'&p=' . $pages . '">Next</a>';
107 }
108
109 echo '</p>'; // Close the paragraph.
110
111 } // End of links section.
112
113 include ('includes/footer.html'),
114 ?>
For this script to display the users over several page viewings, it will need to determine how many total pages of results will be required. The first time the script is run, this number has to be calculated. For every subsequent call to this page, the total number of pages will be passed to the script in the URL, making it available in $_GET['p']. If this variable is set and is numeric, its value will be assigned to the $pages variable. If not, then the number of pages will need to be calculated.
4. Count the number of records in the database:
$q = "SELECT COUNT(user_id) FROM users";
$r = @mysqli_query ($dbc, $q);
$row = @mysqli_fetch_array ($r, MYSQLI_NUM);
$records = $row[0];
Using the COUNT( ) function, introduced in Chapter 8, “Advanced SQL and MySQL,” you can easily find the number of records in the users table (i.e., the number of records to be paginated). This query will return a single row with a single column: the number of records .
The result of running the counting query in the mysql client.
5. Mathematically calculate how many pages are required:
if ($records > $display) {
$pages = ceil ($records/$display);
} else {
$pages = 1;
}
The number of pages required to display all of the records is based upon the total number of records to be shown and the number to display per page (as assigned to the $display variable). If there are more records in the result set than there are records to be displayed per page, multiple pages will be required. To calculate exactly how many pages, take the next highest integer from the division of the two (the ceil( ) function returns the next highest integer). For example, if there are 25 records returned and 10 are being displayed per page, then 3 pages are required (the first page will display 10, the second page 10, and the third page 5). If $records is not greater than $display, only one page is necessary.
6. Complete the number of pages if-else:
} // End of p IF.
7. Determine the starting point in the database:
if (isset($_GET['s']) && is_numeric ($_GET['s'])) {
$start = $_GET['s'];
} else {
$start = 0;
}
The second parameter the script will receive—on subsequent viewings of the page—will be the starting record. This corresponds to the first number in a LIMIT x, y clause. Upon initially calling the script, the first ten records—0 through 10—should be retrieved (because $display has a value of 10). The second page would show records 10 through 19; the third, 20 through 29; and so forth.
The first time this page is accessed, the $_GET['s'] variable will not be set, and so $start should be 0 (the first record in a LIMIT clause is indexed at 0). Subsequent pages will receive the $_GET['s'] variable from the URL, and it will be assigned to $start.
8. Write the SELECT query with a LIMIT clause:
$q = "SELECT last_name, first_name, DATE_FORMAT (registration_date, '%M %d, %Y') AS dr, user_id FROM users ORDER BY registration_date ASC LIMIT $start, $display";
$r = @mysqli_query ($dbc, $q);
The LIMIT clause dictates with which record to begin retrieving ($start) and how many to return ($display) from that point. The first time the page is run, the query will be SELECT last_name, first_name ... LIMIT 0, 10. Clicking to the next page will result in SELECT last_name, first_name ... LIMIT 10, 10.
9. Create the HTML table header:
echo '<table align="center" cellspacing="0" cellpadding="5" width="75%">
<tr>
<td align="left"><b>Edit</b></td>
<td align="left"><b>Delete</b></td>
<td align="left"><b>Last Name</b></td>
<td align="left"><b>First Name</b></td>
<td align="left"><b>Date Registered</b></td>
</tr>
';
In order to simplify this script a little bit, I’m assuming that there are records to be displayed. To be more formal, this script, prior to creating the table, would invoke the mysqli_num_rows( ) function and have a conditional that confirms that some records were returned.
10. Initialize the background color variable:
$bg = '#eeeeee';
To make each row have its own background color, a variable will be used to store that color. To start, the $bg variable is assigned a value of #eeeeee, a light gray. This color will alternate with white (#ffffff).
11. Begin the while loop that retrieves every record, and then swap the background color:
while ($row = mysqli_fetch_array ($r, MYSQLI_ASSOC)) {
$bg = ($bg= ='#eeeeee' ? '#ffffff' : '#eeeeee'),
The background color used by each row in the table is assigned to the $bg variable. Because the background color should alternate, this one line of code will, upon each iteration of the loop, assign the opposite color to $bg. If $bg is equal to #eeeeee, then it will be assigned the value of #ffffff and vice versa (again, see the sidebar for the syntax and explanation of the ternary operator). For the first row fetched, $bg is initially equal to #eeeeee (see Step 10) and will therefore be assigned #ffffff, making a white background.
For the second row, $bg is not equal to #eeeeee, so it will be assigned that value, making a gray background.
12. Print the records in a table row:
echo '<tr bgcolor="' . $bg . '">
<td align="left"><a href="edit_user.php?id=' . $row ['user_id'] . '">Edit</a></td>
<td align="left"><a href=
"delete_user.php?id=' . $row ['user_id'] . '">Delete</a></td>
<td align="left">' . $row['last_name'] . '</td>
<td align="left">' . $row ['first_name'] . '</td>
<td align="left">' . $row['dr'] . '</td>
</tr>
';
This code only differs in one way from that in the previous version of this script: the initial TR tag now includes the bgcolor attribute, whose value will be the $bg variable (so #eeeeee and #ffffff, alternating).
13. Complete the while loop and the table, free up the query result resources, and close the database connection:
} // End of WHILE loop.
echo '</table>';
mysqli_free_result ($r);
mysqli_close($dbc);
14. Begin a section for displaying links to other pages, if necessary:
if ($pages > 1) {
echo '<br /><p>';
If the script requires multiple pages to display all of the records, it needs the appropriate links at the bottom of the page .
15. Determine the current page being viewed:
$current_page = ($start/$display) + 1;
To make the links, the script must first determine the current page. This can be calculated as the starting number divided by the display number, plus 1. For example, on the second viewing of this page, $start will be 10 (because on the first instance, $start is 0), making the $current_page value 2: (10/10) + 1 = 2.
16. Create a link to the previous page, if necessary:
if ($current_page != 1) {
echo '<a href="view_users.php?s=' . ($start - $display) . '&p=' . $pages . '">Previous</a> ';
}
If the current page is not the first page, it should also have a Previous link to the earlier result set . This isn’t strictly necessary, but is nice.
Each link will be made up of the script name, plus the starting point and the number of pages. The starting point for the previous page will be the current starting point minus the number being displayed. These values must be passed in every link, or else the pagination will fail.
The Previous link will appear only if the current page is not the first one (compare with ).
for ($i = 1; $i <= $pages; $i+ +) {
if ($i != $current_page) {
echo '<a href="view_users.php?s=' . (($display * ($i - 1))) . '&p=' . $pages . '">' . $i . '</a> ';
} else {
echo $i . ' ';
}
}
The bulk of the links will be created by looping from 1 to the total number of pages. Each page will be linked except for the current one. For each link, the starting point value, s, will be calculated by multiplying the number of records to display per page times one less than $i. For example, on page 3, $i – 1 is 2, meaning s will be 20.
18. Create a Next link:
if ($current_page != $pages) {
echo '<a href="view_users. php?s=' . ($start + $display) . '&p=' . $pages . '">Next</a>';
}
Finally, a Next page link will be displayed, assuming that this is not the final page .
The final results page will not display a Next link (compare with and ).
19. Complete the page:
echo '</p>';
} // End of links section.
include ('includes/footer.html'),
?>
20. Save the file as view_users.php, place it in your Web directory, and test it in your Web browser.
This example paginates a simple query, but if you want to paginate a more complex query, like the results of a search, it’s not that much more complicated. The main difference is that whatever terms are used in the query must be passed from page to page in the links. If the main query is not exactly the same from one viewing of the page to the next, the pagination will fail.
If you run this example and the pagination doesn’t match the number of results that should be returned (for example, the counting query indicates there are 150 records but the pagination only creates 3 pages, with 10 records on each), it’s most likely because the main query and the COUNT( ) query are too different. These two queries will never be the same, but they must perform the same join (if applicable) and have the same WHERE and/or GROUP BY clauses to be accurate.
No error handling has been included in this script, as I know the queries function as written. If you have problems, remember your MySQL/SQL debugging steps: print the query, run it using the mysql client or phpMyAdmin to confirm the results, and invoke the mysqli_error( ) function as needed.
Script 10.5. This latest version of the view_users.php script creates clickable links out of the table’s column headings.
1 <?php # Script 10.5 - view_users.php #5
2 // This script retrieves all the records
from the users table.
3 // This new version allows the results
to be sorted in different ways.
4
5 $page_title = 'View the Current Users';
6 include ('includes/header.html'),
7 echo '<h1>Registered Users</h1>';
8
9 require_once ('../mysqli_connect.php'),
10
11 // Number of records to show per page:
12 $display = 10;
13
14 // Determine how many pages there are...
15 if (isset($_GET['p']) && is_numeric($_GET
['p'])) { // Already been determined.
16 $pages = $_GET['p'];
17 } else { // Need to determine.
18 // Count the number of records:
19 $q = "SELECT COUNT(user_id) FROM users";
20 $r = @mysqli_query ($dbc, $q);
21 $row = @mysqli_fetch_array ($r,
MYSQLI_NUM);
22 $records = $row[0];
23 // Calculate the number of pages...
24 if ($records > $display) { // More
than 1 page.
25 $pages = ceil ($records/$display);
26 } else {
27 $pages = 1;
28 }
29 } // End of p IF.
30
31 // Determine where in the database to
start returning results...
32 if (isset($_GET['s']) && is_numeric($_GET
['s'])) {
33 $start = $_GET['s'];
34 } else {
35 $start = 0;
36 }
37
38 // Determine the sort...
39 // Default is by registration date.
40 $sort = (isset($_GET['sort'])) ?
$_GET['sort'] : 'rd';
41
42 // Determine the sorting order:
43 switch ($sort) {
44 case 'ln':
45 $order_by = 'last_name ASC';
46 break;
47 case 'fn':
48 $order_by = 'first_name ASC';
49 break;
50 case 'rd':
51 $order_by = 'registration_date
ASC';
52 break;
53 default:
54 $order_by = 'registration_date
ASC';
55 $sort = 'rd';
56 break;
57 }
58
59 // Define the query:
60 $q = "SELECT last_name, first_name,
DATE_FORMAT(registration_date, '%M %d,
%Y') AS dr, user_id FROM users ORDER
BY $order_by LIMIT $start, $display";
61 $r = @mysqli_query ($dbc, $q); // Run the
query.
62
63 // Table header:
64 echo '<table align="center" cellspacing="0"
cellpadding="5" width="75%">
65 <tr>
66 <td align="left"><b>Edit</b></td>
67 <td align="left"><b>Delete</b></td>
68 <td align="left"><b><a href="view_users.
php?sort=ln">Last Name</a></b></td>
69 <td align="left"><b><a href="view_users.
php?sort=fn">First Name</a></b></td>
70 <td align="left"><b><a href="view_users.
php?sort=rd">Date Registered</a></b></td>
71 </tr>
72 ';
73
74 // Fetch and print all the records....
75 $bg = '#eeeeee';
76 while ($row = mysqli_fetch_array($r,
MYSQLI_ASSOC)) {
77 $bg = ($bg= ='#eeeeee' ? '#ffffff' :
'#eeeeee'),
78 echo '<tr bgcolor="' . $bg . '">
79 <td align="left"><a href="edit_
user.php?id=' . $row['user_id'] .
'">Edit</a></td>
80 <td align="left"><a href="delete_
user.php?id=' . $row['user_id'] .
'">Delete</a></td>
81 <td align="left">' . $row['last_
name'] . '</td>
82 <td align="left">' . $row['first_
name'] . '</td>
83 <td align="left">' . $row['dr'] .
'</td>
84 </tr>
85 ';
86 } // End of WHILE loop.
87
88 echo '</table>';
89 mysqli_free_result ($r);
90 mysqli_close($dbc);
91
92 // Make the links to other pages, if
necessary.
93 if ($pages > 1) {
94
95 echo '<br /><p>';
96 $current_page = ($start/$display) +
1;
97
98 // If it's not the first page, make a
Previous button:
99 if ($current_page != 1) {
100 echo '<a href="view_users.
php?s=' . ($start - $display) .
'&p=' . $pages . '&sort=' .
$sort . '">Previous</a> ';
101 }
102
103 // Make all the numbered pages:
104 for ($i = 1; $i <= $pages; $i+ +) {
105 if ($i != $current_page) {
106 echo '<a href="view_users.
php?s=' . (($display * ($i -
1))) . '&p=' . $pages .
'&sort=' . $sort . '">' .
$i . '</a> ';
107 } else {
108 echo $i . ' ';
109 }
110 } // End of FOR loop.
111
112 // If it's not the last page, make a
Next button:
113 if ($current_page != $pages) {
114 echo '<a href="view_users.
php?s=' . ($start + $display) .
'&p=' . $pages . '&sort=' .
$sort . '">Next</a>';
115 }
116
117 echo '</p>'; // Close the paragraph.
118
119 } // End of links section.
120
121 include ('includes/footer.html'),
122 ?>
Making Sortable Displays
To wrap up this chapter, there’s one final feature that could be added to view_users.php. In its current state, the list of users is displayed in order by the date they registered. It would be nice to be able to view them by name as well.
From a MySQL perspective, accomplishing this task is easy: just change the ORDER BY clause of the SELECT query. Therefore, to add a sorting feature to the script merely requires additional PHP code that will change the ORDER BY clause. A logical way to do this is to link the column headings so that clicking them changes the display order. As you hopefully can guess, this involves using the GET method to pass a parameter back to this page indicating the preferred sort order.
To make sortable links
1. Open view_users.php (Script 10.4) in your text editor or IDE, if it is not already.
2. After determining the starting point ($s), define a $sort variable (Script 10.5):
$sort = (isset($_GET['sort'])) ? $_GET['sort'] : 'rd';
The $sort variable will be used to determine how the query results are to be ordered. This line uses the ternary operator (see the sidebar in the previous section of the chapter) to assign a value to $sort. If $_GET['sort'] is set, which will be the case after the user clicks any link, then $sort should be assigned that value. If $_GET['sort'] is not set, then $sort is assigned a default value of rd (short for registration date).
3. Determine how the results should be ordered:
switch ($sort) {
case 'ln':
$order_by = 'last_name ASC';
break;
case 'fn':
$order_by = 'first_name ASC';
break;
case 'rd':
$order_by = 'registration_date ASC';
break;
default:
$order_by = 'registration_date ASC';
$sort = 'rd';
break;
}
The switch checks $sort against several expected values. If, for example, it is equal to ln, then the results should be ordered by the last name in ascending order. The assigned $order_by variable will be used in the SQL query.
If $sort has a value of fn, then the results should be in ascending order by first name. If the value is rd, then the results will be in ascending order of registration date. This is also the default case. Having this default case here protects against a malicious user changing the value of $_GET['sort'] to something that could break the query.
4. Modify the query to use the new $order_by variable:
$q = "SELECT last_name, first_name, DATE_FORMAT(registration_date, '%M %d, %Y') AS dr, user_id FROM users ORDER BY $order_by LIMIT $start, $display";
By this point, the $order_by variable has a value indicating how the returned results should be ordered (for example, registration_date ASC), so it can be easily added to the query. Remember that the ORDER BY clause comes before the LIMIT clause. If the resulting query doesn’t run properly for you, print it out and inspect its syntax.
5. Modify the table header echo statement to create links out of the column headings:
echo '<table align="center" cellspacing="0" cellpadding="5" width="75%">
<tr>
<td align="left"><b>Edit</b></td>
<td align="left"><b>Delete</b></td>
<td align="left"><b><a href="view_users.php?sort=ln">Last Name</a></b></td>
<td align="left"><b><a href="view_users.php?sort=fn">First Name</a></b></td>
<td align="left"><b><a href="view_users.php?sort=rd">Date Registered</a></b></td>
</tr>
';
To turn the column headings into clickable links, just surround them with the A tag. The value of the href attribute for each link corresponds to the acceptable values for $_GET['sort'] (see the switch in Step 3).
6. Modify the echo statement that creates the Previous link so that the sort value is also passed:
echo '<a href="view_users.php?s=' . ($start - $display) . '&p=' . $pages . '&sort=' . $sort . '"> Previous</a> ';
Add another name=value pair to the Previous link so that the sort order is also sent to each page of results. If you don’t, then the pagination will fail, as the ORDER BY clause will differ from one page to the next.
7. Repeat Step 6 for the numbered pages and the Next link:
echo '<a href="view_users.php?s=' . (($display * ($i - 1))) . '&p=' . $pages . '&sort=' . $sort . '">' . $i . '</a> ';
echo '<a href="view_users.php?s=' . ($start + $display) . '&p=' . $pages . '&sort=' . $sort . '"> Next</a>';
8. Save the file as view_users.php, place it in your Web directory, and run it in your Web browser and .
After clicking the first name column, the results are shown in ascending order by first name.
After clicking the Last Name column, and then clicking to the second paginated display, the page shows the second group of results in ascending order by last name.
A very important security concept was also demonstrated in this example. Instead of using the value of $_GET['sort'] directly in the query, it’s checked against assumed values in a switch. If, for some reason, $_GET['sort'] has a value other than would be expected, the query uses a default sorting order. The point is this: don’t make assumptions about received data, and don’t use unvalidated data in an SQL query.
Review and Pursue
If you have any problems with the review questions or the pursue prompts, turn to the book’s supporting forum (www.LarryUllman.com/forums/).
Review
• What is the standard sequence of steps for debugging PHP-MySQL problems (explicitly conveyed at the end of Chapter 8)?
• What are the two ways of passing values to a PHP script (aside from user input)?
• What security measures do the delete_user.php and edit_user.php scripts take to prevent malicious or accidental deletions?
• Why is it safe to use the $id value in queries without running it through mysqli_real_escape_string( ) first?
• In what situation will the mysqli_affected_rows( ) function return a false negative (i.e., report that no records were affected despite the fact that the query ran without error)?
• What is the ternary operator? How is it used?
• What two values are required to properly paginate query results?
• How do you alter a query so that its results are paginated?
• If a paginated query is based upon additional criteria (beyond those used in a LIMIT clause), what would happen if those criteria are not also passed along in every pagination link?
• Why is it important not to directly use the value of $_GET['sort'] in a query?
• Why is it important to pass the sorting value along in each pagination link?
Pursue
• Change the delete_user.php and edit_user.php pages so that they both display the user being affected in the browser window’s title bar.
• Modify edit_user.php so that you can also change a user’s password.
• If you’re up for a challenge, modify edit_user.php so that the form elements’ values come from $_POST, if set, and the database if not.
• Change the value of the $display variable in view_users.php to alter the pagination.
• Paginate another query result, such as a list of accounts or customers found in the banking database.
• Create delete and edit scripts for the banking database. You’ll have to factor in the foreign key constraints in place, which limit, for example, the deletion of customers that still have accounts.