Ok we are not going to waffle on about pagination but we will try and throw up a solution for you. First lets look at the scenario.

Scenario.

We have a database with hundreds of links and wish to display them , we cannot display all of the items as there is to many to view in one go. We would like to give the user the ability to view all records in the database , say 25 at a time.

The database in our example has the following structure

CREATE TABLE `nuke_links_links` ( `pn_lid` int(11) NOT NULL auto_increment, `pn_cat_id` int(11) NOT NULL default '0', `pn_title` varchar(100) NOT NULL default '', `pn_url` varchar(254) NOT NULL default '', `pn_description` text NOT NULL, PRIMARY KEY (`pn_lid`) ) TYPE=MyISAM

This is the modified web links part of PHP-nuke which consists of a unique ID for each entry called pn_lid, pn_cat_id is the category id, pn_title is the title of the link, pn_url is the url and pn_description is a description of the link. We use this for affiliate links.

What we wish to do is display the title, link and description for every link in a category. The category is PHP and its unique number is 98

Solution

We are going to use a couple of the packages from the pear website database section . These are the db package and the DB_pager package. Download them and install them in a folder , we have a simple example detailing this or check out the Pear website.

Code:

<?php
//this is the path to the dbpager file you downloaded, change this
ini_set("include_path", '/path_to_pear_file_in_here/dbpager/' . PATH_SEPARATOR . ini_get("include_path"));
require 'Pager.php';
$offset = intval($_REQUEST['offset']);
//25 links per page
$no_of_links = 25;
//connect to MySQl database using the db package
//the format for this is as follows
//mysql = database being used
//username = your username for access
//password = your password for access
//localhost = database server
//database = the name of the database you are using
$dsn = 'mysql://username:password@localhost/database';
//conect to database
$dbh = DB::connect($dsn);
//SQL query ,list all links
$sql_query = $dbh->query('SELECT * FROM nuke_links_links WHERE pn_cat_id = 98');
//execute the query
$pager = new DB_Pager($sql_query, $offset, $no_of_links);
$data = $pager->build();
//display links
//$rows[3] is the url, $rows[2] is the title
//$rows[4] is the description
while($rows = $pager->fetchRow())
{
print"<a href='$rows[3]'>$rows[2]</a> : $rows[4]<br>";
}
print "<br><br>";
//display previous link
printf('<a href="%s?offset=%d">Previous</a> |', $_SERVER['PHP_SELF'],$data['prev']);
//display page links
foreach($data['pages'] as $page=> $start)
{
printf('<a href="%s?offset=%d">%d</a> |',$_SERVER['PHP_SELF'],$start,$page);
}
//display next page link
printf('<a href=%s?offset=%d">Next</a>',$_SERVER['PHP_SELF'],$data['next']);
//display the records part
printf(" Displaying records %d- %d of %d)",$data['from'],$data['to'],$data['numrows']);
?>

Working example

php pagination example

Need Help

Go to our forums and look for the php pagination entry in the PHP section and post comments problems. We had a few getting it up and running.