Ajax Pagination with Search and Filter in PHP


In our previous pagination tutorial, we have learned how to integrate Ajax pagination to the data list using jQuery, PHP, and MySQL. After publishing that tutorial, we’ve got many requests from our readers for adding the search feature on the Ajax pagination. In this tutorial, we’ll extend our previous Ajax pagination script with search and filter functionality. Before you get started with this tutorial, we suggest you take a look at our Ajax Pagination in PHP tutorial first.

Search is the most required feature on the data list and helps to make your web application user-friendly. Using search and filter, the user can sort the result set based on their requirement. Today you’ll learn how to add ajax pagination to the data list with search and filter in PHP.

In our example script, we’ll fetch the posts data from the database and the data will be listed with pagination links. Also, the search and filter option will be provided at top of the list.

The files structure of a simple Ajax pagination with search will like the below.

  • Pagination.php
  • dbConfig.php
  • getData.php
  • index.php
  • jquery.min.js
  • style.css

Database Creation

To store the posts data a table needs to be created in the database. The following SQL creates a posts table in the MySQL database.

CREATE TABLE `posts` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `content` text 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 helps to generate pagination links and get the data from the database using jQuery and Ajax. To add the search feature on Ajax pagination, we’ve made the following modification in Pagination class.

  • $link_func variable is added for accept a user-defined JavaScript function.
  • getAJAXlink() function is modified to insert the user defined function into the anchor of pagination links.
<?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 '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 ) . ' 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>';
    }
}
?>

Database Configuration (dbConfig.php)

The dbConfig.php file helps to connect and select the database. Specify your database host ($dbHost), username ($dbUsername), password ($dbPassword), and db name ($dbName).

<?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);
}
?>

index.php File

In this file, the initial posts data is listed with pagination links, search input, and filter dropdown. Using pagination links the user can get the posts 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.

JavaScript Code:
Ajax pagination with search uses jQuery, so, the jQuery library needs to be included first.

<script src="jquery.min.js"></script>

searchFilter() is JavaScript function, that collects the value from search input and filter drop-down and posts to the getData.php file. The response HTML is rendered under #posts_content div based on the search and filter terms.

<script>
function searchFilter(page_num) {
    page_num = page_num?page_num:0;
    var keywords = $('#keywords').val();
    var sortBy = $('#sortBy').val();
    $.ajax({
        type: 'POST',
        url: 'getData.php',
        data:'page='+page_num+'&keywords='+keywords+'&sortBy='+sortBy,
        beforeSend: function () {
            $('.loading-overlay').show();
        },
        success: function (html) {
            $('#posts_content').html(html);
            $('.loading-overlay').fadeOut("slow");
        }
    });
}
</script>

PHP & HTML Code:
Initially, specified number of posts is listed on index.php page load. To generate pagination links, you should need to create an object of the Pagination class. To use the search feature, specify the custom function (searchFilter) in the config array ($pagConfig). createLinks() function is used to display the pagination links.
searchFilter() function is called by the following events.

  • Click on the pagination links.
  • Typing on the search input.
  • On change the sort by drop down.
<div class="post-search-panel">
    <input type="text" id="keywords" placeholder="Type keywords to filter posts" onkeyup="searchFilter()"/>
    <select id="sortBy" onchange="searchFilter()">
        <option value="">Sort By</option>
        <option value="asc">Ascending</option>
        <option value="desc">Descending</option>
    </select>
</div>
<div class="post-wrapper">
    <div class="loading-overlay"><div class="overlay-content">Loading.....</div></div>
    <div id="posts_content">
    <?php
    
//Include pagination class file
    
include('Pagination.php');
    
    
//Include database configuration file
    
include('dbConfig.php');
    
    
$limit 10;
    
    
//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(
        
'totalRows' => $rowCount,
        
'perPage' => $limit,
        
'link_func' => 'searchFilter'
    
);
    
$pagination =  new Pagination($pagConfig);
    
    
//get rows
    
$query $db->query("SELECT * FROM posts ORDER BY id DESC LIMIT $limit");
    
    if(
$query->num_rows 0){ ?> <div class="posts_list">         <?php
            
while($row $query->fetch_assoc()){ 
                
$postID $row['id'];
        
?> <div class="list_item"><a href="javascript:void(0);"><h2><?php echo $row["title"]; ?></h2></a></div>         <?php ?> </div>         <?php echo $pagination->createLinks(); ?>
    <?php ?> </div> </div>

getData.php File

This page is requested by the Ajax of searchFilter() function which is placed in the index.php file. In the getData.php file, post data is fetched from the database based on the search query and filter data is returned to the Ajax.

<?php
if(isset($_POST['page'])){
    
//Include pagination class file
    
include('Pagination.php');
    
    
//Include database configuration file
    
include('dbConfig.php');
    
    
$start = !empty($_POST['page'])?$_POST['page']:0;
    
$limit 10;
    
    
//set conditions for search
    
$whereSQL $orderSQL '';
    
$keywords $_POST['keywords'];
    
$sortBy $_POST['sortBy'];
    if(!empty(
$keywords)){
        
$whereSQL "WHERE title LIKE '%".$keywords."%'";
    }
    if(!empty(
$sortBy)){
        
$orderSQL " ORDER BY id ".$sortBy;
    }else{
        
$orderSQL " ORDER BY id DESC ";
    }

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

    
//initialize pagination class
    
$pagConfig = array(
        
'currentPage' => $start,
        
'totalRows' => $rowCount,
        
'perPage' => $limit,
        
'link_func' => 'searchFilter'
    
);
    
$pagination =  new Pagination($pagConfig);
    
    
//get rows
    
$query $db->query("SELECT * FROM posts $whereSQL $orderSQL LIMIT $start,$limit");
    
    if(
$query->num_rows 0){ ?>
        <div class="posts_list">
        <?php
            
while($row $query->fetch_assoc()){ 
                
$postID $row['id'];
        
?>
            <div class="list_item"><a href="javascript:void(0);"><h2><?php echo $row["title"]; ?></h2></a></div>
        <?php ?>
        </div>
        <?php echo $pagination->createLinks(); ?>
<?php 
} } ?>

CSS (style.css)

The following CSS code is used to make pagination links look better.

div.pagination {
    font-family: "Lucida Sans", Geneva, Verdana, sans-serif;
    padding:20px;
    margin:7px;
}
div.pagination a {
    margin: 2px;
    padding: 0.5em 0.64em 0.43em 0.64em;
    background-color: #ee4e4e;
    text-decoration: none;
    color: #fff;
}
div.pagination a:hover, div.pagination a:active {
    padding: 0.5em 0.64em 0.43em 0.64em;
    margin: 2px;
    background-color: #de1818;
    color: #fff;
}
div.pagination span.current {
    padding: 0.5em 0.64em 0.43em 0.64em;
    margin: 2px;
    background-color: #f6efcc;
    color: #6d643c;
}
div.pagination span.disabled {
    display:none;
}

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

Recommended Tutorials For You

2 Comments

  1. Tyler Said...
    • CodexWorld Said...

Leave a reply