JavaScript Workshop » PHP Pagination

The technique of splitting up a information into multiple pages is known as pagination. For web pages it normally means taking a resultset from a database query and only showing a specific number of records per page. There are many variations of how to do this no matter what technology (PHP, ASP, JSP, Coldfusion) you use.

Jonathan Sampson created a very useful screencast (.zip) showing you exactly how to do this using PHP and MySQL. I took his code (.zip) and created my own simple example.

Looking over the code and demo you will see it is a fairly complete solution for any type of data that you may need pagination. Since it is so complete you probably will need a little time to fully understand it (like me), but Jonathan explains it very nicely in the video.

1. Grab the total number of database records and pages:

// Get total number of database entries
$totalCount = "SELECT COUNT(*) as 'Total' FROM pagination";
$rsCount = mysql_query($totalCount, $conn)
$rowCount = mysql_fetch_object($rsCount);
 
// Get our total number of pages
$numOfPages = ceil($rowCount->Total / $perPage);

2. Creating paging links for next and prev as well as each page:

// If our current page, minus our padding, is greater than 1
if (($pageNumber - $padding) > 1) {
 
     print "... ";
 
     // Set our lower limit
     $lowerLimit = $pageNumber - $padding;
 
     // Print all padded numbers between lowerLimit and current
     for ($i = $lowerLimit; $i < $pageNumber; $i++) {
 
          print "<a href=\"page.php?page=".$i."\">".$i."</a> ";
     }
 
} else {
 
     // Print all numbers between current page, and first page
     for ($i = 2; $i < $pageNumber; $i++) {
 
           print "<a href=\"page.php?page=".$i."\">".$i."</a> ";
     }
}
 
// If not on first page, or last page, print current page
if (($pageNumber != 0) && ($pageNumber != 1)
&& ($pageNumber != $numOfPages)) {
 
     print "<b> - " . $pageNumber . " - </b>";
}
 
// If current page, plus padding, is less than the total pages
if (($pageNumber + $padding) < $numOfPages) {
 
     // Set upper limit
     $upperLimit = $pageNumber + $padding;
 
     // Print all number from padded pages above current page
     for ($i = ($pageNumber + 1); $i <= $upperLimit; $i++) {
 
          print "<a href=\"page.php?page=".$i."\">".$i."</a> ";
     }
 
     print "... ";
 
} else {
 
     // Print numbers between number of pages and current page
     for ($i = ($pageNumber + 1); $i < $numOfPages; $i++) {
 
          print "<a href=\"page.php?page=".$i."\">".$i."</a> ";
     }
}

3. Grab the page results and display them:

// Get page results
$sql = "SELECT id, teams FROM pagination ORDER BY id
        LIMIT $startIndex, $perPage";
 
// Get result set
$rs = mysql_query($sql, $conn) or die(mysql_error());
 
// Do we have results?
if (mysql_num_rows($rs) > 0) {
 
     // Show the results
     while($row = mysql_fetch_object($rs)) {
 
          print $row->id;
          print $row->teams;
     }
 
} else {
 
     print "Sorry, no results found.";
}