Lorenzo Alberton

London, UK · Contact me ·

« Articles

PEAR::Pager Tutorials - Paginate database results

PHP, PEAR, Database, Tutorial · 08 September 2006 12:50 · 3 comments

Abstract: Paginate database results with PEAR::Pager, in an efficient way.

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

Back

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

Benjamin, 17 December 2009 22:45

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.

Lorenzo Alberton, 17 December 2009 22:54

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