PEAR::Pager Tutorials - Paginate database results

PEAR::Pager was written with the purpose of paginating arrays. While you can fetch all the records from a database, store them into an array and pass it to Pager, this method isn't particularly efficient. If you have large resultsets, it isn't efficient at all. There has to be a better way! In fact, there is...

Method #1

The first method is using Pager to create the links only, and let you fetch the relevant records on your own. Instead of passing the array of data to paginate to Pager, you just pass the number of records. In the following example, we'll fetch the records from a table containing some products. The PEAR::MDB2 DBAL is used here, but how you fetch the records isn't relevant.

<?php
require_once 'Pager/Pager.php';
require_once 'MDB2.php';

//skipped the db connection code...
//let's just suppose we have a valid db connection in $db.

//first, we use Pager to create the links
$num_products = $db->queryOne('SELECT COUNT(*) FROM products');
$pager_options = array(
    'mode'       => 'Sliding',
    'perPage'    => 10,
    'delta'      => 2,
    'totalItems' => $num_products,
);

$pager = Pager::factory($pager_options);

//then we fetch the relevant records for the current page
list($from, $to) = $pager->getOffsetByPageId();
//set the OFFSET and LIMIT clauses for the following query
$db->setLimit($pager_options['perPage'], $from - 1);
$query = 'SELECT prod_name, prod_description FROM products';
$products = $db->queryAll($query, null, MDB2_FETCHMODE_ASSOC);

//show the results
echo '<ul>';
foreach ($products as $product) {
    echo '<li>'.$product['prod_name'].': '.$product['prod_description'].'</li>';
}
echo '</ul>';

//show the links
echo $pager->links;
?>

While this method is acceptable, it requires a lot of code.

Method #2: Pager_Wrapper to the rescue!

The second method is using the handy Pager_Wrappers you can find in the /examples/ dir of your PEAR installation (or via the CVS viewer). These functions do all the dirty work for you, so have a look.

<?php
//copy the Pager_Wrapper file where you can include it
require_once 'Pager_Wrapper.php';
require_once 'MDB2.php';

//skipped the db connection code...
//let's just suppose we have a valid db connection in $db.

$pager_options = array(
    'mode'       => 'Sliding',
    'perPage'    => 10,
    'delta'      => 2,
);
$query = 'SELECT prod_name, prod_description FROM products';
$paged_data = Pager_Wrapper_MDB2($db, $query, $pager_options);

//show the results
echo '<ul>';
foreach ($paged_data['data'] as $product) {
    echo '<li>'.$product['prod_name'].': '.$product['prod_description'].'</li>';
}
echo '</ul>';

//show the links
echo $paged_data['links'];
?>

These methods are way more efficient than fetching all the records from the database into an array, and paginating them afterwards.
Only the relevant records are fetched, thus considerably reducing the load on the server (and on the network, if you have the db on another machine).
The Pager_Wrapper file bundled with the PEAR::Pager package contains ready-to-use functions working with the PEAR DBALs: DB, MDB, MDB2, DB_DataObject, and with the PHP Eclipse library. You can easily write your own following the examples.

I hope this tutorial was useful. Keep tuned, more to come.

« Go back to PEAR::Pager tutorials index.


PEAR Pager Tutorials - Lorenzo Alberton




3 responses to "PEAR::Pager Tutorials - Paginate database results"

Would you please show me how the “Pager/Pager.php” or MDB2.php page looks like… I have completely failed to get the first explanation right. Thank you.

Pager.php and MDB2.php are contained in the respective PEAR classes. You have to install them:
pear install Pager
pear install MDB2-beta
pear install MDB2_Driver_Mysql-beta

Your Explanation was very helpfull in getting pagination for mysite using pager and my sql.
thanks a lot!

Lorenzo Alberton

Lorenzo Alberton Lorenzo PHP5 ZCE - Zend Certified Engineer has been working with large enterprise UK companies for the past years and is now Chief Tech Architect at DataSift. He's an international conference speaker and a long-time contributor to many open source projects. Lorenzo Alberton's profile on LinkedIN View Lorenzo Alberton's Twitter stream

Lorenzo Alberton - Sun Certified MySQL 5 Developer

Tags

AJAX, Apache, Book Review, Charset, Cheat Sheet, Data structures, Database, Firebird SQL, Hadoop, Imagick, INFORMATION_SCHEMA, JavaScript, Kafka, Linux, Message Queues, mod_rewrite, Monitoring, MySQL, NoSQL, Oracle, PDO, PEAR, Performance, PHP, PostgreSQL, Profiling, Scalability, Security, SPL, SQL Server, SQLite, Testing, Tutorial, TYPO3, Windows, Zend Framework

Buy me a book - The Art Of Application Performance Testing