Pagination is a frequently used function on the data list in nearly every web application. Similarly, pagination distributes the data between pages to load the dynamic data faster. Usually, there is a list of all the data on the web page. Probably, it would be best if you retrieved when the web application handles vast data from the database. But, it may be a bad idea to load all the records once. Because, it will take much time to display all the files on a single page, and sometimes hung your application to load the massive amount of data. As a result, to overcome this issue, you can divide records into pages instead of showing all at once. Pagination makes it simple to spread the documents in multiple pages and more comfortable to view.

What is Application Programming Interface (APIs)?

Like Dislike Rating System with jQuery, Ajax, and PHP

Star Rating System with jQuery, Ajax, PHP, and MySQL

PHP Pagination permits to paging data into pages and access by links. In this tutorial, we explain to you how to build simple pagination in PHP with MySQL. Therefore, to make PHP pagination script reusable, we will create a Pagination library that can be reused in multiple web projects.

For instance, we will attach the records from the posts table of the MySQL database and make a list with pagination links. Through the pagination link, You can retrieve the limited numbers of records from the MySQL database.

Before proceeding, let s have a look at the file structure to create a simple PHP pagination and integrate it into the web application.

php_pagination/
Pagination.class.php
dbConfig.php
index.php

Pagination Library

The pagination class allows you to add pagination to the data list using PHP and MySQL. It will generate links to control the paging of the data list. Various configuration options are available for customizing paging and navigation links.

$baseURL – URL of the webpage.
$totalRows – A total number of items.
$perPage – The number of records wants to display on per page.
$numLinks – A number of links to show.
$firstLink – First link label.
$nextLink – Next link label.
$prevLink – Previous link label.
$lastLink – Last link label.
$fullTagOpen – Full open tag.
$fullTagClose – Full close tag.
$firstTagOpen – First, open tag.
$firstTagClose – First close tag.
$lastTagOpen – Last open tag.
$lastTagClose – Last close tag.
$curTagOpen – Current open tag.
$curTagClose – Current close tag.
$nextTagOpen – Next open tag.
$nextTagClose – Next close tag.
$prevTagOpen – Previous open tag.
$prevTagClose – Previous close tag.
$numTagOpen – Number open tag.
$numTagClose – Number close tag.
$showCount – Show links count.
$queryStringSegment – Page query string flag.

<?php
/**
 * Allsweb
 *
 * This Pagination class helps to integrate pagination in PHP.
 *
 * @class      Pagination
 * @author     Allsweb
 * @link       http://www.allsweb.com
 * @license    http://www.allsweb.com/license
 * @version    2.0
 */
class Pagination{
    protected $baseURL        = '';
    protected $totalRows      = '';
    protected $perPage        = 10;
    protected $numLinks       = 2;
    protected $currentPage    =  0;
    protected $firstLink      = 'First';
    protected $nextLink       = 'Next &raquo;';
    protected $prevLink       = '&laquo; Prev';
    protected $lastLink       = 'Last';
    protected $fullTagOpen    = '<div class="pagination">';
    protected $fullTagClose   = '</div>';
    protected $firstTagOpen   = '';
    protected $firstTagClose  = '&nbsp;';
    protected $lastTagOpen    = '&nbsp;';
    protected $lastTagClose    = '';
    protected $curTagOpen    = '&nbsp;<b>';
    protected $curTagClose    = '</b>';
    protected $nextTagOpen    = '&nbsp;';
    protected $nextTagClose    = '&nbsp;';
    protected $prevTagOpen    = '&nbsp;';
    protected $prevTagClose    = '';
    protected $numTagOpen    = '&nbsp;';
    protected $numTagClose    = '';
    protected $showCount    = true;
    protected $currentOffset= 0;
    protected $queryStringSegment = 'page';
    
    function __construct($params = array()){
        if (count($params) > 0){
            $this->initialize($params);        
        }
    }
    
    function initialize($params = array()){
        if (count($params) > 0){
            foreach ($params as $key => $val){
                if (isset($this->$key)){
                    $this->$key = $val;
                }
            }        
        }
    }
    
    /**
     * Generate the pagination links
     */    
    function createLinks(){ 
        // If total number of rows is zero, do not need to continue
        if ($this->totalRows == 0 OR $this->perPage == 0){
           return '';
        }
        // Calculate the total number of pages
        $numPages = ceil($this->totalRows / $this->perPage);
        // Is there only one page? will not need to continue
        if ($numPages == 1){
            if ($this->showCount){
                $info = 'Showing : ' . $this->totalRows;
                return $info;
            }else{
                return '';
            }
        }
        
        // Determine query string
        $query_string_sep = (strpos($this->baseURL, '?') === FALSE) ? '?page=' : '&amp;page=';
        $this->baseURL = $this->baseURL.$query_string_sep;
        
        // Determine the current page
        $this->currentPage = isset($_GET[$this->queryStringSegment])?$_GET[$this->queryStringSegment]:0;
        
        if (!is_numeric($this->currentPage) || $this->currentPage == 0){
            $this->currentPage = 1;
        }
        
        // Links content string variable
        $output = '';
        
        // Showing links notification
        if ($this->showCount){
           $currentOffset = ($this->currentPage > 1)?($this->currentPage - 1)*$this->perPage:$this->currentPage;
           $info = 'Showing ' . $currentOffset . ' to ' ;
        
           if( ($currentOffset + $this->perPage) <= $this->totalRows )
              $info .= $this->currentPage * $this->perPage;
           else
              $info .= $this->totalRows;
        
           $info .= ' of ' . $this->totalRows . ' | ';
        
           $output .= $info;
        }
        
        $this->numLinks = (int)$this->numLinks;
        
        // Is the page number beyond the result range? the last page will show
        if($this->currentPage > $this->totalRows){
            $this->currentPage = $numPages;
        }
        
        $uriPageNum = $this->currentPage;
        
        // Calculate the start and end numbers. 
        $start = (($this->currentPage - $this->numLinks) > 0) ? $this->currentPage - ($this->numLinks - 1) : 1;
        $end   = (($this->currentPage + $this->numLinks) < $numPages) ? $this->currentPage + $this->numLinks : $numPages;
        
        // Render the "First" link
        if($this->currentPage > $this->numLinks){
            $firstPageURL = str_replace($query_string_sep,'',$this->baseURL);
            $output .= $this->firstTagOpen.'<a href="'.$firstPageURL.'">'.$this->firstLink.'</a>'.$this->firstTagClose;
        }
        // Render the "previous" link
        if($this->currentPage != 1){
            $i = ($uriPageNum - 1);
            if($i == 0) $i = '';
            $output .= $this->prevTagOpen.'<a href="'.$this->baseURL.$i.'">'.$this->prevLink.'</a>'.$this->prevTagClose;
        }
        // Write the digit links
        for($loop = $start -1; $loop <= $end; $loop++){
            $i = $loop;
            if($i >= 1){
                if($this->currentPage == $loop){
                    $output .= $this->curTagOpen.$loop.$this->curTagClose;
                }else{
                    $output .= $this->numTagOpen.'<a href="'.$this->baseURL.$i.'">'.$loop.'</a>'.$this->numTagClose;
                }
            }
        }
        // Render the "next" link
        if($this->currentPage < $numPages){
            $i = ($this->currentPage + 1);
            $output .= $this->nextTagOpen.'<a href="'.$this->baseURL.$i.'">'.$this->nextLink.'</a>'.$this->nextTagClose;
        }
        // Render the "Last" link
        if(($this->currentPage + $this->numLinks) < $numPages){
            $i = $numPages;
            $output .= $this->lastTagOpen.'<a href="'.$this->baseURL.$i.'">'.$this->lastLink.'</a>'.$this->lastTagClose;
        }
        // Remove double slashes
        $output = preg_replace("#([^:])//+#", "\\1/", $output);
        // Add the wrapper HTML if exists
        $output = $this->fullTagOpen.$output.$this->fullTagClose;
        
        return $output;        
    }
}

Basic Usage

Here’s a quick example of using the Pagination library class in PHP.

At first, include the Pagination library file and initialize the Pagination class with some basic configuration.

<?php

// Include pagination library file
include_once 'Pagination.class.php';

// Initialize pagination class
$pagConfig = array(
    'baseURL'=>'http://example.com/php_pagination/index.php',
    'totalRows'=>$rowCount,
    'perPage'=>$limit
);
$pagination =  new Pagination($pagConfig);

?>

To render the pagination links in the data list, call the createLinks() function.

<!-- Display pagination links -->
<?php echo $pagination->createLinks(); ?>

Now, we will mix pagination functionality in the posts lists using the PHP Pagination library.

Create Database Table

It would be best if you created a table in the database from where you will list the data with pagination links. The following SQL builds a posts table with some necessary fields in the MySQL database.

CREATE TABLE `posts` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `created` datetime NOT NULL,
 `modified` datetime NOT NULL,
 `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1=Active | 0=Inactive',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Database Configuration (dbConfig.php)

Use the dbConfig.php file to connect and select the database. Stipulate the database host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName) as per your MySQL database server credentials.

<?php
// Database configuration
$dbHost     = "localhost";
$dbUsername = "root";
$dbPassword = "root";
$dbName     = "codexworld";

// Create database connection
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);

// Check connection
if ($db->connect_error) {
    die("Connection failed: " . $db->connect_error);
}

Data List with Pagination (index.php)

At first, attach a limited number of records (posts data) from the database and listed with the pagination links.

$baseURL – Stipulate the base URL of the web page.
$limit – Set the threshold of the records that you want to display each page.
Reset the Pagination class and set some basic configuration options.
Call the createLinks() function to create and display pagination links.

<?php
// Include pagination library file
include_once 'Pagination.class.php';

// Include database configuration file
require_once 'dbConfig.php';

// Set some useful configuration
$baseURL = 'http://example.com/php_pagination/index.php';
$limit = 5;

// Paging limit & offset
$offset = !empty($_GET['page'])?(($_GET['page']-1)*$limit):0;

// Count of all records
$query   = $db->query("SELECT COUNT(*) as rowNum FROM posts");
$result  = $query->fetch_assoc();
$rowCount= $result['rowNum'];

// Initialize pagination class
$pagConfig = array(
    'baseURL' => $baseURL,
    'totalRows'=>$rowCount,
    'perPage'=>$limit
);
$pagination =  new Pagination($pagConfig);

// Fetch records based on the offset and limit
$query = $db->query("SELECT * FROM posts ORDER BY id DESC LIMIT $offset,$limit");

if($query->num_rows > 0){
?>
    <!-- Display posts list -->
    <div class="post-list">
    <?php while($row = $query->fetch_assoc()){ ?>
        <div class="list-item">
            <a href="javascript:void(0);"><?php echo $row["title"]; ?></a>
        </div>
    <?php } ?>
    </div>
    
    <!-- Display pagination links -->
    <?php echo $pagination->createLinks(); ?>
<?php } ?>

Conclusion

After going through the above example, Firstly, our PHP Pagination library makes it easier to add a pagination feature to the data tables. Secondly, you can quickly implement the pagination functionality in PHP with MySQL. And lastly, you have to extend the functionality of the Pagination class as per your website needs. If you want to give a better user experience, use Ajax Pagination with PHP in your web application.

Also, read our previous blog- Credit Card Validation using jQuery

Tags: Pagination PHP and MySQL