Pb28 PHP Pagination
In this article, we are going to discuss how to paginate a set of items retrieved from the database by using a PHP script. PHP pagination causes a limited number of results per page and has links to see pages that exist before and after the current page.
By providing a pagination feature for the user on seeing database result, reduces the burden of long scrolls upside down and provides page results within the single scope of the user’s display.
Now, we are going to apply pagination for the results retrieved from the database with the following example.
Example: PHP Pagination
First, we need to create a database and tables as we have done with all MySQL-related examples like Conditional Filtering.
Output for viewing the fifth page is shown in the following figure. There we can see that there are two adjacent pages below and after the current page.
Here, the database named php_samples is created with a table named faq which contains a set of frequently asked PHP questions. Now, to apply pagination to the results retrieved from this table, we need to follow the steps listed below.
- Connect MySQL and select database to access faq tables.
- Get limited results from the database for the current page.
- Calculate total count and trigger pagination.
- Create links for pages.
We have seen the first steps more than enough while seeing about how to access MySQL from PHP. So, we can start with the second step in detail.
Get limited results from the database for the current page.
First, we need to initialize parameters that are used for adding pagination views to the browser. Some of such parameters are, currentPage, startPage and URL to be used to put for page links to navigate among various pages.
And then, we need to retrieve a limited set of results for the current page by providing the start and limiting the count to the SELECT query. The following function is used to perform these steps.
<?php
function getFAQ()
{
$sql = "SELECT * FROM faq";
// getting parameters required for pagination
$currentPage = 1;
if (isset($_GET['pageNumber'])) {
$currentPage = $_GET['pageNumber'];
}
$startPage = ($currentPage - 1) * PERPAGE_LIMIT;
if ($startPage < 0)
$startPage = 0;
$href = "/php_samples/perpage.php?";
// adding limits to select query
$query = $sql . " limit " . $startPage . "," . PERPAGE_LIMIT;
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
$questions[] = $row;
}
if (is_array($questions)) {
$questions["page_links"] = paginateResults($sql, $href);
return $questions;
}
}
?>
Calculate total count and trigger pagination.
This PHP function is used to calculate the total number of results. After that, it invokes the pagination function bypassing this count and the URL for href attributes of the page links.
<?php
// function calculate total records count and trigger pagination function
function paginateResults($sql, $href)
{
$result = mysql_query($sql);
$count = mysql_num_rows($result);
$page_links = pagination($count, $href);
return $page_links;
}
?>
Create links for pages.
This step is used to create views for page links using HTML anchor tags. The user can navigate among two pages that exist before and after the current page. That means the pagination view is created with two adjacent page links before and after the existing page.
Not only the adjacent page, but rather the pagination view that includes other links to navigate among the pages that occur below and above the loop’s lower and upper limits respectively.
Following code, creates pagination output as we discussed.
<?php
function pagination($count, $href)
{
$output = '';
if (! isset($_REQUEST["pageNumber"]))
$_REQUEST["pageNumber"] = 1;
if (PERPAGE_LIMIT != 0)
$pages = ceil($count / PERPAGE_LIMIT);
// if pages exists after loop's lower limit
if ($pages > 1) {
if (($_REQUEST["pageNumber"] - 3) > 0) {
$output = $output . '<a href="' . $href . 'pageNumber=1" class="page">1</a>';
}
if (($_REQUEST["pageNumber"] - 3) > 1) {
$output = $output . '...';
}
// Loop for provides links for 2 pages before and after current page
for ($i = ($_REQUEST["pageNumber"] - 2); $i <= ($_REQUEST["pageNumber"] + 2); $i ++) {
if ($i < 1)
continue;
if ($i > $pages)
break;
if ($_REQUEST["pageNumber"] == $i)
$output = $output . '<span id=' . $i . ' class="current">' . $i . '</span>';
else
$output = $output . '<a href="' . $href . "pageNumber=" . $i . '" class="page">' . $i . '</a>';
}
// if pages exists after loop's upper limit
if (($pages - ($_REQUEST["pageNumber"] + 2)) > 1) {
$output = $output . '...';
}
if (($pages - ($_REQUEST["pageNumber"] + 2)) > 0) {
if ($_REQUEST["pageNumber"] == $pages)
$output = $output . '<span id=' . ($pages) . ' class="current">' . ($pages) . '</span>';
else
$output = $output . '<a href="' . $href . "pageNumber=" . ($pages) . '" class="page">' . ($pages) . '</a>';
}
}
return $output;
}
?>
And then, these functions that are used to implement the above steps are called by embedding PHP scripts within the HTML pages to display pagination results to the browser. Look into the following code to display paginated results to the browsers.
<html>
<head>
<title>PHP Pagination</title>
<link rel="stylesheet" type="text/css" href="styles.css" />
<head>
<body>
<table border="0" cellpadding="10" cellspacing="1" width="500"
align="center">
<tr class="tableheader">
<td>Frequently Asked Questions</td>
</tr>
<?php
connectDB();
$questions = getFAQ();
if (is_array($questions)) {
for ($i = 0; $i < count($questions) - 1; $i ++) {
?>
<tr class="tablerow">
<td><h2><?php echo $questions[$i]["questionTitle"]; ?></h2>
<br /> <span class="date">Added Date: <?php echo $questions[$i]["addedDate"]; ?></span></td>
</tr>
<?php
}
?>
<tr class="tableheader">
<td colspan="2"><?php echo $questions["page_links"]; ?></td>
</tr>
<?php
}
?>
</table>
</body>
</html>
Download