Ajax Pagination with Search and Filter in PHP

Pagination Library

Pagination can be easily integrated into a data list using jQuery, Ajax, PHP, and MySQL without page refresh. Ajax pagination is the most elegant option to make the data list user-friendly. In the first tutorial, we have shown how to integrate Ajax pagination with PHP in a web application. Now, we will increase Ajax Paging Script functionality with search and filter features.

Search is the most beneficial feature of the data management section. It helps to filter data from a large number of records quickly. 

The user can find the same data and sort the results set by using the search functionality. In this tutorial, we will explain to you how to unify Ajax pagination with search and filter using PHP, jQuery, and MySQL.

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

Before proceeding to build Ajax pagination and search in PHP, have 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/
    └── style.css

Pagination Library

Our custom pagination library helps you integrate Ajax pagination with search functionality using PHP. Ajax Pagination creates links to control the paging of the data list. The following minimum configuration is required to associate Ajax pagination with a search in the data list.

<?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        Allsweb
 * @link        http://www.Allsweb.com 
 * @contact        http://www.Allsweb.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>'; 
    } 
} 
?>

Make a Database Table

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

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;

Database Configuration (dbConfig.php)

There is a need to use the dbConfig.php file to add and select databases. Specify the database username ($ dbUsername), host ($ dbHost), password ($ dbPassword), and name ($ dbName) according to MySQL database server credentials.

<?php 
// Database configuration 
$dbHost     = "localhost"; 
$dbUsername = "root"; 
$dbPassword = "root"; 
$dbName     = "allsweb"; 
 
// 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)

In this tutorial, There is a list of the initial posts data with pagination links, search input, and filter dropdown. Using pagination links, the user can quickly get the posts data from the MySQL database without page refresh using jQuery and Ajax. Therefore, the user can filter the post data using the search box and filter dropdown.

jQuery Library:

Include the jQuery library for the Ajax pagination.

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

Loading Image:

It is used to show/hide loading overlay when ajax request starts/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>

Search Function:

SearchFilter () is a custom JavaScript function that handles search and filter functionality using jQuery

<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) {
            $('#postContent').html(html);
            $('.loading-overlay').fadeOut("slow");
        }
    });
}
</script>

Search Form:

Describe the elements of HTML for the search input and sort by filter select box.

<div class="post-search-panel">
    <input type="text" id="keywords" placeholder="Type keywords..." onkeyup="searchFilter();"/>
    <select id="sortBy" onchange="searchFilter();">
        <option value="">Sort by Title</option>
        <option value="asc">Ascending</option>
        <option value="desc">Descending</option>
    </select>
</div>

Add Pagination:

At first, retrieve a specific number of records from the database and listed with an Ajax pagination link. When you click a paging link and initiate Ajax request and sent to a server-side script.

<div class="post-wrapper">
    <!-- Loading overlay -->
    <div class="loading-overlay"><div class="overlay-content">Loading...</div></div>
	
    <!-- Post list container -->
    <div id="postContent">
        <?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 posts"); 
        $result  = $query->fetch_assoc(); 
        $rowCount= $result['rowNum']; 
         
        // Initialize pagination class 
        $pagConfig = array( 
            'baseURL' => $baseURL, 
            'totalRows' => $rowCount, 
            'perPage' => $limit, 
            'contentDiv' => 'postContent', 
            'link_func' => 'searchFilter' 
        ); 
        $pagination =  new Pagination($pagConfig); 
         
        // Fetch records based on the limit 
        $query = $db->query("SELECT * FROM posts ORDER BY id DESC LIMIT $limit"); 
         
        if($query->num_rows > 0){ 
        ?>
            <!-- Display posts list -->
            <div class="post-list">
            <?php while($row = $query->fetch_assoc()){ ?>
                <div class="list-item"><a href="#"><?php echo $row["title"]; ?></a></div>
            <?php } ?>
            </div>
			
            <!-- Display pagination links -->
            <?php echo $pagination->createLinks(); ?>
        <?php 
        }else{ 
            echo '<p>Post(s) not found...</p>'; 
        } 
        ?>
    </div>
</div>
Search and Get Pagination Data (getData.php)

You can load the getData.php file by Ajax request (called from searchFilter() function) to retrieve the records from the database.

<?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 = $orderSQL = ''; 
    if(!empty($_POST['keywords'])){ 
        $whereSQL = "WHERE title LIKE '%".$_POST['keywords']."%'"; 
    } 
    if(!empty($_POST['sortBy'])){ 
        $orderSQL = " ORDER BY title ".$_POST['sortBy']; 
    }else{ 
        $orderSQL = " ORDER BY title DESC "; 
    } 
     
    // Count of all records 
    $query   = $db->query("SELECT COUNT(*) as rowNum FROM posts ".$whereSQL.$orderSQL); 
    $result  = $query->fetch_assoc(); 
    $rowCount= $result['rowNum']; 
     
    // Initialize pagination class 
    $pagConfig = array( 
        'baseURL' => $baseURL, 
        'totalRows' => $rowCount, 
        'perPage' => $limit, 
        'currentPage' => $offset, 
        'contentDiv' => 'postContent', 
        'link_func' => 'searchFilter' 
    ); 
    $pagination =  new Pagination($pagConfig); 
 
    // Fetch records based on the offset and limit 
    $query = $db->query("SELECT * FROM posts $whereSQL $orderSQL LIMIT $offset,$limit"); 
     
    if($query->num_rows > 0){ 
    ?> 
        <!-- Display posts list --> 
        <div class="post-list"> 
        <?php while($row = $query->fetch_assoc()){ ?> 
            <div class="list-item"><a href="#"><?php echo $row["title"]; ?></a></div> 
        <?php } ?> 
        </div> 
         
        <!-- Display pagination links --> 
        <?php echo $pagination->createLinks(); ?> 
<?php 
    }else{ 
        echo '<p>Post(s) not found...</p>'; 
    } 
} 
?>

Conclusion

Firstly, we have tried to simplify the Ajax pagination integration process by the pagination library. Secondly, our pagination class gives you an easy way to implement Ajax pagination with search and filter in PHP using MySQL. Moreover, you can create paging links and add paging functionality without refreshing the page using jQuery, Ajax, and PHP. Besides, You can easily increase the functionality of Ajax Pagination Library to suit your needs.

Also, read our previous blog- Integrate new Google reCAPTCHA Checkbox with PHP

 

Exit mobile version