Pagination with PHP and Mysql

Requirements

PHP 5.3+
mysqli connection library
MySQL 5+

Introduction

This article gives an overview of how to add pagination to your application. If you have an application that has to display multiple rows of data it is advisable to break this information up into pages so as not to overwhelm the reader.

The Basics
MySQL has a useful feature LIMIT which allows you to select how many rows you want to retrieve from a query and what row to start from. It is therefore easy to break your rows up into pages all you need is

PageSize - specifying how many rows per page you are interested in
PageNumber - identifies a particular page
TotalRows - how many rows there are in the dataset

With the above information it is simple to build the queries to retreive the data you need

The query to retrieve the data from the dataset would need to look something like this

SELECT field1, field2, ... 
                        FROM table 
                        WHERE condition 
                        LIMIT StartRow, TotalRows
                      

Select all Open in new window

All we need to do is calculate the value of [StartRow] and [TotalRows].
Where [StartRow] and [TotalRows] are calculated and added to your query by your script

StartRow
This is simply a product of the PageSize and the requested PageNo - 1.

LIMIT works from a 0 base but people are more used to pages starting from 1 so we need to subtract 1 from the human readable page to get the start page for Limit

Example
If our PageSize = 10 and PageNumber is 3 then we calculate StartRow as

StartRow = 10 x (3 -1) = 20

TotalRows is simply the PageSize which you can define as constant in your script OR add functionality for the user to select how many rows they want to see on a page.

What is the Total number of Pages in the Recordset?
N It would be useful to show the visitor how many pages there are in the table. How do we find the total number of rows that would have been retrieved had we not added LIMIT to the query?

One way is to run another query with COUNT to return the number of available rows

SELECT COUNT(*) FROM table WHERE condition
                      

Select all Open in new window

This will work but MySQL provides another way for us to do this using the SQL_CALC_FOUND_ROWS.

If we add SQL_CALC_FOUND_ROWS to our query MySQL will automatically store the total number of rows in the dataset. We can retrieve this value by calling the FOUND_ROWS() function in MySQL.

Example

SELECT SQL_CALC_FOUND_ROWS, field1, field2, ... 
                        FROM table 
                        WHERE condition 
                        LIMIT 20,10
                      

Select all Open in new window

Followed by

SELECT FOUND_ROWS()
                      

Select all Open in new window

Putting it all together

The code below is a fully functional pagination example. Before we can use it though we need to create the data.

Client Table

CREATE TABLE `client` (
                        `id` int(11) NOT NULL auto_increment,
                        `firstname` varchar(50) default NULL,
                        `surname` varchar(50) default NULL,
                        `email` varchar(50) default NULL,
                        PRIMARY KEY  (`id`)
                      )
                      

Select all Open in new window

The code makes use of two classes

The Database class is a wrapper for the mysqli connection library

database.php

// Our connection variables - in a real project these would most likely be 
                      // included in a config file that would be included in the application 
                      // deprecating the requirement to include them here.
                      // They are reproduced here for clarity.
                      
                      define('db_server','localhost');
                      define('db_user','user');
                      define('db_password','password');
                      define('db_database','ee');
                      
                      class Database
                      {
                        private $connection;  // A property to hold our database connection
                        
                        function __construct()
                        {
                          $this->connection = new mysqli(
                            db_server, 
                            db_user, 
                            db_password, 
                            db_database);
                        }
                        
                        // A static method to return the current database connection
                        // The function ensures a single instance of the database 
                        // object is used in the application
                        static function &getConnection()
                        {
                          static $connection;
                      
                          // If a database object has not been instantiated then do so
                          if (!is_object($connection)) {
                            $connection = new Database();
                          }
                          
                          return $connection;
                        }
                        
                        // Wrapper function for the query method
                        // This can be expanded with additional parameters to 
                        // use prepared statements etc
                        function query($query)
                        {
                          return $this->connection->query($query);
                        }
                      }
                      

Select all Open in new window

The Result class manages the results we retrieve from the database and renders the client table and pagination

results.php

// The pg_pagesize constant determines how many results you want 
                      // to see on a page. Again, this could be defined in the config file. 
                      // In more advanced implementations this can be modifiable by the 
                      // reader to show more or less items on a page.
                      
                      define('pg_pagesize', 10);
                      
                      class Results
                      {
                        private $pagenumber = null; // Requested page number
                        private $result = null;   // Results from querying the client table
                        private $db = null;     // Instance of the Database singleton class
                        private $total = 0;     // Total number of records in the client table
                        
                        // The constructor takes a requested pagenumber
                        // It builds and executes the query against the client's table
                        // It also calculates total number of rows in the client's table
                        function __construct($pagenumber)
                        {
                          // We want to work with pagenumbers starting from 0 - but humans 
                          // are used to page numbers starting from 1 so we need to compensate.
                          $this->pagenumber = $pagenumber - 1;
                          
                          // Store the pagenumber in the session so we don't annoy users
                          // who navigate away from the page and want to see the same 
                          // results when they return
                          $_SESSION['pagenumber'] = $this->pagenumber;
                          
                          // Calculate the start row
                          $start = pg_pagesize * $this->pagenumber;
                          
                          // Build the query to fetch the required rows from the database 
                          // starting from the requested page
                          $this->db = Database::getConnection();
                          $query = sprintf("SELECT SQL_CALC_FOUND_ROWS * FROM `client` LIMIT %d, %d", 
                                     $start, pg_pagesize);
                        
                          $this->result = $this->db->query($query);
                          
                          // Fetch the calculated total rows of the dataset without the LIMIT
                          $total = $this->db->query("SELECT FOUND_ROWS() AS total");
                          $row = $total->fetch_object();
                          $this->total = $row->total;
                        }
                        
                        // This is the workhorse of the class. It cycles through the dataset 
                        // and outputs the HTML rows for our data table
                        function showResults()
                        {
                          print "<table>" . _L;
                          print "\t<tr>" . _L;
                          print "\t\t<th>First Name</th><th>Surname</th><th>Email</th>" . _L;
                          print "\t</tr>" . _L;
                          while($row = $this->result->fetch_object()) {
                            print "\t\t<tr>" . _L;
                            printf("\t\t\t<td>%s</td><td>%s</td><td>%s</td>" . _L, 
                                    $row->firstname, $row->surname, $row->email);
                            print "\t\t</tr>" . _L;
                          }
                          print "</table>" . _L;
                          $this->result->close();
                        }
                        
                        // Here is where the pagination happens.
                        // The class is designed that this function can be called from 
                        // anywhere in the code (and multiple times)
                        // It is not dependent on showResults having already been called.
                        function showPagination()
                        {
                          // Calculate how many pages there are. We do that by taking the 
                          // integer division of the total rows
                          $pages = intval($this->total / pg_pagesize);
                          
                          // and adding 1 if there is a reminder.
                          $pages += ($this->total%$pages)?1:0;
                          
                          // Finally we have enough information to output our pagination HTML
                          print "<div class=\"page-navigation\">" . _L;
                          print '<a href="client.php?page=1" class="pagination-arrows">&lt;&lt;</a>';    
                          
                          // We don't want to show the goto previous record if we are on record 1
                          if ($this->pagenumber > 0) {
                            printf( "<a href=\"client.php?page=%d\" class=\"pagination-arrows\">&lt;</a>", 
                                  $this->pagenumber);
                          }
                          for($i=1;$i<=$pages;$i++) {
                            if ($i == $this->pagenumber+1) {
                              printf("<span>%d</span>" . _L, $i);
                            }
                            else {
                              printf("<a href=\"client.php?page=%d\">%d</a>" . _L, $i, $i);
                            }
                          }      
                          
                          // We don't want to show goto next record if we are on the last page.
                          if ($this->pagenumber < $pages-1) {
                            printf("<a href=\"client.php?page=%d\" class=\"pagination-arrows\">&gt;</a>", 
                                  $this->pagenumber + 2);
                          }
                          printf("<a href=\"client.php?page=%d\" class=\"pagination-arrows\">&gt;&gt;</a>", 
                                $pages);
                          print "</div>" . _L;
                        }
                      }
                      

Select all Open in new window

All that is left is to build the page that displays our results

client.php

// We are using sessions to store our pagenumber so initiate a session
                      session_start();
                      
                      // shortcut to the newline character
                      define('_L', PHP_EOL);
                      
                      // Include our workhorse files
                      require_once('database.php');
                      require_once('results.php');
                      
                      // Work out what page number to use
                      // First we look to see if a page number is specified in the URL request.
                      // If it is we sanitize it to remove everything except numbers.
                      // If no page was found then default to the empty string
                      $pageno = isset($_GET['page']) 
                        ? preg_replace('/[^0-9]/', '', $_GET['page']) 
                        : '';
                      
                      // This could have been done on one line but has been split for clarity
                      // If pageno is empty then attempt to get it from the session.
                      // If the session is not set yet then default to 1.
                      $pageno = empty($pageno) 
                        ? (
                          isset($_SESSION['pagenumber']) 
                          ? $_SESSION['pagenumber'] + 1
                          : 1
                          ) 
                        : $pageno;
                      
                      // Instantiate the results object to output to the page
                      $results = new Results($pageno);
                      ?>
                      <!doctype html>
                      <html>
                      <head>
                      <title>Client Results Page</title>
                      </script>
                      <style type="text/css">
                      body {
                        font-size: 100%;
                        font-family: Arial;
                      }
                      table {
                        border-collapse: collapse;
                        font-size: 0.8em;
                      }
                      th, td {
                        border: 1px solid #888;
                        padding: 2px 5px;
                      }
                      tr:nth-child(2n) {
                        background: #effeff;
                      }
                      .page-navigation {
                        font-size: 0.6em;
                      }
                      .page-navigation a.pagination-arrows {
                        padding: 0 4px;
                      }
                      </style>
                      </head>
                      <body>
                      <?php 
                        // Demonstrate that pagination can be output anywhere
                        // and any number of times
                        $results->showPagination();
                        
                        // Display our results
                        $results->showResults(); ;?>
                        
                        // Demonstrate that pagination can be output anywhere 
                        // and any number of times
                        $results->showPagination();
                      ?>
                      </body>
                      </html>
                      

Select all Open in new window

That's it - the basics of a pagination script that works with the MySQL functions SQL_CALC_FOUND_ROWS and FOUND_ROWS