Ajax Pagination with Search and Filter in PHP

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

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 == 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 = '<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 + 1 ) . ' 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

View Demo Download Source Code

File Type (extension) Validation with JavaScript

Custom Scrollbars with CSS and Webkit

13 Comments

Dawid Said...

How can I add to this code export to pdf buttons?

This function doesn’t work with this 🙁
$(document).ready(function() {
$(‘#example’).DataTable( {
dom: ‘Bfrtip’,
buttons: [
‘copy’, ‘csv’, ‘excel’, ‘pdf’, ‘print’
]
} );
} );

Can You help with this maybe?
Thank You a lot!

October 20, 2023 at 11:10 PM
Willcgg@mail.com Said...

thanks mens you are godt 🙏

June 10, 2022 at 7:54 PM
Senthil Kumar P Said...

Hello Sir,

If I use the code same as yours, it is working great.

I just changed the query code as per my requirement

In Index.php
//Count of all records
$query=mysqli_query($conn,”SELECT COUNT(*) as rowNum FROM calibrationdata WHERE Branch = ‘$userbranch'”);

// Fetch records based on the offset and limit
$query=mysqli_query($conn,”SELECT * FROM calibrationdata WHERE Branch = ‘$userbranch’ ORDER BY id ASC LIMIT

I just add the $userbranch. If I add in index.php it is displaying table rows.

In getData.php

//Count of all records
$query=mysqli_query($conn,”SELECT COUNT(*) as rowNum FROM calibrationdata WHERE Branch = ‘$userbranch’ .$whereSQL”);

// Fetch records based on the offset and limit
$query=mysqli_query($conn,”SELECT * FROM calibrationdata WHERE Branch = ‘$userbranch’ $whereSQL ORDER BY id ASC LIMIT $offset,$limit”);

If I modify the code in getData.php, while searching or filtering the table I am getting the following error

Fatal error: Uncaught Error: Call to a member function fetch_assoc() on boolean in /storage/ssd1/112/16994112/public_html/chart_data.php:41 Stack trace: #0 {main} thrown in /storage/ssd1/112/16994112/public_html/chart_data.php on line 41

Can you please help me to solve this issue

April 17, 2022 at 7:41 PM
Brate Said...

Hello Sir,

Can you share me CSS and javascript file for this pagination project.
Thanking you,

with regards,
Brate

November 6, 2021 at 7:34 AM
Remie Said...

Great article! Love it.

Is there a back button available when using the pagination. Clicking on a link on page 2 for example and than going back brings up page1.

Thanks.

February 16, 2020 at 3:45 PM
Rohit Kumar Said...

Really a great article. i need help, how to add serial number on starting title.

October 14, 2019 at 7:10 AM
Gabriel Said...

Please is there a way one can display results only when one searches instead of displaying everything?

March 8, 2018 at 9:46 PM
Mark Said...

Thank you for the rapid response. I read through the https://www.codexworld.com/php-pagination-class-with-mysql/ tutorial and still cannot find the solution. Javascript is not my forte. I will continue to work on it.

October 24, 2017 at 7:34 PM
Mark Said...

Apologies . . . I should have posted it here as this was the set of scripts that I was working with:

Right now, I have the $row[‘title’] values showing up as paginated hyperlinked items in the browser. Very nice. They hyperlink only to a javascript void command as is determined in the scripts. I’d like to change it so that the hyperlink allows the visitor to view the record based on the record ID value ($row[‘id’].

I managed to change the value in the index.php script to make it work, but can’t seem to get it working in the Pagination.php script.

Thoughts?

October 24, 2017 at 6:23 PM

CodexWorld Said...

Use PHP pagination to fulfill your requirements. See this tutorial to implement Pagination in PHP with MySQL – https://www.codexworld.com/php-pagination-class-with-mysql/

October 24, 2017 at 6:53 PM

Yusuf_ricky Said...

Hallo master, you’re the best..

i was ask, ho to modify adde checkbox filter in this code ? thank before it…

July 18, 2017 at 6:45 AM
Tyler Said...

Really a great article. You should add

.loading-overlay {
display: none;
}

so that the loading text is not shown when the page is initially loaded.

May 12, 2017 at 8:22 PM

CodexWorld Said...

The CSS for .loading-overlay has included in the style.css file. You will get the style.css file and all the required files in the source code.

May 14, 2017 at 6:00 PM

Cancel

WEB DEVELOPMENT

Do you need a website for your business? We are here to help you out!
We develop cost-effective web applications with complete solutions. Build a new website or enhance your existing application at an affordable cost.

GET A FREE QUOTE

Ajax Pagination with Search and Filter in PHP

Pagination Library

Create Database Table

Database Configuration (dbConfig.php)

Data List with Ajax Pagination and Search (index.php)

Search and Get Pagination Data (getData.php)

Conclusion

RELATED TUTORIALS

13 Comments

Leave a reply

Cancel

WEB DEVELOPMENT

Do you need a website for your business? We are here to help you out!
We develop cost-effective web applications with complete solutions. Build a new website or enhance your existing application at an affordable cost.

TRENDING TUTORIALS

TOPICSall topics

LATEST HOW TO GUIDES

ABOUT US

Contact

Services

Extra Links

Ajax Pagination with Search and Filter in PHP

Pagination Library

Create Database Table

Database Configuration (dbConfig.php)

Data List with Ajax Pagination and Search (index.php)

Search and Get Pagination Data (getData.php)

Conclusion

RELATED TUTORIALS

13 Comments

Leave a reply

Cancel

WEB DEVELOPMENT

Do you need a website for your business? We are here to help you out!We develop cost-effective web applications with complete solutions. Build a new website or enhance your existing application at an affordable cost.

TRENDING TUTORIALS

TOPICSall topics

LATEST HOW TO GUIDES

Do you need a website for your business? We are here to help you out!
We develop cost-effective web applications with complete solutions. Build a new website or enhance your existing application at an affordable cost.