Simple PHP Pagination Script to Handle Your Growing Data

[adinserter block=”34″]PHP Pagination is another very important feature when building any web application.

When you grow your data, it must not be displayed on a single page – it can cause a browser hang or very long vertical scroll-bar, so in short, it is not good for the user experience.

Video Demo

Our final output.

Pagination in 5 Easy Steps

We are going the build this PHP paging script, in just 5 easy steps, see 3.1 to 3.5 below:

Retrieve the Records

This code will connect us to a MySQL database. Create “libs” folder. Open that folder. Create “db_connect.php” file and place the following code.

<?php
$host = "CHANGE_TO_YOUR_HOST";
$db_name = "CHANGE_TO_YOUR_DATABASE_NAME";
$username = "CHANGE_TO_YOUR_DATABASE_USERNAME";
$password = "CHANGE_TO_YOUR_DATABASE_PASSWORD";

try {
    $con = new PDO("mysql:host={$host};dbname={$db_name}", $username, $password);
}

//to handle connection error
catch(PDOException $exception){
    echo "Connection error: " . $exception->getMessage();
}
?>

Next, we will show the records in an HTML table.

<?php
// include for database connection
include 'libs/db_connect.php';

// page is the current page, if there's nothing set, default is page 1
$page = isset($_GET['page']) ? $_GET['page'] : 1;

// set records or rows of data per page
$recordsPerPage = 3;

// calculate for the query LIMIT clause
$fromRecordNum = ($recordsPerPage * $page) - $recordsPerPage;

// select all data
$query = "SELECT
            id, firstname, lastname, username
        FROM
            users
        ORDER BY
            id desc
        LIMIT
            {$fromRecordNum}, {$recordsPerPage}";

            /*
            page and its LIMIT clause looks like:
            1 = 0, 5
            2 = 5,10
            3 = 10,15
            4 = 15, 20
            5 = 20, 25
            */

$stmt = $con->prepare( $query );
$stmt->execute();

//this is how to get number of rows returned
$num = $stmt->rowCount();

//check if more than 0 record found
if($num>0){

    //start table
    echo "<table id='tfhover' class='tftable' border='1'>";

        //creating our table heading
        echo "<tr>";
            echo "<th>Firstname</th>";
            echo "<th>Lastname</th>";
            echo "<th>Username</th>";
        echo "</tr>";

        //retrieve our table contents
        //fetch() is faster than fetchAll()
        //http://stackoverflow.com/questions/2770630/pdofetchall-vs-pdofetch-in-a-loop
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){

            //extract row, this will make $row['firstname'] to just $firstname only
            extract($row);

            //creating new table row per record
            echo "<tr>";
                echo "<td>{$firstname}</td>";
                echo "<td>{$lastname}</td>";
                echo "<td>{$username}</td>";
            echo "</tr>";
        }

    echo "</table>";//end table

    // ***** Paging section will be here *****
}

First and Previous Page Buttons

The code below will show the “First” and “Previous” page buttons.

// ***** for 'first' and 'previous' pages
if($page>1){
    // ********** show the first page
    echo "<a href='" . $_SERVER['PHP_SELF'] . "' title='Go to the first page.' class='customBtn'>";
        echo "<span style='margin:0 .5em;'> << </span>";
    echo "</a>";

    // ********** show the previous page
    $prev_page = $page - 1;
    echo "<a href='" . $_SERVER['PHP_SELF']
            . "?page={$prev_page}' title='Previous page is {$prev_page}.' class='customBtn'>";
        echo "<span style='margin:0 .5em;'> < </span>";
    echo "</a>";

}
?>

Number Page Buttons

We are going to have some simple calculations here. The goal of this code is to display some page numbers that users can click.

<?php
// ********** show the number paging

// find out total pages
$query = "SELECT COUNT(*) as total_rows FROM users";
$stmt = $con->prepare( $query );
$stmt->execute();

$row = $stmt->fetch(PDO::FETCH_ASSOC);
$total_rows = $row['total_rows'];

$total_pages = ceil($total_rows / $recordsPerPage);

// range of num links to show
$range = 2;

// display links to 'range of pages' around 'current page'
$initial_num = $page - $range;
$condition_limit_num = ($page + $range)  + 1;

for ($x=$initial_num; $x<$condition_limit_num; $x++) {

    // be sure '$x is greater than 0' AND 'less than or equal to the $total_pages'
    if (($x > 0) && ($x <= $total_pages)) {

        // current page
        if ($x == $page) {
            echo "<span class='customBtn' style='background:red;'>$x</span>";
        }

        // not current page
        else {
            echo " <a href='{$_SERVER['PHP_SELF']}?page=$x' class='customBtn'>$x</a> ";
        }
    }
}

?>

Next and Last Page Buttons

This will show the buttons with characters “>” and “>>” (meaning ‘next’ and ‘last’)

<?php
// ***** for 'next' and 'last' pages
if($page<$total_pages){
    // ********** show the next page
    $next_page = $page + 1;
    echo "<a href='" . $_SERVER['PHP_SELF'] . "?page={$next_page}' title='Next page is {$next_page}.' class='customBtn'>";
        echo "<span style='margin:0 .5em;'> > </span>";
    echo "</a>";

    // ********** show the last page
    echo "<a href='" . $_SERVER['PHP_SELF'] . "?page={$total_pages}' title='Last page is {$total_pages}.' class='customBtn'>";
        echo "<span style='margin:0 .5em;'> >> </span>";
    echo "</a>";
}
?>

Enter a Page Number

This will show a text-box where the user can enter a page number and submit it by hitting the enter key or clicking the ‘Go’ button.

<?php
// ***** allow user to enter page number
echo "<form action='" . $_SERVER['PHP_SELF'] . "' method='GET'>";
    echo "Go to page: ";
    echo "<input type='text' name='page' size='1' />";
    echo "<input type='submit' value='Go' class='customBtn' />";
echo "</form>";
?>

Download Source Code Examples

You can download all the code used in this tutorial for only $9.99 $5.55!
[purchase_link id=”12382″ text=”Download Now” style=”button” color=”green”]

If you want to paginate with jQuery, here’s a post I made: Pagination with jQuery and PHP

Thank you for learning from our post about: Simple PHP Pagination Script!