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

Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.

↑ Back to Top

• PHP
  • Learn PHP
  • PHP Introduction
  • PHP Basics
  • PHP Shopping Cart
  • Payment Gateway
  • Contact Form
  • PHP Login Registration
  • Comments System
  • Types, Variables & Operators
  • PHP Strings
  • PHP Arrays
  • PHP Functions
  • PHP OOPS
  • Event Management System
  • PHP Mail
  • PHP Forms
  • Advanced
  • PHP AJAX
  • RESTful API
  • PHP Databases
    • PHP Responsive Image Gallery using CSS Media Queries
    • PHP MyAdmin
    • Prevent SQL Injection in PHP
    • How to Import SQL into a Database using PHP
    • How to Backup MySQL Database using PHP
    • Restore MySQL Database using PHP
    • Import Excel File into MySQL Database using PHP
    • PHP Database Backup Client for MySQL
    • MySQL Fetch using PHP
    • Access MySQL from PHP
    • PHP CRUD with MySQL
    • CRUD with MySQLi Prepared Statement using PHP
    • PHP PDO CRUD
    • Object-Oriented CRUD System using PHP PDO and MySQL
    • Amazon like Product Category Tree
    • PHP Input Filtering
    • Filtering MySQL Results by Applying PHP Conditions
    • Multi-Select Dropdown Filter in PHP with Database Search
    • Sorting MySQL Column using PHP
    • DataTables Server-side Processing using PHP with MySQL
    • Column Search in DataTables using Server-side Processing
    • Loading List box Dynamically
    • Update/Delete Multiple Rows using PHP
    • PHP Pagination
    • Ajax Pagination with Tabular Records using PHP and jQuery
    • MySQL BLOB using PHP
    • PHP Search and Pagination using PDO
    • Bootstrap Pagination Example in PHP
  • PHP Sessions and Cookies
  • Error and Exception Handling
  • File Upload
  • File Import Export
  • Files and Directories
  • PHP Date Time
  • PHP XML
  • PHP CSV
  • PHP JSON
  • PHP Code Samples
  • Library
  • PHP Freelancer
• jQuery
• JavaScript
• WordPress
• MySQL
• CSS

• Share
• Tweet
• Share

1. Simple PHP Shopping Cart
2. Stripe Payment Gateway Integration using PHP
3. User Registration in PHP with Login: Form with MySQL and Code Download
4. PHP Contact Form
5. How to Create Dynamic Stacked Bar, Doughnut and Pie charts in PHP with Chart.js