Home jQuery

Simple PHP Pagination Script to Handle Your Growing Data

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.

Simple PHP Pagination Script to Handle Your Growing Data

Contents:
1.0 Video Demo
2.0 Pagination in 5 Easy Steps
2.1 Retrieving the Records
2.2 First and Previous Page
2.3 Number Page Buttons
2.4 Next and Last Page Buttons
2.5 User Input Page Number
3.0 Download Source Code

1.0 Video Demo

Our final output.

2.0 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:

2.1 Retrieve the Records (Step 1)

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 ***** 
}

2.2 First and Previous Page Buttons (Step 2)

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>";
    
}
?>

2.3 Number Page Buttons (Step 3)

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> ";
        }
    }
}

?>

2.4 Next and Last Page Buttons (Step 4)

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>";
}
?>

2.5 Enter a Page Number (Step 5)

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>"; 
?>

3.0 Download Source Code Examples

You can download all the code used in this tutorial for only $9.99 $5.55!

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!

Before you write a comment, please read this guide and our code of conduct.