Previous or Next? Paginating Records with PHP, part 4

In the fourth and final article in our series about paginating records with PHP, we will look at the source code for the “pager” PHP class we developed in part three, and show several examples to demonstrate possible uses in production environments.

Introduction

Welcome to the final part of the series “Previous or Next? Paginating Records with PHP.” Over the previous articles, we addressed a common issue involved with Web applications: record paging. From the very beginning, we provided a friendly introduction to solving paging problems, branching out to different approaches. Starting with a basic procedural solution, then moving on to an object-oriented implementation, hopefully we offered in-depth coverage for the subject.

In the third part of the series, our best efforts were put into developing a “Pager” PHP class. Aimed specifically at paginating MySQL records, it offers a great degree of flexibility for controlling the look of generated output.

Finally, in this last article, we’ll take a quick look at the class’ source code, and head in a practical direction, developing several step-by-step examples to demonstrate possible uses in production environments.

With the preliminaries out of the way, let’s put our hands on the code and start paging records!

{mospagebreak title=A quick look at the class source code}

Before we try the class and show a real example, it’s necessary to list its full code. This way, you’ll have an overall conception of how it works. Here’s the complete source code for the “Pager” class:

<?php
/*
* PHP Pager class
* @access public
*/

class Pager {
  /**
  * MySQL connection identifier
  * @access private
  * data type string
  */
  var $conId;

 

  /**
  * SQL query
  * @access private
  * data type string
  */
  var $query;

 

  /**
  * number of records per page
  * @access private
  * data type integer
  */
  var $numRecs;

 

  /**
  * generated output for records and paging links
  * @access private
  * data type string
  */
  var $output;

 

  /**
  * records template file
  * @access private
  * data type string
  */
  var $template;

 

  /** ID attribute for styling paging links
  * @access private
  * data type string
  */
  var $linksId;

 

  /**
  * Pager constructor
  * @access public
  * @param string conId
  * @param string query
  * @param string linksId
  * @param integer numRecs
  * @param string template
  */
  function Pager (&$conId,$query,$linksId,$numRecs=10,
                 $template=’default_record_template.htm’){

    // validate connection idenfifier
    (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 paging links ID
    (!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);

    // initialize output
    $this->output=”;

  }

 

  /**
  * method displayRecords
  * @access public 
  * @return string output
  * @param integer page
  * @description display paginated records/paging links
  */
  function displayRecords($page){

    // calculate total number 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);
    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);

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

    // append template file footer to final output
    $this->output=reset($templateContent);

    // move pointer to placeholder line
    $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);

    }

    // append template file footer to final output
    $this->output.=end($templateContent);

    // create page 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 numerated 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 final output
    return $this->output;

  }

}

?>

 

That was a long listing, don’t you think? However, it’s really worthwhile. Now we’re ready to develop some examples and see the class in action. Since the class will work with the MySQL server, let’s begin by setting up a sample database and creating a basic table with some records. Want to know more? Just click the link and keep reading.

{mospagebreak title=Creating the database table}

In the first place, we need to define a database and create a table. So, we’re going to create a database called “songs,” which will contain only one table. The SQL code for creating our “songs” database is as simple as the following line:

CREATE DATABASE songs;

Whew, that was a lot of work! I feel really exhausted. But let’s go on and create the “songs” table, using the SQL code listed below:

CREATE TABLE songs
(
  songid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30) NOT NULL,
  author VARCHAR(30) NOT NULL
);

As you can see, I’ve defined a basic table with three fields: “songid,” “name” and “author,” respectively. The first field, “songid,” is the primary key, which I’ve specified directly. Also, I’ve decided this will be an integer (notice that its data type is INT), and that this field should be unsigned. As usual, we’ve taken advantage of the AUTO_INCREMENT capability, so that MySQL can handle this field for us.

The rest of the fields, “name” and “author,” are defined to hold string type data, using VARCHAR, to allocate the amount of storage required (plus one byte). The alternative would be using CHAR, which will make room for 30 characters for storage, and generally is faster.

Now, it’s time to populate the database with some records. After all, it’s pointless to have an empty table. Using a multi-row insertion method, let’s add the corresponding records:

INSERT INTO songs VALUES
(NULL,”Chariots of fire”,”Vangelis”),
(NULL,”Friends of Mr. Cairo”,”Vangelis”),
(NULL,”Lucifer”,”The Alan Parson Project”),
(NULL,”The eye in the sky”,”Alan Parson Project”),
(NULL,”Caribean Blue”,”Enya”),
(NULL,”Only Time”,”Enya”),
(NULL,”Music”,”John Miles”),
(NULL,”The turn of a friendly card”,”The Alan Parson Project”),
(NULL,”Children”,”Robert Miles”),
(NULL,”One and One”,”Robert Miles”),
(NULL,”Sadness”,”Enigma”),
(NULL,”Mea Culpa”,”Enigma”),
(NULL,”Cherry Blossom Girl”,”Air”),
(NULL,”Hot stuff”,”Donna Summer”),
(NULL,”Bad Girls”,”Donna Summer”),
(NULL,”I will survive”,”Gloria Gaynor”),
(NULL,”Rimes and reasons”,”John Denver”),
(NULL,”Touch and go”,”Emerson,Lake and Powell”),
(NULL,”In Jeopardy”,”Roger Hogdson”),
(NULL,”Ameno”,”Era”);

Utilizing the above SQL code, we’ve populated the “songs” table with a few records that include information about the song’s title and author. Perhaps this might sound like common sense, but before creating any database or tables, make sure you have the proper privileges to perform these operations.

Finally, we have a database table to work with, suitable for testing our pager class. In next section, we’ll see several examples to demonstrate the power and flexibility of the class. Let’s jump right into the samples.

{mospagebreak title=Paging MySQL records: putting the “Pager” class into action}

Before showing any examples, a disclaimer is in order. Since we need to connect to the MySQL server, I will use a hypothetical MySQL class to perform the server connection. So, if you’re seated on the procedural chair, don’t worry. I’ll take a look at that approximation too. Having said that, let’s show the first example, first defining a simple default template file, as listed below:

<table>
  <tr class=”header”>
    <td>Song Id</td><td>Song Name</td><td>Song Author</td>
  </tr>
  <tr>
    <td>{data0}</td><td>{data1}</td><td>{data2}</td>
  </tr>
</table>

That’s all that we need to format the result set. Definitely, you’ll agree that this template file is very simple. Now, let’s continue defining some CSS rules, in order to style the records and the paging links:

table {
  width: 50%;
  background: #ffc;
  font: normal 11px “Verdana”, Arial, Helvetica, sans-serif;
  color: #000;
  text-align: center;
  border: 1px solid #000;
}

tr.header {
  background: #fc0;
  font: bold 11px “Verdana”, Arial, Helvetica, sans-serif;
  color: #000;
}

td {
  width: 33%;
  padding: 2px;
}

#paginglinks {
  position: absolute;
  top: 180px;
  left: 320px;
  font: bold 11px “Verdana”, Arial, Helvetica, sans-serif;
  color: #00f;
}

Finally, let’s implement our “Pager” class, including the paging class file, a MySQL abstraction class, and the instantiation of the corresponding objects:

<?php
// include class files
require_once(‘pagerclass.php’);
require_once(‘mysqlclass.php’);

// instantiate a new MySQL object
$db=&new MySQL(‘dbhost’,’username’,’password’,’songs’);

// instantiate a new Pager object
$pager=&new Pager($db->getConnectionId(),’SELECT * FROM songs’,’paginglinks’,5);

// display paginated result set
echo $pager->displayRecords($_GET['page']);

?>

Notice from the above example, that I’ve specified the display of five records per page, feeding the class with that incoming value.

For those Web developers connecting to MySQL using a procedural method, the above code can be rewritten as follows:

<?php
// include the pager class
require_once(‘pagerclass.php’);

// connect to MySQL
$conId=@mysql_connect(‘dbhost’,’username’,’password’) or die (‘Error connecting to the server: ‘.mysql_error());

// select database
mysql_select_db(‘songs’) or die (‘Error selecting database’);

// instantiate a new pager object
$pager=&new Pager(&$conId,’SELECT * FROM songs’,’paginglinks’,5);

// display records and paging links
echo $pager->displayRecords($_GET['page']);

?>

Whatever method you use to connect to MySQL, the rendered output would look similar to this:

Not too bad, eh? Please notice how we’ve formatted database records and paging links, simply by using a three line template file. However, this is not the end. What if we change our mind and want to use another template file to build a different look and feel? Just go to the next page to find out more.

{mospagebreak title=Extending the use of the “Pager class”: more examples to come}

Say we want to use HTML lists to display the database records. Our new template file, called “record_template1.htm” would be defined in the following way:

<ul>
  <li>{data0} {data1} – {data2}</li>
</ul>

But, wait a minute! Right now you must think that I’m kidding you. Not really. That’s all we need to redefine the rendered output. Now, let’s declare a few simple CSS rules:

li {
  font: normal 12px Arial, Helvetica, sans-serif;
  color: #009;
}

#paginglinks {
  position: absolute;
  top: 170px;
  left: 170px;
  font: bold 11px “Verdana”, Arial, Helvetica, sans-serif;
  color: #00f;
}

Finally, let’s implement the same PHP code, this time passing the new template file to our pager class:

<?php

// include class files
require_once(‘pagerclass.php’);
require_once(‘mysqlclass.php’);

// instantiate a new MySQL object
$db=&new MySQL(‘dbhost’,’username’,’password’,’songs’);

// instantiate a new Pager object
$pager=&new Pager($db->getConnectionId(),’SELECT * FROM songs’,’paginglinks’,5,’record_template1.htm’);

// display paginated result set
echo $pager->displayRecords($_GET['page']);

?>

Or we can use the procedural approach, with the following lines:

<?php

// include the pager class
require_once(‘pagerclass.php’);

// connect to MySQL
$conId=@mysql_connect(‘dbhost’,’username’,’password’) or die (‘Error connecting to the server: ‘.mysql_error());

// select database
mysql_select_db(‘songs’) or die (‘Error selecting database’);

// instantiate a new pager object
$pager=&new Pager(&$conId,’SELECT * FROM songs’,’paginglinks’,5,’record_template1.htm’);

// display records and paging links
echo $pager->displayRecords($_GET['page']);

?>

Our new paginated result set, using unordered lists, will be displayed as follows:

Can you see what a difference changing the template file made? The power and flexibility of the pager class is really remarkable. Want to see more? Fine, let’s use the same default record template file defined in the first example, but this time change only the CSS rules. Here are the new CSS styles:

table {
  width: 50%;
  background: #eee;
  font: normal 11px “Verdana”, Arial, Helvetica, sans-serif;
  color: #000;
  text-align: center;
  border: 1px solid #000;

}

tr.header {
  background: #039;
  font: bold 11px “Verdana”, Arial, Helvetica, sans-serif;
  color: #fff;
}

td {
  width: 33%;
  padding: 3px;
}

#paginglinks {
  position: absolute;
  top: 180px;
  left: 320px;
  font: bold 11px “Verdana”, Arial, Helvetica, sans-serif;
  color: #00f;
}

As usual, let’s implement the PHP code to instantiate the pager class:

<?php

// include class files
require_once(‘pagerclass.php’);
require_once(‘mysqlclass.php’);

// instantiate a new MySQL object
$db=&new MySQL(‘dbhost’,’username’,’password’,’songs’);

// instantiate a new Pager object
$pager=&new Pager($db->getConnectionId(),’SELECT * FROM songs’,’paginglinks’,5);

// display paginated result set
echo $pager->displayRecords($_GET['page']);

?>

Alternatively, let’s take the procedural road to make the MySQL connection:

<?php

// include the pager class
require_once(‘pagerclass.php’);

// connect to MySQL
$conId=@mysql_connect(‘dbhost’,’username’,’password’) or die (‘Error connecting to the server: ‘.mysql_error());

// select database
mysql_select_db(‘songs’) or die (‘Error selecting database’);

// instantiate a new pager object
$pager=&new Pager(&$conId,’SELECT * FROM songs’,’paginglinks’,5);

// display records and paging links
echo $pager->displayRecords($_GET['page']);

?>

According to the new CSS rules, the revamped visual output would be the following:

Certainly, the class shows how easy it is to change styles for records and display them in a paginated manner. It’s just a matter of changing the template files, and we’re in business.

Having shown all of these examples, I believe that I provided you with a wealth of resources for building paginated Web pages. Feel free to use this samples and build your own template files and CSS styles. If you’ve been feeling creative lately, let your inspiration fly and find new styles to utilize. The possibilities are endless.

Conclusion

We have finished our long examination of paging techniques. From the first article onward, I’ve demonstrated different methods for paginating web pages in a progressive learning curve. I wound up the process by developing a solid and robust PHP pager class, capable of rendering paged MySQL result sets with facility and flexibility.

For those developers working in the area of recordset paging, this class can certainly be very useful and highly portable. Personally, I’ve been using the class in several projects with excellent results, so with minor or no modifications, it might be the next solution for your paging needs. Good luck and happy paging!

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

chat sex hikayeleri Ensest hikaye