| There's one final feature that I want to add to view_users.php while I'm at it. 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. Therefore, all I need to do is add some functionality in PHP that will change the ORDER BY clause. The 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 take this concept just a little bit further, I want the sorting links to be contextual. For example, clicking the Last Name column the first time will sort the results in ascending order by last name. Clicking that same column again should sort the results in descending order by last name. This is easily accomplished by toggling the link's parameters. To make sortable links 1. | Open view_users.php (Script 8.5) in your text editor or IDE.
| 2. | Before the main query, define the default links (Script 8.6).
$link1 = "{$_SERVER['PHP_SELF']}  ?sort=lna; $link2 = "{$_SERVER['PHP_SELF']} ?sort=fna; $link3 = "{$_SERVER['PHP_SELF']} ?sort=dra;
Script 8.6. This latest version of the view_users.php script creates clickable links out of the table's column headings. Clicking one of these links redisplays the results in a different order. 
 
These variables define the default link values for the Last Name ($link1), First Name ($link2), and Date Registered ($link3) columns. Each link should be something like view_users.php?sort=XXX, where XXX represents the new sorting order.
I've decided not to hard-code view_users.php as the link because if I later want to apply this same technique to another script, I'll need to find and change the link name multiple times. Instead I use $_SERVER['PHP_SELF'], which is a way for a script to refer to itself (the curly braces are used to avoid parse errors, in case you've forgotten).
The abbreviations for the three links stand for last name ascending, first name ascending, and date registered ascending.
| 3. | Check if a sorting order has already been determined.
if (isset($_GET['sort'])) {
As with $_GET['np'] and $_GET['s'], the sorting order will be passed in the URL, so it should be available in $_GET['sort']. This only applies when the user accesses multiple results pages, though.
| 4. | Begin defining a switch conditional that determines how the results should be sorted.
switch ($_GET['sort']) { case 'lna': $order_by = 'last_name ASC'; $link1 = "{$_SERVER['PHP_SELF'] }?sort=lnd; break; case 'lnd': $order_by = 'last_name DESC'; $link1 = "{$_SERVER['PHP_SELF'] }?sort=lna; break;
The switch checks $_GET['sort'] against several expected values. If, for example, it is equal to lna, 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.
Because I want the links to be contextual, I also change the appropriate link value based upon $_GET['sort']. If the results are going to be displayed in ascending order by last name (lna), then the Last Name column link should be to display the result in descending order by last name (lnd). So for each case in the switch, the appropriate link is given a new value.
| 5. | Complete the switch conditional.
case 'fna': $order_by = 'first_name ASC'; $link2 = "{$_SERVER['PHP_SELF']} ?sort=fnd; break; case 'fnd': $order_by = 'first_name DESC'; $link2 = "{$_SERVER['PHP_SELF']} ?sort=fna; break; case 'dra': $order_by = 'registration_date ASC; $link3 = "{$_SERVER['PHP_SELF']} ?sort=drd; break; case 'drd': $order_by = 'registration_date DESC; $link3 = "{$_SERVER['PHP_SELF']} ?sort=dra; break; default: $order_by = 'registration_date  DESC; break; }
There are six total conditions to check against, plus the default (just in case). For each the $order_by variable is defined as it will be used in the query and the appropriate link is redefined. Since each link has already been given a default value (Step 2), I only need to change a single link's value for each case.
| 6. | Complete the isset() conditional.
$sort = $_GET['sort']; } else { $order_by = 'registration_date ASC; $sort = 'rdd'; }
The $sort variable will need to be appended to the pagination links, like $s and $np. If $_GET['sort'] is set, then $sort should have that value. Otherwise, the page's default $order_by and $sort values are used (Figure 8.14).
Figure 8.14. The first time viewing the page, the results are shown in descending order of registration date. 
| 7. | Modify the query to use the new $order_by variable.
$query = "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 DESC), 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 the dynamically-generated syntax.
| 8. | Modify the table header echo statement to create links out of the column headings.
echo '<table align="center" cellspacing="0 cellpadding="5"> <tr> <td align="left"><b>Edit</b></td> <td align="left"><b>Delete</b> </td> <td align="left"><b><a href="' . $link1 . '">Last Name</a></b> </td> <td align="left"><b><a href="' . $link2 . '">First Name</a></b> </td> <td align="left"><b><a href="' . $link3 . '">Date Registered</a> </b></td> </tr> ';
To make the column headings clickable links, just surround them with the <a> tags. The value of the href attribute for each link has already been determined and assigned to $link1, $link2, and $link3.
| 9. | 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) . '&np=' .$num_pages . '&sort=' . $sort .'">Previous</a> ';
I've added another name=value pair to the Previous link so that the sort order is also sent to each page of results.
| 10. | Repeat Step 9 for the numbered pages and the Next link.
echo '<a href="view_users.php?s=' . (($display * ($i - 1))) . '&np=' . $num_pages . '&sort=' . $sort .'">' . $i . '</a> '; echo '<a href="view_users.php?s=' . ($start + $display) . '&np=' . $num_pages . '&sort=' . $sort .'">Next</a>';
| 11. | Save the file as view_users.php, upload it to your Web server, and run in your Web browser (Figures 8.15 and 8.16).
Figure 8.15. Clicking the Last Name column displays the results in order by last name ascending. 
Figure 8.16. Clicking the Last Name column again (from Figure 8.15) displays the results in order by last name descending. 
|
 Tip
A very important security concept was also demonstrated in this example. Instead of using the value of $_GET['sort'] directly in the query, I check it against assumed values in a switch. If, for some reason, $_GET['sort'] has a value other than I would expect, 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 a SQL query.
|
