Many CRUD applications display long lists of search results- perhaps showing all rows in a table. Pagination, or splitting the search results into pages, is a great way to assist users so that they don't get a mile-long list of items and saves database processing power, too! Here is a simple method for paginating search results.
Example of Pagination using this technique
Pagination typically relies on SQL's LIMIT construct which, as the name suggests, LIMITs the number of rows to return. The construct can accept either one or two non-negative integral parameters. With a single parameter, such as:
SELECT id_product, product_name FROM products LIMIT 10
the query would only return the first 10 rows of the result set. With two parameters, such as:
SELECT id_product, product_name FROM products LIMIT 21,5
the query will return 5 rows, starting with row #21 (i.e., 21, 22, 23, 24, & 25). The initial offset is 0, so we would use "LIMIT 0,5" to obtain the first 5 rows of a result set.
The following code is to be used at the top of the page to retrieve the current page and get the limited search results. All non-native functions are used as demonstrative placeholders; you will, of course, need to obtain that data somehow.
<![CDATA[
$results_per_page = 10; //
$page = 1; // default first page. Set it to '1' so that it makes sense with URL parameters
if(array_key_exists('page',$_GET)){
$page = intval($_GET['page']);
}
$page -= 1; // reset to proper indexing
$num_p = get_product_count(); // SELECT COUNT(*)... → The total number of records
$page_count = ceil($num_p / $results_per_page); // The total number of pagess
if($page > $page_count){ // sanity check → if you are requesting a page beyond the last page,
$page_to_view = 0; // display the first page; otherwise, you won't get any search results...
}
$products = get_products($page * $results_per_page,$results_per_page);
]]>
For demonstration purposes, here is a function stub which utilizes the LIMIT feature:
<![CDATA[
public function get_products($start = -1, $items = -1){
// sanitize parameters:
$start = intval($start);
$items = intval($items);
$sql = 'SELECT id_product, product_name FROM products ORDER BY name ASC ';
if($start > -1 && $items > -1){
$sql .= " LIMIT $start, $items ";
}
// ... execute query and return results ...
}
]]>
Here's the HTML / PHP To generate the links to page numbers. Assuming that the previous variable names are not changed, this can be copied directly into your script:
<![CDATA[
<?php echo $num_results ?> Product<?php echo $num_results == 1?'':'s' ?> are listed in this application.<br />
View Page:
<?php if($page > 0) { ?>
<a href="<?php echo $_SERVER['PHP_SELF'] ?>?page=<?php echo $page ?>">◀ Previous</a>
<?php }
for($i=1; $i<=$page_count; $i++){
if($i == ($page + 1)) {
echo ' ' . $i . ' ';
} else { ?>
<a href="<?php echo $_SERVER['PHP_SELF'] ?>?page=<?php echo $i ?>"><?php echo $i ?></a>
<?php } } ?>
<?php if(($page + 2) <= $page_count){ ?>
<a href="<?php echo $_SERVER['PHP_SELF'] ?>?page=<?php echo $page + 2 ?>">Next ▶</a>
<?php } ?>
]]>
