Using PEAR Pager with PHP PDO
This post will be addressing 2 things.
- How to use PEAR’s Pager class with PHP PDO
- Addressing PDO bug #44639 http://bugs.php.net/bug.php?id=44639
Two queries will be used for this task. The first query, you will count the rows of the recordset. The second query is the same as the first but it will have the LIMIT – this is what will be used to display the data on each page.
Let’s say we have the following table.
CREATE TABLE IF NOT EXISTS`blog`
(
`blog_id` int(11) not null auto_increment,
`blog_text` varchar(4000) not null default ”,
PRIMARY KEY `pk_blog_id` (`blog_id`)
) ENGINE=INNODB;Now we’ll query the database and get the row count using PDO.
<?php
require_once(‘file-with-pdo-db-connection.php');
$sql = “SELECT * FROM `blog`”;
$stmt = $dbh->prepare($sql);
if ($stmt->execute())
{
// begin pager
require_once(‘Pager/Pager.php');
$params = array
(
‘totalItems' => $stmt->rowCount,
‘perPage' => 10,
‘delta' => 5,
‘mode' => ‘Sliding'
);
$pager =& Pager::factory($params);
$links = $pager->getLinks();
echo $links['all'];
// offset setup
list($from, $to) = $pager->getOffsetByPageId();
$from = $from – 1;
$perPage = $params['perPage'];
// 2nd query based on 1st with LIMIT – this will be displaying data per page
$stmt2 = $db->prepare($sql.' LIMIT :from, :perPage');
// address bug 44639 – forces the variables to have the property of integer instead of string so no quotes will surround it
$stmt2->bindValue(‘:from', $from, PDO::PARAM_INT);
$stmt2->bindValue(‘:perPage', $perPage, PDO::PARAM_INT);
$stmt2->execute();
while ($row_blog = $stmt2->fetch())
{
echo ‘'.$row_blog['blog_text'].'';
}
}
$dbh = null;
?>If you don’t use the bindValue, you will get an error in your query. Your second query would look something like this….
SELECT * FROM `blog` LIMIT ‘0', '10'…and the error would occur right after LIMIT. You cannot have quotes surrounding the numbers. It took a few hours of Googling but found the solution.
If you want to read more about PEAR Pager, visit http://pear.php.net/package/Pager/docs.
Here’s a tutorial I found that helped me out a lot – http://www.codediesel.com/php/simple-pagination-in-php/.
Thanks a whole bunch for this tutorial. It was a big help for a custom CMS I am writing. Once it is finished I will give you a thank you mention. Don’t understand why no one commented here. This is the only tutorial on making Pager work with PDO I could find. Thanks again.