codeofaninja
website

Simple PHP Pagination Script to Handle Your Growing Data

Photo of Mike Dalisay
Modified Wednesday, June 5, 2013
by - @ninjazhai
Contents:



1.0 Introduction


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


2.0 Video Demo


Our final output.


3.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:


3.1 Retrieve the Records (Step 1)


We will show the records in a 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 ***** 
}


3.2 First and Previous Page Buttons (Step 2)


This is the buttons with signs "<<" and "<".

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


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

?>



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


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


4.0 Complete Code


Here's the complete code to enjoy:

<!DOCTYPE HTML>
<html>
    <head>
        <title>PHP Paging Tutorial Demo</title>
        <link rel="stylesheet" type="text/css" href="css/style.css">
    </head>
<body>

<?php
// include 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> ***************
    echo "<div id='paging'>";

        // ***** 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>";
            
        }
        
        
        // ********** 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> ";
                }
            }
        }
        
        
        // ***** 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>";
        }
        
    echo "</div>";
    
    // ***** 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>"; 
    
    // *************** </PAGING_SECTION> ***************
}

// tell the user if no records were found
else{
    echo "<div class='noneFound'>No records found.</div>";
}
?>

</body>
</html>

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

I hope you enjoy this simple PHP Paging code!
For FREE programming tutorials, click the red button below and subscribe! :)
Thanks for the comments!
 
 
Fundamentals
"First do it, then do it right, then do it better."
~ Addy Osmani
"Talk is cheap. Show me the code."
~ Linus Torvalds
Let's Stay Connected!
g+ r
Android app on Google Play
© 2011-2014 The Code Of A Ninja. All rights reserved. Proudly Powered by Google Blogger. Images, logos, marks or names mentioned herein are the property of their respective owners.