Ajax Pagination with Search and Filter in PHP

The pagination without page refresh can be easily integrated into the data list using jQuery, Ajax, PHP, and MySQL. Ajax Pagination is the best option to make the data list user-friendly. In the earlier tutorial, we have shown how to integrate Ajax pagination with PHP in the web application. Now, we will enhance the Ajax pagination script functionality with search and filter features.

Search is the most useful feature of the data management section. It helps to filter the data from a large number of records quickly. The user can find the relevant data and sort the results set using the search functionality. In this tutorial, we will show you how to integrate Ajax pagination with search and filter using jQuery, PHP, and MySQL.

In the example Ajax search script, we will add the pagination and search feature to the users data list.

  • Fetch the dynamic records from the database and listed on the webpage using PHP & MySQL.
  • Generate links and add pagination to the data list.
  • Add search and filter options to the data list.
  • Allow the user to paginate and search records without page reload.

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

ajax_pagination_with_search_filter/
├── 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 integrate Ajax pagination with search functionality using PHP. The Ajax Pagination class creates links to control the paging of the data list. The following minimum configurations are needed to add Ajax pagination with search to the data list.

  • baseURL – URL where the Ajax request will send to fetch the records from the database.
  • totalRows – Total number of records.
  • perPage – Record numbers to display on each page.
  • currentPage – Current page number.
  • contentDiv – HTML element ID where the Ajax response data will appear.
  • link_func – Function name that handles search functionality.
<?php 
/**
 * CodexWorld is a programming blog. Our mission is to provide the best online resources on programming and web development.
 *
 * This Pagination class helps to integrate ajax pagination in PHP.
 *
 * @class        Pagination
 * @author        CodexWorld
 * @link        http://www.codexworld.com
 * @contact        http://www.codexworld.com/contact-us
 * @version        1.0
 */
class Pagination{
    var 
$baseURL        '';
    var 
$totalRows      '';
    var 
$perPage        10;
    var 
$numLinks       =  3;
    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'';
    var 
$link_func      '';
    
    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 == 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 '<p>Showing : ' $this->totalRows.'</p>';
                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 ) . ' to ' ;
        
           if( (
$currentOffset $this->perPage) < $this->totalRows)
              
$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->link_func == '' && $this->contentDiv == '')
            return 
'<a href="'.$this->baseURL.'?'.$count.'"'.$this->anchorClass.'>'.$text.'</a>';
        
        
$pageCount $count?$count:0;
        if(!empty(
$this->link_func)){
            
$linkClick 'onclick="'.$this->link_func.'('.$pageCount.')"';
        }else{
            
$this->additionalParam "{'page' : $pageCount}";
            
$linkClick "onclick=\"$.post('"$this->baseURL."', "$this->additionalParam .", function(data){
                       $('#"
$this->contentDiv "').html(data); }); return false;\"";
        }
        
        return 
"<a href=\"javascript:void(0);\" " $this->anchorClass "
                "
$linkClick .">"$text .'</a>';
    }
}
?>

Create Database Table

A table is required to store the dynamic data in the database. The following SQL creates an 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 and Search (index.php)

Initially, the limited numbers of data are fetched from the database and listed with pagination links, search input, and filter dropdown. Using pagination links the user can get the additional data from the MySQL database without page refresh using jQuery and Ajax. Also, the user can filter the post data using the search box and filter dropdown.

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

<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>

Search Function:
The searchFilter() is a custom JavaScript function that handles the search and filter functionality using jQuery.

  • Get the search input and filter dropdown value.
  • Send the search and filter request to the server-side script (getData.php) via Ajax.
  • Set the content of dataContainer HTML element and update the data list with filtered records.
<script>
function searchFilter(page_num) {
    page_num = page_num?page_num:0;
    var keywords = $('#keywords').val();
    var filterBy = $('#filterBy').val();
    $.ajax({
        type: 'POST',
        url: 'getData.php',
        data:'page='+page_num+'&keywords='+keywords+'&filterBy='+filterBy,
        beforeSend: function () {
            $('.loading-overlay').show();
        },
        success: function (html) {
            $('#dataContainer').html(html);
            $('.loading-overlay').fadeOut("slow");
        }
    });
}
</script>

Search Form:
Define the HTML elements for the search input and sort by filter select box.

  • Set the searchFilter() method with onkeyup/onchange event to trigger the search option.
<div class="search-panel">
    <div class="form-row">
        <div class="form-group col-md-6">
            <input type="text" class="form-control" id="keywords" placeholder="Type keywords..." onkeyup="searchFilter();">
        </div>
        <div class="form-group col-md-4">
            <select class="form-control" id="filterBy" onchange="searchFilter();">
                <option value="">Filter by Status</option>
                <option value="1">Active</option>
                <option value="0">Inactive</option>
            </select>
        </div>
    </div>
</div>

Add Pagination:
Initially, a limited number of records are fetched from the database and listed with the pagination links in a tabular format.

  • Use createLinks() function of the Pagination class to generate and display pagination links.
  • When the paging link is clicked, the Ajax request is initiated and sent to the server-side script.
<?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',
    
'link_func' => 'searchFilter'
);
$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>

Search and Get Pagination Data (getData.php)

The getData.php file is loaded by Ajax request (called from searchFilter() function) to retrieve the records from the database.

  • Fetch the data based on the search query and paging limit & offset.
  • Render filtered data list with pagination links.
  • Return the HTML view of the users 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;
    
    
// Set conditions for search
    
$whereSQL '';
    if(!empty(
$_POST['keywords'])){
        
$whereSQL " WHERE (first_name LIKE '%".$_POST['keywords']."%' OR last_name LIKE '%".$_POST['keywords']."%' OR email LIKE '%".$_POST['keywords']."%' OR country LIKE '%".$_POST['keywords']."%') ";
    }
    if(isset(
$_POST['filterBy']) && $_POST['filterBy'] != ''){
        
$whereSQL .= (strpos($whereSQL'WHERE') !== false)?" AND ":" WHERE ";
        
$whereSQL .= " status = ".$_POST['filterBy'];
    }
    
    
// Count of all records
    
$query   $db->query("SELECT COUNT(*) as rowNum FROM users ".$whereSQL);
    
$result  $query->fetch_assoc();
    
$rowCount$result['rowNum'];
    
    
// Initialize pagination class
    
$pagConfig = array(
        
'baseURL' => $baseURL,
        
'totalRows' => $rowCount,
        
'perPage' => $limit,
        
'currentPage' => $offset,
        
'contentDiv' => 'dataContainer',
        
'link_func' => 'searchFilter'
    
);
    
$pagination =  new Pagination($pagConfig);

    
// Fetch records based on the offset and limit
    
$query $db->query("SELECT * FROM users $whereSQL 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
}
?>

PHP CRUD Operations with Search and Pagination

Conclusion

Here we have tried to make the Ajax pagination integration process simple by the Pagination library. Our Pagination class provides an easy way to implement Ajax pagination with search and filter using PHP and MySQL. You can create pagination links and add pagination functionality without page refresh using jQuery and Ajax in PHP. Also, the functionality of the Ajax Pagination library can be easily extended as per your needs.

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

13 Comments

  1. Dawid Said...
  2. Willcgg@mail.com Said...
  3. Senthil Kumar P Said...
  4. Brate Said...
  5. Remie Said...
  6. Rohit Kumar Said...
  7. Gabriel Said...
  8. Mark Said...
  9. Mark Said...
  10. Yusuf_ricky Said...
  11. Tyler Said...
    • CodexWorld Said...

Leave a reply

keyboard_double_arrow_up