Pagination in PHP with MySQL


Pagination is a common functionality used almost every web application. When the huge data need to be retrieved from the database, it’s not a good idea to display all result on one page. In that case, we 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 class, which can be reused in various web projects.

In the example script, we will fetch the records from the posts table of 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 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

Create Database Table

The data will be retrieved from the posts table and listed with the pagination links. The following SQL creates a posts table in MySQL database with some basic columns.

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;

Pagination Class

The Pagination class generates links to control paging of the data list. Various configuration options are available to customize the pagination links.

  • $baseURL – URL of the webpage.
  • $totalRows – Total number of items.
  • $perPage – The number of records want 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     1.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 
$currentOffset0;
    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 == 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 $_GET[$this->queryStringSegment];
        
        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 class in PHP.
At first, include and initialize the Pagination class with some basic configuration.

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

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

To render pagination links in the web page use createLinks() function.

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

Pagination with jQuery Ajax PHP and MySQL

Database Configuration (dbConfig.php)

The dbConfig.php is used to connect and select the MySQL database. Specify the database host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName) as per your database server credentials.

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

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

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

Display Data with Pagination (index.php)

In the index.php file, a limited number of posts will be retrived from the database and listed with the pagination links. You need to create an object of the Pagination class and pass some basic configuration to generate pagination links. To display the pagination links, use createLinks() function.

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

//include database configuration file
include_once 'dbConfig.php';

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

//get number of rows
$queryNum $db->query("SELECT COUNT(*) as postNum FROM posts");
$resultNum $queryNum->fetch_assoc();
$rowCount $resultNum['postNum'];

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

//get rows
$query $db->query("SELECT * FROM posts ORDER BY id DESC LIMIT $offset,$limit");

if(
$query->num_rows 0){ ?>
    <div class="posts_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 
?>

Ajax Pagination in CodeIgniter Framework

Conclusion

We have tried to make PHP pagination simple as much as possible. Hope, our example script will help you to implement pagination functionality in PHP. Also, you can easily extend the functionality of PHP Pagination class as per your requirements. You can use Ajax Pagination with PHP to provide better user experience to your web application.

Are you want to get implementation help, or modify or extend the functionality of this script? Submit paid service request

Recommended Tutorials For You

Leave a reply