Previous or Next? Paginating Records with PHP – Part 3

In part three of our series about paginating records with PHP, we will learn how to make our application work with a relational database system such as MySQL. We will develop a tight, compact PHP class, capable of performing the most common paging tasks efficiently.

Introduction

Welcome to part three of the series “Previous or Next? Paginating Records with PHP.” I hope that you’ve had a good time reading the previous articles, where we strongly focused our attention on implementing several approaches to paginate data in websites, using PHP’s excellent capabilities. So far, we’ve developed a couple of methods for displaying chunks of data, appending the proper paging links, and providing an easy way to navigate across pages.

In the first case, we explored a procedural approach for dealing with the topic, defining a function to paginate data coming from a simple text file. This approach is suitable for small applications. Analogously, in the second case, we utilized a plain text file as a data source, but this time we built up a paging PHP class to generate the visual data output and the paging links. However, our newly developed “Pager” class presents some issues that need to be properly addressed.

Since the Web is now driven almost entirely by databases, we should be able to work with a relational database system such as MySQL to paginate result sets, and have the possibility of applying an effective visual format to retrieved records. To meet real world needs, it’s highly imperative to develop a tight, compact PHP class, capable of performing the most common paging tasks efficiently, with minor hassles at most.  So, let’s jump straight into the class code and create this new toy for our developer’s toolbox.

{mospagebreak title=Object-oriented paging: anatomy of the “Pager” class}

In order to set up the blueprints for the “Pager” class, let’s start by defining the methods that compose it. As we saw in the previous article, the class will display two methods to work: the constructor itself and the “displayRecords()” method, which will take care of creating both the visual output for database records, and the corresponding paging links. Here’s the first look at the class structure:

class Pager {

// properties declaration

function Pager(){

// parameters initialization

}

function displayRecords(){

// code to display records and paging links

}

}

As you can see, the skeleton of the class is very simple, and extremely understandable. So, let’s examine the constructor to get a detailed understanding of its logic. At an initial stage, this method will accept only three incoming parameters: a reference to a MySQL’s connection identifier, to provide database connectivity inside the class; an SQL query to get a result set (specifically a SELECT statement); and finally, the number of records per page to be displayed at a time.

Is that really all there is to it? Just three parameters and we’re in business with the class constructor? Well, we’ll add a couple of arguments later, but right now, the class structure might be redefined, including parameter initialization, like this:

class Pager {

var $conId; // connection identifier

var $query; // query performed

var $numRecs; // number of records per page

function Pager(&$conId,$query,$numRecs=10){

// validate connection Id

(mysql_query(‘SELECT 1′,&$conId))?$this->conId=$conId:die(‘Invalid connection identifier.’);

// validate query

(preg_match(“/^SELECT/”,$query))?$this->query=$query:die(‘Invalid query ‘.$query);

// validate number of records per page

(is_int($numRecs)&&$numRecs>0)?$this->numRecs=$numRecs:die(‘Invalid number of records ‘.$numRecs);

$this->output=”;

}

function displayRecords(){

// code to display records and paging links

}

}

Now things are getting a bit more complex — but not too complex, really. Let’s take a look at what the constructor does. Since it accepts the three mentioned parameters, before assigning them as class properties, they should be properly validated.

In order to do that, first the methods check whether the connection identifier $conId corresponds to a valid MySQL connection, performing a simple “SELECT 1″ statement against the selected database. If the query is successful, we have a valid MySQL connection identifier. Indeed, this little trick is useful to validate this kind of connection parameter. 

Please notice that we’re using a reference to the connection identifier, not a copy; this is the reason for the usage of the (&) ampersand operator, preceding the parameter. 

The next task that the constructor performs is to validate the incoming SQL query, by checking whether it starts up with the string “SELECT”. Since we don’t need another statement, any query that doesn’t fit this format is considered invalid, killing the script with a call to “die()”.

The final parameter to be validated is the number of records per page, $numRecs, which must be a positive integer. In this case, I’ve opted to specify a default value of 10 records to be shown at a time. As usual, if it happens that this argument is not valid, the class is simply killed. 

By this point, the method has nicely validated the three parameters, assigning them as class properties. However, remember I said that we would add two more incoming parameters? Right, we need to provide the class a flexible mechanism to control the look and feel of database records, as well as of the paging links. Doing so, we’ll make it more portable and extensible. How can it be done? We will simply implement a template file for records and a style ID for links. If this sounds confusing to you, just keep reading to learn more.

{mospagebreak title=Controlling the look of database records: defining a template file}

We often want to control the appearance of the output generated by an SQL query. In our case, this is not only necessary, but mandatory. While prior methods presented in previous articles have addressed this topic very basically, it’s time to define a way to do visual presentation.

There are numerous ways to achieve this, all of them perfectly good and valid. Regarding our class, I’ve decided to define a generic template file, which allows us to specify the look of the records retrieved after performing the query. The template file will include a header section, a content section that contains the placeholders to be replaced by real values, and finally, a footer section. Here’s how a sample template file would look:

<table><tr><td>First Name</td><td>Last Name</td></tr>

<tr><td>{data0}</td><td>{data1}</td></tr>

</table>

As you can see, the file contains only three lines. The first line contains a <table> tag; its first row is the header section. It enables us to specify a header element for the records. Notice that I’ve defined two table cells including “First Name” and “Last Name” as possible values to be retrieved from the database.

The middle line contains the placeholders to be filled with real values in the form {data0}, {data1}…{dataN}. So, if we’re pulling out values from a table with information about famous writers (not my case, by the way), the final formatted output to be shown in the browser would look similar to this:

<table><tr><td>First Name</td><td>Last Name</td></tr>

<tr><td>Stephen</td><td>King</td></tr>

<tr><td>Dean</td><td>Koontz</td></tr>

<tr><td>Frank</td><td>DeFellita</td></tr>

</table>

Are you getting the idea? Fine, let’s show another sample template file, this time using unordered lists to format records:

<ul>

<li>{data0}&nbsp;{data1}&nbsp;{data2}</li>

</ul>

In this case, we used regular HTML lists to control the record’s visual presentation. Now I think that you understand the concept behind the template file system. Just feed the class with different template files, and voila! We’ve easily and quickly changed the appearance for any database generated output. The only requirement to keep in mind is that our template files must be three lines long. The first line is for headers, the second is for placeholders, and the last is for the footer. Isn’t it simple?

Having explained the mechanism for record template files, it’s time to look at the appearance of paging links. The technique for handling this is a lot easier. Since we don’t need header or footer sections for displaying links, we simply provide them with an ID attribute, in order to tie them to a CSS style.

As stated before, the look and feel might be achieved by hard-coding templates outside the class and then passing them as parameters, possibly stored in PHP constants. While this might be easier to get done eventually, using template files gives us much more flexibility.

Our next step is to add the reviewed parameters to the constructor and include them as class properties. This way, our class is rewritten as follows:

class Pager {

var $conId; // connection identifier

var $query; // query performed

var $numRecs; // number of records per page

var $output; // output for records and paging links

var $template; // records template file

var $linksId; // Id attribute for styling paging links

function Pager(&$conId,$query,$linksId,$numRecs=10,$template=
‘default_record_template.htm’){

// validate connection Id

(mysql_query(‘SELECT 1′,&$conId))?$this->conId=$conId:die(‘Invalid connection identifier.’);

// validate query

(preg_match(“/^SELECT/”,$query))?$this->query=$query:die(‘Invalid query ‘.$query);

// validate linksId

(!is_numeric($linksId))?$this->linksId=$linksId:die(‘Invalid ID for paging links ‘.$linksId);

// validate template file

(file_exists($template))?$this->template=$template:die(‘Invalid template file ‘.$template);

// validate number of records per page

(is_int($numRecs)&&$numRecs>0)?$this->numRecs=$numRecs:die(‘Invalid number of records ‘.$numRecs);

$this->output=”;

}

function displayRecords(){

// code to display records and paging links

}

}

Certainly, the constructor remains nearly the same. We’ve only added the extra parameters, specifying a default records template file called “default_record_template.htm”, and validating them accordingly. For the supplied template file, the method checks whether the file exists. If it does, then it is assigned as a property. Otherwise, the script is stopped. Similarly, we’re more permissive about ID values for paging links, allowing only non-numeric values.

Finally, the constructor has performed the proper validation on each incoming parameter and assigned properties. What comes next? Our “displayRecords()” method is waiting for a decent explanation. That is what we shall focus on now.

{mospagebreak title=Going deeper: a detailed coverage at the “displayRecords()” method}

Undoubtedly, the “displayRecords()” method is the real workhorse of the class, since it performs the hard work of building the SQL query, obtaining a formatted result set, and creating the paging links. While this might sound complex, the truth is that the process is quite simple.

Based on the previous “Pager” class, defined in the second part of this series, the method uses the same logic, accepting only one parameter: $page, which simply behaves as a page pointer for displaying records in a paginated manner.

Taking into account that the constructor has already set up the $query property, it’s relatively easy to build dynamically a new query, adding a regular LIMIT clause to specify the offset and the number of records extracted from the database. So, let’s put theory to the side for a moment and show the initial definition for our “displayRecords()” method:

function displayRecords($page){

// calculate total of records

if(!$totalRecs=mysql_num_rows(mysql_query($this->query))){

die(‘Cannot retrieve records from database’);

}

// calculate number of pages

$numPages=ceil($totalRecs/$this->numRecs);

// validate page pointer $page

if(!preg_match(“/^d{1,2}$/”,$page)||$page<1||$page>$numPages){

$page=1;

}

// get result set

$result=mysql_query($this->query.’ LIMIT ‘.($page-1)*$this->numRecs.’,’.$this->numRecs);

}

As the above code clearly shows, the method is fed with the “$page” page pointer. Then, it calculates the total number of records extracted, performing the query without any LIMIT clause, and later using the “mysql_num_rows()” PHP function. The reason for doing this should become clear, because we need to calculate the number of pages needed to span all of the records. If the method cannot obtain this value, the class is simply stopped.

The next step logically involves calculating the number of pages to span the whole result set. It means simply dividing the total number of records by the number or records per page. Once this is done, the result is rounded up to work with an integer value.

Finally, having obtained these values, the method is capable of performing a strong validation process on the $page pointer, allowing only an integer positive value, which is always less than the number of pages previously calculated. If the page pointer is not within the valid range, for any given reason, then we place it at the first page ($page=1). Here’s the code to validate the page pointer:

if(!preg_match(“/^d{1,2}$/”,$page)||$page<1||$page>$numPages){

$page=1;

}

I briefly explained the reason for this in the second part of the series. Why should we bother validating this parameter? The answer is straightforward: we don’t want any malicious code injected into the querystring, troubling our result sets or even the paging links. As with any parameter coming from GET, POST methods, it must be properly checked and validated. While this sounds like common sense, there are lots of sites that are extremely vulnerable to querysting manipulation, to name just one security failure. So, always keep an eye on that, and remember that the Internet is largely out of your control!

Back to our method explanation, the only remaining step is to perform a SELECT query appending a usual LIMIT clause, to obtain the specified number of records to be displayed at a time. The line listed below does that:

$result=mysql_query($this->query.’ LIMIT ‘.($page-1)*$this->numRecs.’,’.$this->numRecs);

Now we’ve obtained the proper result set, ready to be displayed before the visitor’s eyes. However, the method is still immature in its current version, since we have not used the template file to replace the placeholders and give our records the desired look. What’s more, we need to include the paging links in the final output. The method must be completed. Let’s move on and show how to work with the template file and our paging links.

{mospagebreak title=The final round: completing the “displayRecords()” method}

Completing the definition of this method is matter of adding to it the capabilities to handle any template file for formatting purposes, and generating the paging links. Actually, we’re not so far from the finish line. Let’s add the necessary code to work with template files and the paging links. Here are the additional lines to complete the method:

// previous method code goes here

// read template file contents

$templateContent=file($this->template);

// add template header to final output

$this->output=reset($templateContent);

// move pointer to placeholders line

$templateRow=next($templateContent);

// replace placeholders

while($row=mysql_fetch_row($result)){

$tempOutput=$templateRow;

for($i=0;$i<mysql_num_fields($result);$i++){

$tempOutput=str_replace(‘{data’.$i.’}’,$row
[$i],$tempOutput);

}

// remove unpopulated placeholders

$this->output.=preg_replace(“/
{data.}/”,”,$tempOutput);

}

// add template footer to final output

$this->output.=end($templateContent);

// create paging links

$this->output.='<div id=”‘.$this->linksId.'”>';

// create previous link

if($page>1){

$this->output.='<a href=”‘.$_SERVER['PHP_SELF'].’?
page=’.($page-1).'”>&lt;&lt;Previous</a>&nbsp;';

}

// create intermediate links

for($i=1;$i<=$numPages;$i++){

($i!=$page)?$this->output.='<a href=”‘.$_SERVER
['PHP_SELF'].’?page=’.$i.'”>’.$i.'</a>&nbsp;':$this-
>output.=$i.’&nbsp;';

}

// create next link

if($page<$numPages){

$this->output.=’&nbsp;<a href=”‘.$_SERVER
['PHP_SELF'].’?page=’.($page+1).'”>Next&gt;&gt;</a> ‘;

}

$this->output.='</div>';

// return generated output

return $this->output;

The majority of the listed code is fully commented and quite easy to understand. Let’s begin by explaining how the method will format records using a template file. First, it reads the template file contents, storing them in the $templateContent array, like this:

$templateContent=file($this->template);

Please, remember that the template file has three lines, defined as a header section, a placeholders section and a footer section. In order to format the records, the method first adds the header section to the final output, resetting the array pointer, and retrieving the first line of the template file, in the following way:

// add template header to final output

$this->output=reset($templateContent);

Now, it’s time to move the pointers to the placeholders’ line and replace them with the actual values:

$templateRow=next($templateContent);

// replace placeholders with actual data

while($row=mysql_fetch_row($result)){

$tempOutput=$templateRow;

for($i=0;$i<mysql_num_fields($result);$i++){

$tempOutput=str_replace(‘{data’.$i.’}’,$row
[$i],$tempOutput);

}

// remove unpopulated placeholders

$this->output.=preg_replace(“/
{data.}/”,”,$tempOutput);

}

After replacing the placeholders with the corresponding values, any unpopulated variables are removed from the final output, substituting the placeholders with an empty string, this way enhancing the class’ flexibility. Finally, once records have been properly formatted, the footer line is appended to the output:

// add template footer to final output

$this->output.=end($templateContent);

That efficiently completes the process of formatting the records. As you can see, the approach is flexible enough to change the overall visual appearance, by just changing simple template files.

The rest of the code is nearly identical to the example listed in the second part, where the paging links are created by appending a <previous> link whenever possible, then generating the numbered links, and finally inserting a regular <next> link, when applicable. The only noticeable change hangs on providing an ID to tie a CSS style to the links, and offering a more polished appearance:

// create paging links

$this->output.='<div id=”‘.$this->linksId.'”>';

// create previous link

if($page>1){

$this->output.='<a href=”‘.$_SERVER['PHP_SELF'].’?
page=’.($page-1).'”>&lt;&lt;Previous</a>&nbsp;';

}

// create intermediate links

for($i=1;$i<=$numPages;$i++){

($i!=$page)?$this->output.='<a href=”‘.$_SERVER
['PHP_SELF'].’?page=’.$i.'”>’.$i.'</a>&nbsp;':$this-
>output.=$i.’&nbsp;';

}

// create next link

if($page<$numPages){

$this->output.=’&nbsp;<a href=”‘.$_SERVER
['PHP_SELF'].’?page=’.($page+1).'”>Next&gt;&gt;</a> ‘;

}

$this->output.='</div>';

// return generated output

return $this->output;

Having completed the definition for our “displayRecords()” class method, the class is already set up to be implemented on any existing or future Web application. Hopefully we’ve demonstrated that coding a PHP paging class is a not only an instructive experience, but also fun!

Wrapping up

That’s about it for the moment. In the final part of this series, we’ll run through several examples to show how the class can be implemented in real-world applications, providing a useful way to introduce paginated result sets in websites. In the meantime, feel free to play with the class code, adding your own improvements to it. 

[gp-comments width="770" linklove="off" ]

chat