Ajax Pagination using jQuery with PHP and MySQL

By: CodexWorld | In: PHP | Last Updated: Dec 15, 2021

Pagination is very useful for the data list where a large number of records are listed from the database. It helps to load the dynamic data faster by diving records in multiple pages. The pagination functionality can be integrated using PHP easily. In PHP Pagination, the data is loaded by navigating the links with page reload. If you want to improve the UI of the webpage and make the data list user-friendly, Ajax Pagination is the best choice to load data without page refresh.

Ajax Pagination helps to create pagination links and load dynamic data without page refresh from the database. You can add pagination to the data list without page using Ajax and PHP. In this tutorial, we will show you how to integrate pagination on the web page using jQuery, Ajax, PHP, and MySQL.

In this example script, we will fetch users data dynamically from the MySQL database and list them on the webpage with the pagination links. Through the pagination links, a limited number of records will be retrieved from the database without page reload using jQuery and Ajax. Ajax will be used to implement pagination functionality on a single page without page reload.

Before getting started to build Ajax Pagination in PHP, take a look at the file structure.

ajax_pagination_with_php/
├── dbConfig.php
├── index.php
├── getData.php
├── Pagination.class.php
├── js/
│   └── jquery.min.js
└── css/
    ├── bootstrap.min.css
    └── style.css

Pagination Library

Our custom Pagination library helps you to add Ajax pagination to the data list using PHP. It generates links to control the paging of the data list. Various configuration options are available to customize the pagination and navigation links. Some commonly used configuration options are mentioned below.

baseURL – URL where the Ajax request will send to fetch the records from the database based on the pagination link.
totalRows – Total number of records.
perPage – Number of records that will display on each page.
contentDiv – HTML element ID where the Ajax response data will appear.

<?php 
/** 
 * Pagination Library by CodexWorld 
 * 
 * This Pagination class helps to integrate pagination with Ajax in PHP. 
 * 
 * @class        Pagination 
 * @author        CodexWorld 
 * @link        http://www.codexworld.com 
 * @license        http://www.codexworld.com/license 
 * @version        2.0 
 */ 
class Pagination{ 
    var $baseURL        = ''; 
    var $totalRows      = ''; 
    var $perPage         = 10; 
    var $numLinks        =  2; 
    var $currentPage    =  0; 
    var $firstLink       = '&lsaquo; First'; 
    var $nextLink        = '&gt;'; 
    var $prevLink        = '&lt;'; 
    var $lastLink        = 'Last &rsaquo;'; 
    var $fullTagOpen    = '<div class="pagination">'; 
    var $fullTagClose    = '</div>'; 
    var $firstTagOpen    = ''; 
    var $firstTagClose    = '&nbsp;'; 
    var $lastTagOpen    = '&nbsp;'; 
    var $lastTagClose    = ''; 
    var $curTagOpen        = '&nbsp;<b>'; 
    var $curTagClose    = '</b>'; 
    var $nextTagOpen    = '&nbsp;'; 
    var $nextTagClose    = '&nbsp;'; 
    var $prevTagOpen    = '&nbsp;'; 
    var $prevTagClose    = ''; 
    var $numTagOpen        = '&nbsp;'; 
    var $numTagClose    = ''; 
    var $anchorClass    = ''; 
    var $showCount      = true; 
    var $currentOffset    = 0; 
    var $contentDiv     = ''; 
    var $additionalParam= ''; 
     
    function __construct($params = array()){ 
        if (count($params) > 0){ 
            $this->initialize($params);         
        } 
         
        if ($this->anchorClass != ''){ 
            $this->anchorClass = 'class="'.$this->anchorClass.'" '; 
        }     
    } 
     
    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 the current page     
        if ( ! is_numeric($this->currentPage)){ 
            $this->currentPage = 0; 
        } 
         
        // Links content string variable 
        $output = ''; 
         
        // Showing links notification 
        if ($this->showCount){ 
           $currentOffset = $this->currentPage; 
           $info = 'Showing ' . ( $currentOffset + 1 ) . ' to ' ; 
         
           if( ( $currentOffset + $this->perPage ) < ( $this->totalRows -1 ) ) 
              $info .= $currentOffset + $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 - 1) * $this->perPage; 
        } 
         
        $uriPageNum = $this->currentPage; 
         
        $this->currentPage = floor(($this->currentPage/$this->perPage) + 1); 
 
        // 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){ 
            $output .= $this->firstTagOpen  
                . $this->getAJAXlink( '' , $this->firstLink) 
                . $this->firstTagClose;  
        } 
 
        // Render the "previous" link 
        if  ($this->currentPage != 1){ 
            $i = $uriPageNum - $this->perPage; 
            if ($i == 0) $i = ''; 
            $output .= $this->prevTagOpen  
                . $this->getAJAXlink( $i, $this->prevLink ) 
                . $this->prevTagClose; 
        } 
 
        // Write the digit links 
        for ($loop = $start -1; $loop <= $end; $loop++){ 
            $i = ($loop * $this->perPage) - $this->perPage; 
                     
            if ($i >= 0){ 
                if ($this->currentPage == $loop){ 
                    $output .= $this->curTagOpen.$loop.$this->curTagClose; 
                }else{ 
                    $n = ($i == 0) ? '' : $i; 
                    $output .= $this->numTagOpen 
                        . $this->getAJAXlink( $n, $loop ) 
                        . $this->numTagClose; 
                } 
            } 
        } 
 
        // Render the "next" link 
        if ($this->currentPage < $numPages){ 
            $output .= $this->nextTagOpen  
                . $this->getAJAXlink( $this->currentPage * $this->perPage , $this->nextLink ) 
                . $this->nextTagClose; 
        } 
 
        // Render the "Last" link 
        if (($this->currentPage + $this->numLinks) < $numPages){ 
            $i = (($numPages * $this->perPage) - $this->perPage); 
            $output .= $this->lastTagOpen . $this->getAJAXlink( $i, $this->lastLink ) . $this->lastTagClose; 
        } 
 
        // Remove double slashes 
        $output = preg_replace("#([^:])//+#", "\\1/", $output); 
 
        // Add the wrapper HTML if exists 
        $output = $this->fullTagOpen.$output.$this->fullTagClose; 
         
        return $output;         
    } 
 
    function getAJAXlink( $count, $text) { 
        if( $this->contentDiv == '') 
            return '<a href="'. $this->anchorClass . ' ' . $this->baseURL . $count . '">'. $text .'</a>'; 
 
        $pageCount = $count?$count:0; 
        $this->additionalParam = "{'page' : $pageCount}"; 
         
        return "<a href=\"javascript:void(0);\" " . $this->anchorClass . " 
                onclick=\"$.post('". $this->baseURL."', ". $this->additionalParam .", function(data){ 
                       $('#". $this->contentDiv . "').html(data); }); return false;\">" 
               . $text .'</a>'; 
    } 
}

The following example shows how to use the Pagination class for Ajax Pagination functionality.

// Include pagination library file 
include_once 'Pagination.class.php'; 
 
// Initialize pagination class 
$pagConfig = array( 
    'baseURL' => 'getData.php', 
    'totalRows' => $rowCount, 
    'perPage' => 10, 
    'contentDiv' => 'dataContainer' 
); 
$pagination =  new Pagination($pagConfig);

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

Note that: The Pagination library is included in the source code, you don’t need to download it separately.

Let’s start the Ajax pagination integration process with PHP and MySQL.

Create Database Table

A table is required to store the dynamic data in the database. The following SQL creates a users table with some basic fields in the MySQL database.

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `country` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1=Active | 0=Inactive',
  `created` datetime NOT NULL,
  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 Ajax Pagination (index.php)

Bootstrap Library:
Include the Bootstrap CSS library file, it is used to define the styles for the data list table. If you don’t want to use Bootstrap for table styling, omit it.

<link href="css/bootstrap.min.css" rel="stylesheet">

jQuery Library:
Include the jQuery library, it is required for the Ajax pagination.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>

Loading Image:
The following jQuery code is used to show/hide loading overlay when an ajax request is started/completed.

<script>
// Show loading overlay when ajax request starts
$( document ).ajaxStart(function() {
    $('.loading-overlay').show();
});

// Hide loading overlay when ajax request completes
$( document ).ajaxStop(function() {
    $('.loading-overlay').hide();
});
</script>

Data List with Pagination Links:
Initially, a limited number of records are fetched from the database using PHP & MySQL and listed in a tabular format on the webpage.

Use createLinks() function of the Pagination calss to display pagination links.
When the paging link is clicked, the Ajax request is initiated and sent to the server-side script.
The Ajax pagination library handles the links creation and Ajax request operations.

<?php 
// Include pagination library file 
include_once 'Pagination.class.php'; 
 
// Include database configuration file 
require_once 'dbConfig.php'; 
 
// Set some useful configuration 
$baseURL = 'getData.php'; 
$limit = 5; 
 
// Count of all records 
$query   = $db->query("SELECT COUNT(*) as rowNum FROM users"); 
$result  = $query->fetch_assoc(); 
$rowCount= $result['rowNum']; 
 
// Initialize pagination class 
$pagConfig = array( 
    'baseURL' => $baseURL, 
    'totalRows' => $rowCount, 
    'perPage' => $limit, 
    'contentDiv' => 'dataContainer' 
); 
$pagination =  new Pagination($pagConfig); 
 
// Fetch records based on the limit 
$query = $db->query("SELECT * FROM users ORDER BY id DESC LIMIT $limit"); 
?>

<div class="datalist-wrapper">
    <!-- Loading overlay -->
    <div class="loading-overlay"><div class="overlay-content">Loading...</div></div>
    
    <!-- Data list container -->
    <div id="dataContainer">
        <table class="table table-striped">
        <thead>
            <tr>
                <th scope="col">#</th>
                <th scope="col">First Name</th>
                <th scope="col">Last Name</th>
                <th scope="col">Email</th>
                <th scope="col">Country</th>
                <th scope="col">Status</th>
            </tr>
        </thead>
        <tbody>
            <?php 
            if($query->num_rows > 0){ $i=0; 
                while($row = $query->fetch_assoc()){ $i++; 
            ?>
                <tr>
                    <th scope="row"><?php echo $i; ?></th>
                    <td><?php echo $row["first_name"]; ?></td>
                    <td><?php echo $row["last_name"]; ?></td>
                    <td><?php echo $row["email"]; ?></td>
                    <td><?php echo $row["country"]; ?></td>
                    <td><?php echo ($row["status"] == 1)?'Active':'Inactive'; ?></td>
                </tr>
            <?php 
                } 
            }else{ 
                echo '<tr><td colspan="6">No records found...</td></tr>'; 
            } 
            ?>
        </tbody>
        </table>
        
        <!-- Display pagination links -->
        <?php echo $pagination->createLinks(); ?>
    </div>
</div>

Get Pagination Data (getData.php)

The getData.php file is loaded by Ajax request to retrieve the records from the database.

Fetch the data based on the paging limit and offset.
Render data list with pagination links.
Return the HTML view of the data list.

<?php 
if(isset($_POST['page'])){ 
    // Include pagination library file 
    include_once 'Pagination.class.php'; 
     
    // Include database configuration file 
    require_once 'dbConfig.php'; 
     
    // Set some useful configuration 
    $baseURL = 'getData.php'; 
    $offset = !empty($_POST['page'])?$_POST['page']:0; 
    $limit = 5; 
     
    // Count of all records 
    $query   = $db->query("SELECT COUNT(*) as rowNum FROM users"); 
    $result  = $query->fetch_assoc(); 
    $rowCount= $result['rowNum']; 
     
    // Initialize pagination class 
    $pagConfig = array( 
        'baseURL' => $baseURL, 
        'totalRows' => $rowCount, 
        'perPage' => $limit, 
        'currentPage' => $offset, 
        'contentDiv' => 'dataContainer' 
    ); 
    $pagination =  new Pagination($pagConfig); 
 
    // Fetch records based on the offset and limit 
    $query = $db->query("SELECT * FROM users ORDER BY id DESC LIMIT $offset,$limit"); 
 
?> 
    <!-- Data list container --> 
    <table class="table table-striped"> 
    <thead> 
        <tr> 
            <th scope="col">#</th> 
            <th scope="col">First Name</th> 
            <th scope="col">Last Name</th> 
            <th scope="col">Email</th> 
            <th scope="col">Country</th> 
            <th scope="col">Status</th> 
        </tr> 
    </thead> 
    <tbody> 
        <?php 
        if($query->num_rows > 0){ 
            while($row = $query->fetch_assoc()){ 
                $offset++ 
        ?> 
            <tr> 
                <th scope="row"><?php echo $offset; ?></th> 
                <td><?php echo $row["first_name"]; ?></td> 
                <td><?php echo $row["last_name"]; ?></td> 
                <td><?php echo $row["email"]; ?></td> 
                <td><?php echo $row["country"]; ?></td> 
                <td><?php echo ($row["status"] == 1)?'Active':'Inactive'; ?></td> 
            </tr> 
        <?php 
            } 
        }else{ 
            echo '<tr><td colspan="6">No records found...</td></tr>'; 
        } 
        ?> 
    </tbody> 
    </table> 
     
    <!-- Display pagination links --> 
    <?php echo $pagination->createLinks(); ?> 
<?php 
} 
?>

Load more data from Database using jQuery, Ajax and PHP

Conclusion

Our Ajax Pagination library provides an instant way to implement the pagination functionality without page refresh. You can easily create and add pagination links to the data list using jQuery, Ajax, and PHP. The functionality of the Ajax pagination library can be customized or enhanced as per your needs.

In the next pagination tutorial, we’ll extend the functionality of this Ajax pagination script by adding search and filter option – Ajax Pagination with Search and Filter in PHP

Do you want to get implementation help, or enhance the functionality of this script? Click here to Submit Service Request

Download Source Code View Demo

Adding Google Map on Your Website within 5 Minutes

4 Comments

Gopi Said...

Hi, I need help in pagination, for a bootstrap table I need pagination, i am working with codexworld pagination its working fine but for a table it is not working properly

December 16, 2019 at 10:35 AM
Discount Aftershave Said...

Is it possible to also include drop down for number of results per page?

July 5, 2017 at 12:08 PM
Max Said...

How to include search button in Pagination with jQuery Ajax PHP and MySQL

February 14, 2017 at 7:24 AM

CodexWorld Said...

We’ve recently published a tutorial on how to implement Ajax pagination with search and filter in PHP. Please checkout it from here – http://www.codexworld.com/ajax-pagination-with-search-filter-php/

February 20, 2017 at 4:46 PM

Ajax Pagination using jQuery with PHP and MySQL

Pagination Library

Create Database Table

Database Configuration (dbConfig.php)

Data List with Ajax Pagination (index.php)

Get Pagination Data (getData.php)

Conclusion

RELATED TUTORIALS

4 Comments

Leave a reply

Cancel

SUBSCRIBE FOR FREE NEWSLETTER

Join our 1M+ subscribers and get the latest tutorials and resources, straight to your inbox.

TRENDING TUTORIALS

TOPICSall topics

LATEST HOW TO GUIDES

ABOUT US

Contact

Terms

Extra Links