PHP Recordset Paging

Imagine that you have a recordset containing 100 records, for example. Usually you wouldn't want to display all 100 records at once, as it's far too much information for a web site visitor to take in at once, and it can also make your web pages slow to load. Instead, it's much more desirable to be able to show the user 10 records at a time for example, and let them move back and forth between pages. A perfect example of this is a search engine such as Google, although it can be used on any web site that uses dynamic data.

Advertisement DMXzone Paginator PHP

Add the coolest page navigation to your site and make browsing through large lists of items or tables easy and quick. Choose from 22 different styles, used by many sites such as Digg, Yahoo, Flickr and much more, to fit perfectly with your design and display the total number of items.

All paging styles are fully CSS based, so you can always fine tune the colors and borders to your site design.

 

4.4 Testing the Complete Page

All that's left to do now is to test the completed page in your browser.

You should initially see a display similar to the one below:

Figure 5 - Page showing First Page of Records

Note that because this is the first page of records there is no previous page button displayed. If you click on 2 or the next page button, you should see a result similar to Figure 6 below.

Figure 6 - Page showing Second Page of Records

Note that because this is the second page, the previous page button (<<) is displayed.

4.5 - Using the Recordset Pages with the Results of a Search

In the example so far we've shown all records from the example_data table. We'll finish up by looking at how to create recordset pages that show the results of a search.

Save the recordset_paging.php page as recordset_paging_search.php.  Again you can if you wish download the completed page from http://www.garethdp.com/writing/articles/dmxzone/code/recordsetpaging/.

We will modify the page so that it displays only tutorials written by a particular author, where the authors name is specified in a URL parameter called author.

Switch to Code View, and make the changes shown in blue below to the first block of PHP code at the very top of the page.

<?php
// Include Database Connection File
require_once("G:/webserver/DMXZone/recordset_paging/includes/dbConnection.php");
// Open Database Connection
$dbLink = mysql_connect($dbHost, $dbUser, $dbPass);
if (!$dbLink){ die ("Database: Couldn`t connect to mySQL Server"); }
mysql_select_db($dbName, $dbLink) or die ("Database: Couldn`t open Database");
// Build SQL query to find Total Number of Records
$sql  = "SELECT id ";
$sql .= "FROM example_data ";
$sql .= "WHERE author='" . $_GET['author'] . "'";
// Read in Total Number of Records
$dbResult = mysql_query($sql, $dbLink) or die ("MySQL Error: " . mysql_error() );
$totalRecords = mysql_num_rows($dbResult);
// Calculate Number of Pages Required
$recordsPerPage = 5;
$totalPageNumber = ceil($totalRecords / $recordsPerPage);
// Check for Page Number
if(strlen($_GET['page']) < 1){
     $currentPage = 1;
} else {
     $currentPage = $_GET['page'];
}
// Create Offset Number
$offset  = ($currentPage * $recordsPerPage) - $recordsPerPage;
// Create New SQL Query
$sql  = "SELECT UNIX_TIMESTAMP(date) as dateTime,id,title,author ";
$sql .= "FROM example_data ";
$sql .= "WHERE author='" . $_GET['author'] . "'";
$sql .= "ORDER BY UNIX_TIMESTAMP(date) DESC ";
$sql .= "LIMIT " . $offset . "," . $recordsPerPage;
// Execute SQL Query and Read in Records
$dbResult = mysql_query($sql, $dbLink) or die ("MySQL Error: " . mysql_error() );
$numRecords = mysql_num_rows($dbResult);
$recordset = "";
for($i=0;$i<$numRecords;$i++){
  $recordset[] = mysql_fetch_assoc($dbResult);
}  
// Close Database Connection 
mysql_close($dbLink);
?>

Code Block 8 - Changes required to the first block of PHP code

Here we've added a simple WHERE clause to the SQL queries as shown below:

$sql .= "WHERE author='" . $_GET['author'] . "'";

This filters the records so that it only retrieves the records where the authors name matches the name in the URL parameter author.

You can now test the page, by using a URL similar to the one below:

recordset_paging_search.php?author=Gareth+Downes-Powell

You can see that the records shown are only ones written by myself. However, we have a problem in that if you use the navigation bar, the search parameter author isn't passed from page to page so the search fails. We can easily remedy this by changing the block of code that creates the navigation bar to:

<?php
// Create Dynamic Navigation Bar
$pageURL = $_SERVER['SCRIPT_NAME'];
$html = "";
if($currentPage > 1){
     // Create Previous Page Link (<<)
     $url = $pageURL . "?page=" . ($currentPage - 1) . "&author=" . $_GET['author'];
     $html .= "<a href='" .  $url . "'> << </a>";
     $html .= "&nbsp;";
}
for($i=1; $i <= $totalPageNumber; $i++){
     // Create Numerical Page Links ( 1 2 3 etc )
     $url = $pageURL . "?page=" . $i . "&author=" . $_GET['author'];
     $html .= "<a href='" .  $url . "'>" . $i . "</a>";
     $html .= "&nbsp;";
}
if($currentPage < $totalPageNumber){
     // Create Next Page Link (>>)
     $url = $pageURL . "?page=" . ($currentPage + 1) . "&author=" . $_GET['author'];
     $html .= "<a href='" .  $url . "'> >> </a>";
     $html .= "&nbsp;";
}
?>

Code Block 9 - Changes to the Navigation Bar code to preserve the Search Parameters

In code block 9 above the changes required are highlighted in blue.  You can see that we are just adding the contents of the URL author parameter onto the end of the link, so that it's preserved as the user moves from page to page and the search returns the correct results still.

You can see example output in Figure 7 below.

Figure 7 - Page showing Search Results and using Recordset Paging