PHP Pagination

by Vincy. Last modified on July 3rd, 2022.
For an improved version of this script, visit PHP Pagination MySQL Database Example Script with Previous Next like Google.

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.

php_pagination_output

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

Comments to “PHP Pagination”

  • danish says:

    nice toturial

  • danish says:

    can we split it into two files so that all pagination code resides on one file and then include that file to other php files and make pagination by calling the function?

  • Md. Abdus Salam says:

    Mam, I am PHP Programmer. I follow your code. Your’s code working well for me. But Your pagination not working for me after cross 2000 data. Please answer me if it’s wrong. I am using your script in a huge project there have lots of data.

    Thanks

    • Vincy says:

      Abdus, I am using the same pagination code in a project which has more than 100,000 records and it is working good. Can you let me know the error you are getting. Send more information on the behavior and I will help you out. Thanks.

  • gaby says:

    This is great!

Comments are closed.

↑ Back to Top

Share this page