Pagination in PHP with MySQL

Pagination is a commonly used functionality on the data list in almost every web application. Pagination divides the data between pages to load the dynamic data faster. Generally, all the data is listed on the webpage. But when the web application handles huge data and needs to be retrieved from the database, it’s not a good idea to load all the records at once. It will take much time to display all the records in a single page and sometimes your application may be hung to load the huge amount of data. To overcome this issue you can divide the records into pages instead of showing all at once. Pagination makes it simple to spread the records in multiple pages and easier to view.

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

In the example script, we will fetch the records from the posts table of the MySQL database and list them with pagination links. Through the pagination link, limited numbers of records will be retrieved from the MySQL database.

Before getting started, take a look the files structure to create a simple PHP pagination and integrate into the web application.

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

Pagination Library

The Pagination class helps you to add pagination to data list using PHP and MySQL. It generates links to control paging of the data list. Various configuration options are available to customize the pagination and navigation links.

• $baseURL – URL of the webpage.
• $totalRows – Total number of items.
• $perPage – The number of records wants to display on per page.
• $numLinks – 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
/**
 * CodexWorld
 *
 * This Pagination class helps to integrate pagination in PHP.
 *
 * @class      Pagination
 * @author     CodexWorld
 * @link       http://www.codexworld.com
 * @license    http://www.codexworld.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 to use the Pagination library class in PHP.

At first, include 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(); ?>

Pagination in CodeIgniter

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

Create Database Table

A table needs to be created in the database from where the data will be listed with pagination links. The following SQL creates a posts table with some basic 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)

The dbConfig.php file is used to connect and select the database. Specify 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)

Initially, a limited number of records (posts data) are fetched from the database and listed with the pagination links.

• $baseURL – Specify the base URL of the web page.
• $limit – Set the limit of the records that you want to display each page.
• Initialize the Pagination class and set some basic configuration options.
• Call the createLinks() function to generate 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

Our PHP Pagination library makes it easier to add pagination feature to the data tables. You can easily implement the pagination functionality in PHP with MySQL. The functionality of the Pagination class can be extended as per your website needs. If you want to provide better user experience, use Ajax Pagination with PHP in your web application.