Last Updated:

Pagination of a query result

The most annoying code that you have to write from scratch every time is the breakdown of a large database query result into pages that the user can navigate through. With our acquired knowledge of OOP, we can create a class that will do this work for us!

Unlike our previous rectangle-based example, the best way to design a class is to decide how the object should behave and then encode everything. In a programming language, we begin to define the interface of our class.

Let's say we have a joke database and we have a Jokes table (in which the AID column links that table to the Authors table).


Let's say we want to write a PHP script that will output all the jokes of one author, but only 5 per page, and so that the user can navigate through these pages with five jokes on each.

Here's what the page code will look like if we use OOP:

<?php
require('pagedresults.php');

$cnx = @mysql_connect('localhost','kyank','********');
mysql_select_db('jokes',$cnx);
$rs = new MySQLPagedResultSet("select * from jokes where aid=$aid",
5,$cnx);

?>

<html>
<head>
<title>Paged Results Demo</title>
</head>
<body>
<table border="1">
<?php while ($row = $rs->fetchArray()): ?>
<tr><td><?=$row['JokeText']?></td><td><?=$row['JokeDate']?></td></tr>
<?php endwhile; ?>
</table>
<p><?=$rs->getPageNav("aid=$aid")?></p>
</body>
</html>

Areas in bold indicate where the object (of the MySQLPagedResultSet class) is participating. Everything else is fairly standard and shouldn't cause you any questions if you've been working with PHP for at least a while. Let's break it down.

require('pagedresults.php');

As in the rectangle example, we put the class code in a separate file. This is done not only to simplify the work with it, but also to ensure that this class can be used on other pages by a simple connection, and not by a blunt copy/paste.

After connecting to the database, we create an object:

$rs = new MySQLPagedResultSet("select * from jokes where aid=$aid", 5,$cnx);

As you can see, the constructor for this class takes three parameters:

  • SQL query
  • Number of records we want to display per page
  • Pointer to the connection to the base

We could design the object to pass it the usual mySQL query result, but I decided to embed the query parsing into the object to illustrate how the class works more clearly.

Once you have retrieved an array from a database, then typically use a while loop to traverse the entire array and display its contents. Our paging class (an instance of which we named $rs) allows you to do the same thing using the fetchArray() method in the same way that we typically use the mysql_fetch_array() function:

<?php while ($row = $rs->fetchArray()): ?>

So each call to the $rs->fetchArray() will return an array containing one string from the query result (the array will be placed in a variable $row) until it reaches the end of the results on that page. At the end of the page, the $rs->fetchArray() will return false, so the loop ends.

All that's left is to display links to the pages:

<p><?=$rs->getPageNav("aid=$aid")?></p>

The getPageNav method is responsible for displaying the navigation, which for page 4 will look like this:


The method creates references to the same script that contain the special variable resultinpage in the query string. The constructor of the MySQLPagedResultSet class keeps track of this variable and uses it to determine which page from the selection result to show.

Because in most cases a SQL query contains more than one variable (in this case, only the $aid variable that defines which author's jokes to output), you can pass any additional query elements to the getPageNav method. In our case, we pass "aid=$aid" to ensure that the variable $aid is passed through all generated links.

Now that we can see how the object should work, we can move on to creating the MySQLPagedResultSet class itself.

Create the MySQLPagedResultSet class

My description of the class interface gave you the key to understanding how a class works. Now we'll go through all the code in the class and see how it works.

class MySQLPagedResultSet
{
var $results;
var $pageSize;
var $page;
var $row;

We started with a list of variables (more correctly, properties):

  • $results is used to store the result of a MySQL query
  • $pageSize contains the number of records that are displayed on a single page
  • $page stores the link of each page of the query result
  • $row is used by the fetchArray method to track the current row in the query result

Let's start with the constructor:

function MySQLPagedResultSet($query,$pageSize,$cnx)
{
global $resultpage;

As we decided earlier, three parameters will be passed to the designer: the SQL query, the number of records on the page, and the connection to the database.

The first thing we will do in this function is to access the global variable $resultpage. This is the variable that the getPageNav method inserts into the links to indicate which page to display.

The constructor must then process the query string:

$this->results = @mysql_query($query,$cnx);

The result is placed in the $this->results, a class variable defined earlier. Next, initialize the $this->pageSize property, which is simply assigned the value of the $pageSize parameter passed to the constructor.

$this->pageSize = $pageSize;

After that, you need to install the current page. First, let's check the variable $resultpage. If the variable is empty or has a negative value, we set it to 1, so that the first page is selected by default.

if ((int)$resultpage <= 0) $resultpage = 1;

(int) causes PHP to convert the value of a variable $resultpage to an integer. This is necessary if someone changes the query string and types a string value there. We should also check to see if the page is out of the box. To do this, use the getNumPage method:

if ($resultpage > $this->getNumPages())
$resultpage = $this->getNumPages();

As a result, with the correct page number on hand, we pass it to the setPageNum method:

$this->setPageNum($resultpage);
}

With the designer, we figured out, let's move on to the methods. The getNumPage method determines the number of pages in this sample. It is needed for service use by other methods of the class, in particular, in the constructor.

function getNumPages()
{
if (!$this->results) return FALSE;
return ceil(mysql_num_rows($this->results) /
(float)$this->pageSize);
}

As you can see, determining the number of pages is a fairly simple operation. We simply divide the number of rows in the query result (mysql_num_rows($this->result)) by the page size ($this->pageSize), and then round the result using the ceil function. To be sure that the result of the division can be rounded, we convert the value of the pageSize property to a floating-point number.

You can use the setPageNum method to set the query result page. In fact, this method is quite useless, so in practice it can be considered as an extension of the designer.

function setPageNum($pageNum)
{
if ($pageNum > $this->getNumPages() or
$pageNum <= 0) return FALSE;

$this->page = $pageNum;
$this->row = 0;
mysql_data_seek($this->results,($pageNum-1) * $this->pageSize);
}

First, we check the $pageNum parameter to make sure it belongs to the range of possible pages. Then save the new page number to the $this >page, reset the $this->row to zero, and then use the PHP mysql_data_seek function to go to the first line of the selected page.

The getNumPage method returns the current page number.

function getPageNum()
{
return $this->page;
}

Why is it needed, you ask? After all, if $rs is an instance of the MySQLResultSet class, then you can get a page number like this $rs->page, right? Let's look at the setPageNum method. As you can see, there's a lot involved in the process of setting the page number. If someone who uses the class decides to directly change the $rs >page, they will not succeed and the object will not behave as expected.

Current views of OOP say that no property should be directly accessible outside of a class. Instead, methods should be able to "read" and "set" the values of each property that may need to be accessed externally. In most object-oriented languages, this can be imposed by declaring the property as private, and therefore completely inaccessible from the outside.

In PHP, this is not the case, but the principle of using properties does not change from this. Instead of accessing properties directly, you should use methods.

In practice, you can use the getPageNum method to display "Page X of Y". The code for this will be as follows:

<p>Страница <?=$rs->getPageNum()?> from <?=$rs->getNumPages()?>.</p>

Phew! So much talk about such a simple method! Fortunately, the following require much less explanation.

function isLastPage()
{
return ($this->page >= $this->getNumPages());
}

function isFirstPage()
{
return ($this->page <= 1);
}

These methods allow you to determine whether the current page is the last or the first. Return true or false methods. And the code has to explain everything itself.

It remains to disassemble the two workhorses of the class. First, let's deal with the fetchArray method, which replaces the mysql_fetch_array function for our case.

function fetchArray()
{
if (!$this->results) return FALSE;
if ($this->row >= $this->pageSize) return FALSE;
$this->row++;
return mysql_fetch_array($this->results);
}

The method returns false if the query result stored in the $this >results is false (that is, the request itself was false), and if the current sample series number is greater than or equal to the current page number (indicating that the end of the page has been reached). If the checks pass, the value of the current series is incremented by one and the mysql_fetch_array function is called to return the next sample series.

And finally, the getPageNav method.

function getPageNav($queryvars = '')
{

As you can see, the $queryvars parameter defaults to an empty string in order to make this parameter optional, so you can call the getPageNav method with no parameters if you don't want to pass any variables in the generated links query string.

if (!$this->isFirstPage())
{
$nav .= "<a href=\"?resultpage=".
($this->getPageNum()-1).'&'.$queryvars.'">Prev</a> ';
}

First of all, if the current page is not the first page (we do this check using the isFirstPage method), we need to display the "Prev" link. The query string contains the magic variable resultpage, which tells the ySQLPagedResultSet object which page to display. After this variable, write to the query string all the remaining variables contained in the .$queryvars parameter.

At the output of this method, we have a variable $nav, which we will print on the page not in the class, but directly in the template. This will provide some flexibility. Here's a list of pages:

if ($this->getNumPages() > 1)
for ($i=1; $i<=$this->getNumPages(); $i++)
{
if ($i==$this->page)
$nav .= "$i ";
else
$nav .= "<a href=\"?resultpage=&".
$queryvars."\"></a> ";
}

Check that there are more than one page. Displaying a unit on a single page is completely pointless. Then we use a for loop to go through the page numbers, making a reference to the corresponding page numbers, except for the current page (where $i=$this->page), the number of which we derive without reference.

Finally, display the "Next" link if the current page is not the last page.


if (!$this->isLastPage())
{
$nav .= "<a href=\"?resultpage=".
($this->getPageNum()+1).'&'.$queryvars.'">Next</a> ';
}

return $nav;
}
}

Writing a class actually takes longer than regular mixed code, but if you continue to use this class in your work, the benefits will be obvious. In addition, one time does not determine everything.