PHP Pagination

In this article we are going to discuss about how to paginate set of items retrieved from database by using PHP script. PHP pagination cause with limited number of results per page and have links to see pages that exists before and after the current page.

By providing pagination feature for the user on seeing database result, it reduces the burden with long scrolls upside down and provides page results within the single scope of user’s display.

Now, we are going to apply pagination for the results retrieved from database with the following example.

php_pagination

Example: PHP Pagination

First, we need to create database and tables as we have done with all MySQL related examples like Conditional Filtering.

Here, the database named php_samples is created with a table named as faq which contains 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 database for current page.
  • Calculate total count and trigger pagination.
  • Create links for pages.

We have seen about 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 database for 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 limited set of results for current page by providing start and limit count to the SELECT query. The following function is used to perform this steps.

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 total number of results. After that, it invokes pagination function bypassing this count and the URL for href attributes of the page links.

//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. User can navigate among two pages that are exists 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, rather 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, that creates pagination output as we discussed.

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 HTML page to display pagination results to the browser. Look in to 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>

PHP Pagination Output:

Output for viewing fifth page as shown in the following figure. There we can see that, there are two adjacent pages below and after current page.

php_pagination_output

Download PHP Pagination Source Code

This PHP code tutorial was published on June 7, 2013.

↑ Back to Top