PHP – Pagination With PEAR and MySQL
Okay so the usual way of paginating mysql result sets within php involves code that looks something like this:
$total_pages = ceil($total_records / $products_per_page);
for($i = 1; $i < = $total_pages; $i++){
echo "" . $i . "";
}
if($_GET['page'] >= 1){
$current_page = $_GET['page'];
} else {
$current_page = 1;
}
$offset = ($current_page - 1) * $products_per_page;
$sql = "SELECT * FROM table_name ";
$sql .= "LIMIT " . $offset . "," . $products_per_page;
While this is fine for quick hack job scripts. We as programmers have access to a solid framework in the name of PEAR. Within PEAR a package called Pager can help us enormously.
Paginating with PEAR
PHP Code:
'Sliding',
'perPage' => 10,
'delta' => 2,
);
$query = 'SELECT prod_name, prod_description FROM products';
$paged_data = Pager_Wrapper_MDB2($db, $query, $pager_options);
echo '
- ';
- '.$product['prod_name'].': '.$product['prod_description'].'
foreach ($paged_data['data'] as $product) {
echo '
';
}
echo '
';
//show the links
echo $paged_data['links'];
?>
Lets take a look at what going on and i’ll explain the important elements.
require_once 'Pager_Wrapper.php';
require_once 'MDB2.php';
Line 1 and 2 we include the require PEAR classes which need to be installed into PEAR on the machine.
Next up are paging options, you can set these to numerical values to suit your needs, perPage value within the array = 10. This sets how many results per page. For more information on the other elements please visit the Pager Package documentation.
pager_options = array(
'mode' => 'Sliding',
'perPage' => 10,
'delta' => 2,
);
Finally we give the SQL query to MDB2 which is another PEAR package. We use this for the database absraction layer, loop the resultset and echo the list to the page.
$query = 'SELECT prod_name, prod_description FROM products';
$paged_data = Pager_Wrapper_MDB2($db, $query, $pager_options);
echo '
- ';
- '.$product['prod_name'].': '.$product['prod_description'].'
foreach ($paged_data['data'] as $product) {
echo '
';
}
echo '
';
//show the links
echo $paged_data['links'];
Finally we echo $paged_data['links']; to produce the pagination. That’s it you’ve learned how to use a more solid paging solution. Go Forth and paginate your heart out!