Tony Marston's Blog About software development, PHP and OOP

Pagination - what it is and how to do it

Posted on 5th January 2004 by Tony Marston
Intended Audience
Prerequisites
What is pagination?
How to do it - the easy way
How to do it - the hard way
Sample software
Comments

Intended Audience

This tutorial is intended for developers who wish to give their users the ability to step through a large number of database rows in manageable chunks instead of the whole lot in one go.

Prerequisites

It is assumed that you already have basic knowledge of PHP and MySQL. The techniques described in this document are written with MySQL in mind but can be applied to any database that allows the LIMIT and OFFSET clauses on the SELECT statement.

What is pagination?

If you have a form which allows the user to browse through the rows in a database table, what do you do if that table has hundreds or even thousands of rows? It would not be a good idea to show all those rows in a single form, instead you should split the database output into more manageable chunks or 'pages'. There are two things you must do:

  1. Decide on the maximum number of database rows that can be included in each page. You may hard code this value, or (my preferred method) you can define it in a variable so that the value may be changed at runtime.
  2. You then need to inform the user that other 'pages' are available and provide a mechanism whereby the user is able to select a different 'page' of details. I currently use a set of hyperlinks in a separate pagination area which looks like this:

    Pagination Area

    dialog-types-pagination (1K)

    This area tells the user which page is currently being viewed, the total number of pages available, and contains links to go either forwards or backwards through the available pages. The hyperlinks are displayed as simple text if there are no previous or next pages. Note that these hyperlinks do not put the word FIRST, PREV, NEXT or LAST as a parameter in the URL string - they all specify an absolute page number in the format pageno=n.

How to do it - the easy way

To include all the relevant functionality in a single script you should follow the steps outlined below.

1. Obtain the required page number

This code will obtain the required page number from the $_GET array. Note that if it is not present it will default to 1.

if (isset($_GET['pageno'])) {
   $pageno = $_GET['pageno'];
} else {
   $pageno = 1;
} // if

2. Identify how many database rows are available

This code will count how many rows will satisfy the current query.

$query = "SELECT count(*) FROM table WHERE ...";
$result = mysql_query($query, $db) or trigger_error("SQL", E_USER_ERROR);
$query_data = mysql_fetch_row($result);
$numrows = $query_data[0];

3. Calculate number of $lastpage

This code uses the values in $rows_per_page and $numrows in order to identify the number of the last page.

$rows_per_page = 15;
$lastpage      = ceil($numrows/$rows_per_page);

4. Ensure that $pageno is within range

This code checks that the value of $pageno is an integer between 1 and $lastpage.

$pageno = (int)$pageno;
if ($pageno > $lastpage) {
   $pageno = $lastpage;
} // if
if ($pageno < 1) {
   $pageno = 1;
} // if

5. Construct LIMIT clause

This code will construct the LIMIT clause for the sql SELECT statement.

$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;

6. Issue the database query

Now we can issue the database qery and process the result.

$query = "SELECT * FROM table $limit";
$result = mysql_query($query, $db) or trigger_error("SQL", E_USER_ERROR);
... process contents of $result ...

7. Construct pagination hyperlinks

Finally we must construct the hyperlinks which will allow the user to select other pages. We will start with the links for any previous pages.

if ($pageno == 1) {
   echo " FIRST PREV ";
} else {
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1'>FIRST</a> ";
   $prevpage = $pageno-1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'>PREV</a> ";
} // if

Next we inform the user of his current position in the sequence of available pages.

echo " ( Page $pageno of $lastpage ) ";

This code will provide the links for any following pages.

if ($pageno == $lastpage) {
   echo " NEXT LAST ";
} else {
   $nextpage = $pageno+1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'>NEXT</a> ";
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>LAST</a> ";
} // if

That's all there is to it. Easy peasy, lemon squeezy.

How to do it - the hard way

Rather than have all my presentation, business and data access code in a single script I prefer to use the 3 tier architecture as described in my article A Development Infrastructure for PHP. As an added complication all my HTML output is generated from XSL stylesheets which use XML data files produced by my PHP scripts. The steps are exactly the same, but the code is slightly different.

1. Obtain the required page number

If a page number is supplied in the $_GET array this code will pass it to the database object.

if (isset($_GET['page'])) {
  $dbobject->setPageNo($_GET['page']);
} // if

The object uses this code to receive the passed value.

   function setPageNo ($pageno)
   // this allows a particular page number to be selected
   {
      $this->pageno = abs((int)$pageno);
   } // setPageNo

As I serialise my object in the $_SESSION array if a new value is not supplied then I use whatever value was available in the previous instance.

2. Identify how many database rows are available

This code is the same as before.

3. Calculate number of $lastpage

Here the result depends on the vaue held in $this->rows_per_page. This is set to a default value in the class constructor, but it may be set to any other value at runtime.

if ($this->rows_per_page > 0) {
   $this->lastpage = ceil($this->numrows/$this->rows_per_page);
} else {
   $this->lastpage = 1;
} // if

4. Ensure that $pageno is within range

This code is the same as before.

5. Construct LIMIT clause

This code will construct the LIMIT clause for the sql SELECT statement. Note that if the value of $rows_per_page has been set to zero then this effectively turns pagination off.

if ($rows_per_page > 0) {
   $limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;
} else {
   $limit = '';
} // if

6. Issue the database query

This code is the same as before.

7. Construct pagination hyperlinks

This is a bit more complicated as it involves passing a series of parameters to an XSL stylesheet during the transformation process. Stage 1 is to extract certain values from the database object and insert them into any array.

$xsl_params['curpage']  = $dbobject->getPageNo();
$xsl_params['lastpage'] = $dbobject->getLastPage();

Stage 2 is to perform the XSL transformation using an XML file which was constructed by the PHP script and a predefined XSL stylesheet. These processes are described in detail in the following documents:

Stage 3 is performed during the XSL transformation. Although pagination is required in many scripts the code is absolutely identical, so being an efficient programmer I have defined it just once in its own template file. As this code has already been described in Generating dynamic web pages using XSL and XML I shall not bother to duplicate it here.

This code can be included in any number of XSL stylesheets using the followng line of code:

<xsl:include href="std.pagination.xsl"/>

The predefined template can be executed when required using the following line of code:

<xsl:call-template name="pagination" />

That's all there is to it. Easy peasy, lemon squeezy.

Sample software

Sample software which shows pagination in action is available on my website, as described in article A Sample PHP Application. This software can be run online, or you can download all the source code and run it locally in your own PHP/MySQL environment.

counter