Pagination with PHP and PDO
by Kevin Waterson
Contents
- What is pagination?
- Getting Started
- Included Classes
- Retrieving Results
- Fetching Number of Records
- Fetching and Validating Page Number
- Building the Menu
- Displaying the Results
- The HTML Page
- Credits
What is pagination?
We have all seen the little menu's at the top of a page like the one shown here...
<< PREV- 1 2 3 4 5 -NEXT >>
Getting Started
The key to pagination is limiting the results from the SQL query. This is achieved with the LIMIT clause in our SQL statement. The format for LIMIT is as follows.
This SQL statement would fetch all records from my_table beginning at row 20, and fetching the next 40 records. Before we go on, lets make a quick database using the omnipresent periodic table of elements. This is available from the PHPRO projects listing where you will find a database dump.
With the database in place we can continue on with the pagination script. The essential ingredients in build pagination are:
- Number of results
- Number of results per page
- Current page number
To get these three items into variables we need to set variables for them.
Included Classes
To begin with we will create a class to carry out the work of retrieving result sets from the database. For this we will use a simple pager class that contains a single static method that we will send our page information to. The page information includes the number of pages, the limit, and the current page number. With these few paramaters we can calculate the offset and the number of links we will need in the menu. The pager class will return an object of stdClass which provides simple object oriented access to the members within. An array could have been used also with the same result. The class is reproduced here.
<?php
/*
* Example usage
* $pager = new pager();
* $pager->num_results = $product_count;
* $pager->limit = $config->config_values['product']['display_num'];
* $pager->page = $page_num;
* $pager->menu_link = '/category/electrical';
* $pager->menu_link_suffix = '/foo/bar'; ( optional )
* $pager->css_class = 'fubar'; ( optional )
* $pager->run();
* echo $pager;
*
*/
class pager{
/**
*
* Constructor, duh!
*
* @access public
* @param $num_pages
* @param $limit
* @param $page
*
*/
public function __construct( $num_results=null, $limit=null, $page=null )
{
if( !is_null( $num_results ) && !is_null( $limit ) && !is_null( $page ) )
{
$this->num_results = $num_results;
$this->limit = $limit;
$this->page = $page;
$this->run();
}
}
/**
*
* Settor
*
* @param string $name
* @param mixed $value
*
*/
public function __set( $name, $value )
{
switch( $name )
{
case 'menu_link_suffix':
case 'num_results':
case 'menu_link':
case 'css_class':
case 'num_pages':
case 'offset':
case 'limit':
case 'page':
$this->$name = $value;
break;
default: throw new \Exception( "Unable to set $name" );
}
}
/**
*
* Gettor
*
* @param string $name
*
*/
public function __get( $name )
{
switch( $name )
{
case 'menu_link_suffix':
case 'num_results':
case 'menu_link':
case 'css_class':
case 'num_pages':
case 'offset':
case 'limit':
case 'page':
return $this->$name;
break;
default: throw new \Exception( "Unable to get $name" );
}
}
/**
* @calculate paging inforomation
*
* @access public
* @param int $num_pages
* @param int $limit
* @param $page
* @return object
*
**/
public function run()
{
/*** the number of pages ***/
$this->num_pages = ceil( $this->num_results / $this->limit );
$this->page = max( $this->page, 1 );
$this->page = min( $this->page, $this->num_pages );
/*** calculate the offset ***/
$this->offset = ( $this->page - 1 ) * $this->limit;
}
/**
*
* return a HTML string representation of the pager links
* The links are in an <ul> with a CSS class name
*
* @access public
* @retun string
*
*/
public function __toString()
{
$menu = '<ul';
$menu .= isset( $this->css_class ) ? ' class="'.$this->css_class.'"' : '';
$menu .= '>';
/*** if this is page 1 there is no previous link ***/
if($this->page != 1)
{
$menu .= '<li><a href="'.$this->menu_link.'/'.( $this->page - 1 );
$menu .= isset( $this->menu_link_suffix ) ? $this->menu_link_suffix : '';
$menu .= '">PREV</a></li>';
}
/*** loop over the pages ***/
for( $i = 1; $i <= $this->num_pages; $i++ )
{
if( $i == $this->page )
{
$menu .= '<li class="active"><a href="'.$this->menu_link.'/'.$i;
$menu .= isset( $this->menu_link_suffix ) ? $this->menu_link_suffix : '';
$menu .= '">'.$i.'</a></li>';
}
else
{
$menu .= '<li><a href="'.$this->menu_link.'/'.$i;
$menu .= isset( $this->menu_link_suffix ) ? $this->menu_link_suffix : '';
$menu .= '">'.$i.'</a></li>';
}
}
/*** if we are on the last page, we do not need the NEXT link ***/
if( $this->page < $this->num_pages )
{
$menu .= '<li><a href="'.$this->menu_link.'/'.( $this->page + 1 );
$menu .= isset( $this->menu_link_suffix ) ? $this->menu_link_suffix : '';
$menu .= '">Next</a></li>';
}
return $menu;
}
} /*** end of class ***/
?>
A second helper class will also be included to connect to the database. It is a singleton pattern and is shown below. The connection class will be included in the script. If you are not familiar with PDO or the singleton design pattern it is highly recommended you read the Introduction To PHP PDO tutorial.
<?php
class db{
/*** Declare instance ***/
private static $instance = NULL;
/**
*
* the constructor is set to private so
* so nobody can create a new instance using new
*
*/
private function __construct() {
/*** maybe set the db name here later ***/
}
/**
*
* Return DB instance or create intitial connection
*
* @return object (PDO)
*
* @access public
*
*/
public static function getInstance() {
if (!self::$instance)
{
self::$instance = new PDO("mysql:host=localhost;dbname=periodic_table", 'username', 'password');;
self::$instance-> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
return self::$instance;
}
/**
*
* Like the constructor, we make __clone private
* so nobody can clone the instance
*
*/
private function __clone(){
}
} /*** end of class ***/
?>
Retrieving Results
Of course to begin with, we need some results to paginate over, so we need to include our database class and run a query to get the results, but before we can, we need to define exactly how many results need to be fetched from the database. To get these results, we need to include the class definitions (files) from above. Lets start the code:
<?php
/*** make it or break it ***/
error_reporting(E_ALL);
/*** begin the session ***/
session_start();
/*** include the database connection class ***/
include 'db.class.php';
/*** include the pager class ***/
include 'pager.class.php';
/*** set the page name ***/
$page_name = htmlentities($_SERVER['PHP_SELF']);
/*** set the number of results per page ***/
$limit = 20;
The comments in the code describe what is happening above. As with all development script, the error_reporting is set to E_ALL to ensure any issues that arise during the development stage are caught early and can be fixed before other code is added and which may lead to bigger problems further down the track. Next we start a session. You may wonder why we need a session but it will become clear in the next block of code when we use it. After this, the database class definition (file) is included (db.class.php) and the pager class definition (pager.class.php).
Next in the scipt is two variables are set. The first is the name of the current file. You will not that the PHP_SELF variable is wrapped with the PHP function htmlentities(). This is because PHP_SELF is injectable, remember, this value is coming from the user and is not to be trusted. After this the $limit variable is set, this is the number of records to show per page. The only thing left to get now, is the total number of pages.
Fetching the number of records
Of course, we need to get the total number of records from the database. This is where we use our database singleton connection. Lets take a look..
<?php
/*** check the SESSION array for the total_records ***/
if(!isset($_SESSION['total_records']))
{
try
{
/*** first get the count of records ***/
$sql = "SELECT count(atomicnumber) AS total FROM elements";
$stmt = db::getInstance()->prepare($sql);
$stmt->execute();
$_SESSION['total_records'] = $stmt->fetch(PDO::FETCH_COLUMN);
}
catch (Exception $e)
{
$_SESSION['total_records'] = 0;
}
}
?>
Unless the data absolutely has to be dynamic each time, there is no point in loading up the database server needlessly by making an un-needed call. The Google search engine is a good example of this, where they do not give you a count of how many results exist because of the performance penalty. Instead you are presented with a line like
Results 1 - 10 of about 4,170,000.
Counts are estimated and cached.
Fetching and Validating Page Number
The page number in this pagination is governed by a GET variable in the URL. Like all user input, this value must be checked to be sure it is a valid value for our use. In this case the value must be an integer. The value must also not be negative, and must not be greater than the maximum number of results that we acquired in the above script. The PHP filter extension is right tool for this as it provides both validation for check the value is of type INT and that the value is within range. The code below shows how.
<?php
/*** check for a page number in GET ***/
if( filter_has_var(INPUT_GET, "page") == false)
{
/*** no page in GET ***/
$page = 1;
}
/*** if the page number is not an int or not within range, assign it to page 1 ***/
elseif(filter_var($_GET['page'], FILTER_VALIDATE_INT, array("min_range"=>1, "max_range"=>$_SESSION['total_records'])) == false)
{
$page = 1;
}
else
{
/*** if all is well, assign it ***/
$page = (int)$_GET['page'];
}
?>
As you can see, the page variable is extensively checked for type and value. Even when we the value is assigned the the $page variable, the use of (int) assures that the value is always going to be an integer. If you are unfamiliar with the PHP filter extension, swing by http://www.phpro.org/tutorials/Filtering-Data-with-PHP.html for a full explanation of how it works.
Building the Menu
Now that all the pieces are in place, we can build the menu. The menu is created outside of the main HTML code and is only a single variable is used within the HTML code itself. This provides for good seperation of display content, thus allowing the HTML monkies to weave their magic without interfering with the PHP coder or coders.
<?php
/*** if we have no results then there is no point in going on ***/
if($_SESSION['total_records'] == 0)
{
$content = 'No Records Available';
}
else
{
/*** feed the variables to the pager class ***/
$pager = Pager::getPagerData($_SESSION['total_records'], $limit, $page);
/*** retrieve the variables from the pager class ***/
$offset = $pager->offset;
$limit = $pager->limit;
$page = $pager->page;
/*** begin the menu ***/
$menu = '';
/*** if this is page 1 there is no previous link ***/
if($page != 1)
{
$menu .= '<li><a href="'.$page_name.'?page='.($page - 1).'"><< PREV </a></li>';
}
/*** loop over the pages ***/
for ($i = 1; $i <= $pager->numPages; $i++)
{
if ($i == $pager->page)
{
$menu .= '<li class="selected">'.$i.'</li>';
}
else
{
$menu .= '<li><a href="'.$page_name.'?page='.$i.'">'.$i.'</a></li>'."\n";
}
}
/*** if we are on the last page, we do not need the NEXT link ***/
if ($page < $pager->numPages)
{
$menu .= '<li><a href="'.$page_name.'?page='.($page + 1).'"> NEXT >></a></li>';
}
?>
Displaying the Results
With the menu created, the table of results can be created also. The same variables are used so no more calculation is required to slow us down. This is a simple matter of querying the database and allowing PDO to create and associative array of results. The result set is assigned to a varible which, like the menu, is embedded into the HTML code.
<?php
/*** our sql statement ***/
$sql ='SELECT
atomicnumber,
origin,
english
FROM
elements
LIMIT :limit
OFFSET :offset';
/*** run the query ***/
$db = db::getInstance();
$stmt = $db->prepare($sql);
$stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$res = $stmt->fetchAll(PDO::FETCH_ASSOC);
/*** the elements table content ***/
$content = '';
foreach ($res as $el)
{
$content .= '
<tr><td>'.$el['atomicnumber'].'</td>
<td>'.$el['origin'].'</td>
<td>'.$el['english'].'</td></tr>';
}
}
?>
?>
The HTML Page
In the menu building process we used HTML list items as our menu building blocks. With the result set, we used HTML table rows, as the data is tabular in nature. Do not let the CSS zeolats tell you that tables are dead. Tables are for tabular data and not for layout.
The html is quite simple, and contains some simple CSS to format the menu, keeping it horizontal rather than vertical. The two PHP variables $menu and $content that were created above are embedded within the HTML allowing for optimum seperation of HTML and PHP code.
Rather than showing just the HTML code, the whole script is reproduced here for easy cut and paste for your use.
<?php
/*** make it or break it ***/
error_reporting(E_ALL);
/*** begin the session ***/
session_start();
/*** include the database connection class ***/
include 'db.php';
/*** include the pager class ***/
include 'pager.php';
/*** set the page name ***/
$page_name = htmlentities($_SERVER['PHP_SELF']);
/*** set the number of results per page ***/
$limit = 20;
/*** check the SESSION array for the total_records ***/
if(!isset($_SESSION['total_records']))
{
try
{
/*** first get the count of records ***/
$sql = "SELECT count(atomicnumber) AS total FROM elements";
$stmt = db::getInstance()->prepare($sql);
$stmt->execute();
$_SESSION['total_records'] = $stmt->fetch(PDO::FETCH_COLUMN);
}
catch (Exception $e)
{
$_SESSION['total_records'] = 0;
}
}
/*** check for a page number in GET ***/
if( filter_has_var(INPUT_GET, "page") == false)
{
/*** no page in GET ***/
$page = 1;
}
/*** if the page number is not an int or not within range, assign it to page 1 ***/
elseif(filter_var($_GET['page'], FILTER_VALIDATE_INT, array("min_range"=>1, "max_range"=>$_SESSION['total_records'])) == false)
{
$page = 1;
}
else
{
/*** if all is well, assign it ***/
$page = (int)$_GET['page'];
}
/*** if we have no results then there is no point in going on ***/
if($_SESSION['total_records'] == 0)
{
$content = 'No Records Available';
}
else
{
/*** feed the variables to the pager class ***/
$pager = Pager::getPagerData($_SESSION['total_records'], $limit, $page);
/*** retrieve the variables from the pager class ***/
$offset = $pager->offset;
$limit = $pager->limit;
$page = $pager->page;
/*** begin the menu ***/
$menu = '';
/*** if this is page 1 there is no previous link ***/
if($page != 1)
{
$menu .= '<li><a href="'.$page_name.'?page='.($page - 1).'"><< PREV </a></li>';
}
/*** loop over the pages ***/
for ($i = 1; $i <= $pager->num_pages; $i++)
{
if ($i == $pager->page)
{
$menu .= '<li class="selected">'.$i.'</li>';
}
else
{
$menu .= '<li><a href="'.$page_name.'?page='.$i.'">'.$i.'</a></li>'."\n";
}
}
/*** if we are on the last page, we do not need the NEXT link ***/
if ($page < $pager->num_pages)
{
$menu .= '<li><a href="'.$page_name.'?page='.($page + 1).'"> NEXT >></a></li>';
}
/*** our sql statement ***/
$sql ='SELECT
atomicnumber,
origin,
english
FROM
elements
LIMIT :limit
OFFSET :offset';
/*** run the query ***/
$db = db::getInstance();
$stmt = $db->prepare($sql);
$stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$res = $stmt->fetchAll(PDO::FETCH_ASSOC);
/*** the elements table content ***/
$content = '';
foreach ($res as $el)
{
$content .= '
<tr><td>'.$el['atomicnumber'].'</td>
<td>'.$el['origin'].'</td>
<td>'.$el['english'].'</td></tr>';
}
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>PHP Pagination</title>
<style type="text/css">
ul{
margin: 0 auto;
width: 395px;
list-style-type:none;
}
li{
display:inline;
}
li.selected{
float:left;
text-decoration:none;
color:black;
font-weight:bold;
background-color: #c0c0c0;
padding:10px;
padding-bottom: solid 1px red;
}
a{
float:left;
text-decoration:none;
color:green;
padding:10px;
padding-bottom: 1px;
}
a:hover{
border-bottom: solid 1px red;
padding-bottom: 1px;
}
table {
clear:both;
margin: 0 auto;
}
</style>
</head>
</body>
<ul>
<?php echo $menu; ?>
</ul>
<table class="elements">
<thead>
<tr> <th>Atomic Number</th> <th>Latin</th> <th>English</th> </tr>
</thead>
<tbody>
<?php echo $content; ?>
</tbody>
<tfoot>
<tr><th colspan="3">Table of elements</th> </tr>
</tfoot>
</table>
</body>
</html>
Credits
Thanks to Captain Cook for discovering Australia.