Lorenzo Alberton
« Articles
PEAR::Pager Tutorials - Paginate database results
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.
Related articles
- PEAR::Pager Tutorials
- PEAR::Pager Tutorials - Create pretty links with Pager and mod_rewrite
- PEAR::Pager Tutorials - Navigation with Pager and AJAX (or simple Javascript)
- PEAR::Pager Tutorials - Article Pagination and Navigation
- PEAR::Pager Tutorials - Use Pager with Smarty. Use Pager_Wrapper with AJAX
Latest articles
- On batching vs. latency, and jobqueue models
- Updated Kafka PHP client library
- Musings on some technical papers I read this weekend: Google Dremel, NoSQL comparison, Gossip Protocols
- Historical Twitter access - A journey into optimising Hadoop jobs
- Kafka proposed as Apache incubator project
- NoSQL Databases: What, When and Why (PHPUK2011)
- PHPNW10 slides and new job!
Filter articles by topic
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 FrameworkFollow @lorenzoalberton
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
steve, 15 January 2010 10:24
Your Explanation was very helpfull in getting pagination for mysite using pager and my sql.
thanks a lot!