| 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 same feature. Paginating query results makes extensive use of the LIMIT SQL clause introduced in Chapter 4. LIMIT restricts which subset of the matched records are actually returned. To paginate the returned results of a query, each page will run the same query using different LIMIT parameters. These parameters will also be passed from page to page in the URL, like the user IDs passed from the view_users.php page. Another, more cosmetic technique will be demonstrated here: displaying each row of the tableeach returned recordusing an alternating background color (Figure 8.10). This effect will be achieved with ease, using the ternary operator (see the sidebar "The Ternary Operator" on page 304). Figure 8.10. 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 here, so be careful as you follow along and make sure that your script matches this one exactly. If altering the existing script seems like too much of a hassle, make your life easier by just writing this one from scratch (you'll obviously have to fill in a few steps on your own using what you already know from the previous version). To paginate view_users.php 1. | Open view_users.php (Script 8.2) in your text editor or IDE.
| 2. | After including the database connection, set the number of records to display per page (Script 8.5).
$display = 10;
Script 8.5. This new version of view_users.php incorporates pagination so that the users are listed over multiple Web browser pages.   
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.
| 3. | Check if the number of required pages has been determined.
if (isset($_GET['np'])) { $num_pages = $_GET['np']; } else {
For this script to display the users over several pages, it will need to determine how many total pages of results will be required. The first time the script is run, this number will be calculated. From then on, the number of pages will be passed to the script as part of the URL, as the $_GET['np'] variable. If this variable is set, its value will be assigned to the $num_pages variable. If not, then the number of pages will need to be calculated.
| 4. | Count the number of records in the database.
$query = "SELECT COUNT(*) FROM users  ORDER BY registration_date ASC; $result = mysql_query ($query); $row = mysql_fetch_array ($result,  MYSQL_NUM); $num_results = $row[0];
Using the COUNT() function, introduced in Chapter 5, "Advanced SQL and MySQL," you can easily see the number of records in the users table. This query will return a single row with a single column: the number of records.
| 5. | Mathematically calculate how many pages are required.
if ($num_records > $display) { $num_pages = ceil ($num_ records/$display); } else { $num_pages = 1; } }
The number of pages used to display the records is based upon the total number of records to be shown and the number to display per page (as set by the $display variable). If there are more rows 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). If $num_records is not greater than $display, only one page is necessary.
| 6. | Determine the starting point in the database.
if (isset($_GET['s'])) { $start = $_GET['s']; } else { $start = 0; }
The second parameter the script will receiveon subsequent viewings of the pagewill be the starting record. Upon initially calling the script, the first 10 records should be retrieved. The second page would show records 10 through 20; the third, 20 through 30; and so forth. The first page will not have an $_GET['s'] variable set, and so $start will 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.
| 7. | Change the query so that it uses the LIMIT clause.
$query = "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;
The LIMIT clause dictates 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.
| 8. | Delete the references to the $num variable.
In order to simplify this script a little bit, I'm deleting both the assignment of the $num variable and the if ($num > 0) conditional. I'm assuming that there are records to be displayed.
| 9. | Before the while loop, initialize the background color variable.
$bg = '#eeeeee';
I initialize a $bg variable (to #eeeeee, a light gray), which will be used as the background color for every other row.
| 10. | Within the while loop, add.
$bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee);
The background color used by each row in the table is assigned to the $bg variable. Because I want this color to alternate, I use this line of code to assign the opposite color to $bg. If it's 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, $bg is equal to #eeeeee 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.
| 11. | Modify the while loop's echo statement so that it prints the table row's background color.
echo '<tr bgcolor="' . $bg . '">
| 12. | After completing the HTML table, begin a section for displaying links to other pages, if necessary.
if ($num_pages > 1) { echo '<br /><p>'; $current_page = ($start/$display) + 1; if ($current_page != 1) { echo '<a href="view_users. php?s=' . ($start - $display) . '&np=' . $num_ pages . '">Previous</a> '; }
If the script requires multiple pages to display all of the records, I'll want to make the appropriate links at the bottom of the page (see Figure 8.11). For these links I'll first determine the current page, which can be calculated as the start number divided by the display number, plus 1. For example, on the second instance of this script, $start will be 10 (because on the first instance, $start is 0), so 10/10 + 1 = 2.
Figure 8.11. After all of the returned records, links are generated to the other result pages. 
If the current page is not the first page, I'll display a Previous link to the earlier result set (Figure 8.12).
Figure 8.12. The Previous link will appear only if the current page is not the first one. 
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.
| 13. | Finish making the links.
for ($i = 1; $i <= $num_pages; $i++) { if ($i != $current_page) { echo '<a href="view_ users.php?s=' . (($display * ($i - 1))) . '&np=' . $num_pages . '">' . $i . '</a> '; } else { echo $i . ' '; } } if ($current_page != $num_pages) { echo '<a href="view_users. php?s=' . ($start + $display) . '&np=' . $num_pages . '">Next</a>'; } echo '</p>'; }
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.
Finally, a Next page link will be displayed, assuming that this is not the final page (Figure 8.13).
Figure 8.13. The final results page will not display a Next link. 
| 14. | Save the file as view_users.php, upload it to your Web server, and test in your Web browser.
|
 Tips
From a security standpoint, it would be better if this script validated $_GET['np'] and $_GET['s'] to ensure they are numeric. Such steps have been omitted for brevity, but note that you should not assume that data received by a PHP page will necessarily be of a certain type or value. Also, 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 mysql_error() function as needed. The ternary operator derives its name from the fact that there are three parts to its structure (the conditional and the two returned values). It is also sometimes referred to as the trinary operator.
The Ternary Operator In this example, I'll be using an operator I have not introduced before, called the ternary operator. Its structure is (condition) ? valueT : valueF
The condition in parentheses will be evaluated; if it is TRUE, the first value will be returned (valueT). If the condition is FALSE, the second value (valueF) will be returned. Because the ternary operator returns a value, the entire structure is often the argument of a function. For example, the line echo (isset($var)) ? 'SET' : 'NOT SET';
will print out SET or NOT SET, depending upon the status of the variable $var. In this version of the view_users.php script, the ternary operator assigns a value to a variable. The variable itself will then be used to dictate the background color of each record in the table. There are certainly other ways to set this value, but the ternary operator is the most concise. |
|
