Server Side Filtering using jQuery Ajax PHP and MySQL

Filtering feature helps to sort the recordset in the data list. Using the search and filter feature, the user can easily find the relevant data from the huge number of records. The sorting or filtering functionality is very useful in the data management section of the web application. The server-side search and filter functionality can be integrated easily with PHP and MySQL.

The jQuery and Ajax can be used to implement the search functionality without page refresh. It helps to make the search and filter feature user-friendly. In this tutorial, we’ll show you how to implement the search and filter feature with the server-side data using jQuery, Ajax, PHP, and MySQL.

This example script provides a search and filter option on the record list. A user can be able to search some particular records in the data list from the MySQL database or filter records by the specific type (like newest records, records by ascending and descending order, records by status). This server-side search and filter in PHP are used jQuery and Ajax to do this filtering functionality without page refresh.

The following functionality will be implemented to build server-side filtering script with PHP and MySQL.

• Fetch all the records from the MySQL database and list with the search and sort option.
• When the user searches by keywords, filter and fetch the matched records.
• When the user sort records, filter the records by the selected option.

Before getting started to integrate server-side filter using PHP, take a look at the file structure.

server_side_filter_jquery_ajax_php_mysql/
├── config.php
├── index.php
├── getData.php
├── User.class.php
├── js/
│   └── jquery.min.js
└── css/
    └── style.css

Create Database Table

To store the data, a table is required in the database. The following SQL creates a members table with some basic fields in the MySQL database.

CREATE TABLE `members` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `gender` enum('Male','Female') COLLATE utf8_unicode_ci NOT NULL,
 `country` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `created` 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 (config.php)

The database configuration variables are defined in this file.

• DB_HOST – Database host
• DB_USERNAME – Database username
• DB_PASSWORD – Database password
• DB_NAME – Database name

<?php 
// Database configuration 
define('DB_HOST', 'MySQL_Database_Host'); 
define('DB_USERNAME', 'MySQL_Database_Username'); 
define('DB_PASSWORD', 'MySQL_Database_Password'); 
define('DB_NAME', 'MySQL_Database_Name');

User Class (User.class.php)

The User class help to connect with the database and handles the database related operations using PHP and MySQL.

• __construct() – Connect with the MySQL database.
• getRows() – Fetch the records from the database.
  • $conditions – If specified, it filters records based on the mentioned conditions.

<?php 
/* 
 * User Class 
 * This class is used for database related operations (connect and fetch) 
 * @author    CodexWorld.com 
 * @url       http://www.codexworld.com 
 * @license   http://www.codexworld.com/license 
 */ 
 
class User{ 
    private $dbHost     = DB_HOST; 
    private $dbUsername = DB_USERNAME; 
    private $dbPassword = DB_PASSWORD; 
    private $dbName     = DB_NAME; 
    private $tblName    = 'members'; 
     
    public function __construct(){ 
        if(!isset($this->db)){ 
            // Connect to the database 
            $conn = new mysqli($this->dbHost, $this->dbUsername, $this->dbPassword, $this->dbName); 
            if($conn->connect_error){ 
                die("Failed to connect with MySQL: " . $conn->connect_error); 
            }else{ 
                $this->db = $conn; 
            } 
        } 
    } 
     
    /* 
     * Returns rows from the database based on the conditions 
     * @param array select, where, search, order_by, limit and return_type conditions 
     */ 
    public function getRows($conditions = array()){ 
        $sql = 'SELECT '; 
        $sql .= array_key_exists("select",$conditions)?$conditions['select']:'*'; 
        $sql .= ' FROM '.$this->tblName; 
        if(array_key_exists("where",$conditions)){ 
            $sql .= ' WHERE '; 
            $i = 0; 
            foreach($conditions['where'] as $key => $value){ 
                $pre = ($i > 0)?' AND ':''; 
                $sql .= $pre.$key." = '".$value."'"; 
                $i++; 
            } 
        } 
         
        if(array_key_exists("search",$conditions)){ 
            $sql .= (strpos($sql, 'WHERE') !== false)?' AND ':' WHERE '; 
            $i = 0; 
            $sql_src = ''; 
            foreach($conditions['search'] as $key => $value){ 
                $pre = ($i > 0)?' OR ':''; 
                $sql_src .= $pre.$key." LIKE '%".$value."%'"; 
                $i++; 
            } 
             
            $sql .= !empty($sql_src)?' ('.$sql_src.') ':''; 
        } 
         
        if(array_key_exists("order_by",$conditions)){ 
            $sql .= ' ORDER BY '.$conditions['order_by'];  
        }else{ 
            $sql .= ' ORDER BY id DESC ';  
        } 
         
        if(array_key_exists("start",$conditions) && array_key_exists("limit",$conditions)){ 
            $sql .= ' LIMIT '.$conditions['start'].','.$conditions['limit'];  
        }elseif(!array_key_exists("start",$conditions) && array_key_exists("limit",$conditions)){ 
            $sql .= ' LIMIT '.$conditions['limit'];  
        } 
         
        $result = $this->db->query($sql); 
         
        if(array_key_exists("return_type",$conditions) && $conditions['return_type'] != 'all'){ 
            switch($conditions['return_type']){ 
                case 'count': 
                    $data = $result->num_rows; 
                    break; 
                case 'single': 
                    $data = $result->fetch_assoc(); 
                    break; 
                default: 
                    $data = ''; 
            } 
        }else{ 
            if($result->num_rows > 0){ 
                while($row = $result->fetch_assoc()){ 
                    $data[] = $row; 
                } 
            } 
        } 
        return !empty($data)?$data:false; 
    } 
}

Data List with Search and Filter (index.php)

Initially, all the member’s data is fetched from the database and listing in a tabular format with search and filter option.

• Search Option: When the user types the keywords and submit search request, an Ajax request is initiated to send input to the server-side script (getData.php) for further processing.
• Filter Option: When the user selects an option from the dropdown, an Ajax request is sent to the server-side script (getData.php) to sort records by the following actions.
  • Newest – The records are fetched based on the created date of the member.
  • Ascending – The records are fetched in ascending order of the member’s name.
  • Descending – The records are fetched in descending order of the member’s name.
  • Active – The records are fetched based on the status of the member.
  • Inactive – The records are fetched based on the status of the member.

Bootstrap Library:
The bootstrap library is used to design the data list table structure. If you don’t want to use Bootstrap, omit to include the Bootstrap CSS file.

<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">

jQuery Library:
Include the jQuery library, it helps to use Ajax to implement the search and filter functionality without page refresh.

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

JavaScript Code:
The searchFilter() function is used to initiate the Ajax request.

• Post the search input and option to the server-side script (getData.php) via jQuery Ajax.
• Render response HTML on the web page.

function searchFilter(){
    $.ajax({
        type: 'POST',
        url: 'getData.php',
        data: 'keywords='+$('#searchInput').val()+'&filter='+$('#filterSelect').val(),
        beforeSend: function(){
            $('.loading-overlay').show();
        },
        success:function(html){
            $('.loading-overlay').hide();
            $('#userData').html(html);
        }
    });
}

PHP & HTML Code:
List all the members data in an HTML table with search input and filter dropdown.

• Fetch all the members data from the database using User class
• The JavaScript searchFilter() function is called by two events:
  • Search button click.
  • Sort dropdown change.

<?php 
// Include configuration file 
require_once 'config.php'; 
 
// Include User class 
require_once 'User.class.php'; 
 
// Initialize User class 
$user = new User(); 
 
// Get members data from database 
$members = $user->getRows(); 
?>

<div class="search-panel">
    <div class="input-group">
        <input type="text" class="search form-control" id="searchInput" placeholder="By Name or Email">
        <div class="input-group-append">
            <button class="btn btn-outline-secondary" type="button" onclick="searchFilter();">Search</button>
        </div>
    </div>
    <div class="form-group">
        <select class="form-control" id="filterSelect" onchange="searchFilter();">
            <option value="">Sort By</option>
            <option value="new">Newest</option>
            <option value="asc">Ascending</option>
            <option value="desc">Descending</option>
            <option value="active">Active</option>
            <option value="inactive">Inactive</option>
        </select>
    </div>
</div>

<div class="main-content">
    <div class="loading-overlay" style="display: none;"><div class="overlay-content">Loading.....</div></div>
    <table class="table table-striped">
        <thead>
            <tr>
                <th>#</th>
                <th>Name</th>
                <th>Email</th>
                <th>Gender</th>
                <th>Country</th>
                <th>Created</th>
                <th>Status</th>
            </tr>
        </thead>
        <tbody id="userData">
            <?php 
            if(!empty($members)){ $i = 0; 
                foreach($members as $row){ $i++; 
            ?>
            <tr>
                <td><?php echo $i; ?></td>
                <td><?php echo $row['name']; ?></td>
                <td><?php echo $row['email']; ?></td>
                <td><?php echo $row['gender']; ?></td>
                <td><?php echo $row['country']; ?></td>
                <td><?php echo $row['created']; ?></td>
                <td><?php echo ($row['status'] == 1)?'Active':'Inactive'; ?></td>
            </tr>
            <?php } }else{ ?>
            <tr><td colspan="7">No member(s) found...</td></tr>
            <?php } ?>
        </tbody>
    </table>
</div>

Search and Filter Data (getData.php)

The getData.php file is loaded by the Ajax request and used to handles the search and filter operations.

• Retrieve the keywords and sort option from the Ajax request using the PHP $_POST method.
• Get the filtered member data using the getRows() function of the User class.
• Generate HTML of the member’s data rows.
• Render table row HTML with filtered records.

<?php 
// Include configuration file 
require_once 'config.php'; 
 
// Include User class 
require_once 'User.class.php'; 
 
// Initialize User class 
$user = new User(); 
 
// Define filters 
$conditions = array(); 
 
// If search request is submitted 
if(!empty($_POST['keywords'])){ 
    $conditions['search'] = array('name' => $_POST['keywords'], 'email' => $_POST['keywords']); 
} 
 
// If filter request is submitted 
if(!empty($_POST['filter'])){ 
    $sortVal = $_POST['filter']; 
    $sortArr = array( 
        'new' => array( 
            'order_by' => 'created DESC' 
        ), 
        'asc'=>array( 
            'order_by'=>'name ASC' 
        ), 
        'desc'=>array( 
            'order_by'=>'name DESC' 
        ), 
        'active'=>array( 
            'where'=>array('status' => 1) 
        ), 
        'inactive'=>array( 
            'where'=>array('status' => 0) 
        ) 
    ); 
    $sortKey = key($sortArr[$sortVal]); 
    $conditions[$sortKey] = $sortArr[$sortVal][$sortKey]; 
} 
 
// Get members data based on search and filter 
$members = $user->getRows($conditions); 
 
if(!empty($members)){ 
    $i = 0; 
    foreach($members as $row){ $i++; 
        echo '<tr>'; 
        echo '<td>'.$i.'</td>'; 
        echo '<td>'.$row['name'].'</td>'; 
        echo '<td>'.$row['email'].'</td>'; 
        echo '<td>'.$row['gender'].'</td>'; 
        echo '<td>'.$row['country'].'</td>'; 
        echo '<td>'.$row['created'].'</td>'; 
        $status = ($row['status'] == 1)?'Active':'Inactive'; 
        echo '<td>'.$status.'</td>'; 
        echo '</tr>'; 
    } 
}else{ 
    echo '<tr><td colspan="7">No members(s) found...</td></tr>'; 
} 
exit;

Ajax Pagination with Search and Filter in PHP

Conclusion

This example code snippet will help you to add search and filter functionality to the data list in your web application. The user can easily sort the recordset without page refresh. You can enhance the functionality of this server-side filtering script as per your needs.