Server Side Filtering using jQuery Ajax PHP and MySQL


server-side-filtering-jquery-ajax-php-mysql-codexworld

Filtering helps the user to sort the recordset in the data list. Using the search and filter feature user can find the relevant data from the huge number of records list. In the web application, sorting or filtering is very useful where many data is listed. In this tutorial, we’ll show you how to implement server side filtering feature on data list using jQuery, Ajax, PHP, and MySQL.

Our example script provides a search and filter option on records list. A user can be able to search some particular records in the data list from 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 is used jQuery and Ajax to do this filtering functionality without page refresh.

In our example script, initially, we’ll fetch and display all the users data from the MySQL database with data search and sort option. Once user search by keywords, users list will be filter based on the keywords matched with name or email of the users. Using sort by dropdown, the user can filter the lists by a specific condition. The following actions would happen by sorting dropdown.

  • Newest: Users data would be fetched based on the created date of the user.
  • Ascending: Users data would be fetched as ascending order on the name of the user.
  • Descending: Users data would be fetched as descending order on the name of the user.
  • Active: Active users data would be fetched based on the status of the user.
  • Inactive: Blocked users data would be fetched based on the status of the user.

Before you begin, take a look at the simple files structure of Server Side Filter using PHP and MySQL.

server-side-filter-jquery-ajax-php-mysql-files-structure-codexworld

Database Table Creation

For this example script, we’ll create a simple table (users) with some basic columns where users data would be stored.

CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `phone` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
 `created` datetime NOT NULL,
 `modified` datetime NOT NULL,
 `status` enum('1','0') COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Database Class (DB.php)

DB class handles all the operations related to the database. For example, connect to the database and get records from the database. You need to change the $dbHost, $dbUsername, $dbPassword, and $dbName variable’s value as per the database credentials.

<?php
/*
 * DB Class
 * This class is used for database related (connect and fetch) operations
 * @author    CodexWorld.com
 * @url       http://www.codexworld.com
 * @license   http://www.codexworld.com/license
 */
class DB{
    private 
$dbHost     "localhost";
    private 
$dbUsername "root";
    private 
$dbPassword "";
    private 
$dbName     "codexworld";
    
    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 string name of the table
     * @param array select, where, search, order_by, limit and return_type conditions
     */
    
public function getRows($table,$conditions = array()){
        
$sql 'SELECT ';
        
$sql .= array_key_exists("select",$conditions)?$conditions['select']:'*';
        
$sql .= ' FROM '.$table;
        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)?'':' WHERE ';
            
$i 0;
            foreach(
$conditions['search'] as $key => $value){
                
$pre = ($i 0)?' OR ':'';
                
$sql .= $pre.$key." LIKE '%".$value."%'";
                
$i++;
            }
        }
        
        if(
array_key_exists("order_by",$conditions)){
            
$sql .= ' ORDER BY '.$conditions['order_by']; 
        }
        
        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;
    }
}

getData.php

In this file, users data is fetched from the database using DB class based on the search or filter condition. Based on the type and val, users data will get from the database and render the users list HTML. The following operations can happen based on the type.
search returns the users list based on search keywords.
sort returns the new or active or inactive or ascending order or descending order users list based on type val.

<?php
include 'DB.php';
$db = new DB();
$tblName 'users';
$conditions = array();
if(!empty(
$_POST['type']) && !empty($_POST['val'])){
    if(
$_POST['type'] == 'search'){
        
$conditions['search'] = array('name'=>$_POST['val'],'email'=>$_POST['val']);
        
$conditions['order_by'] = 'id DESC';
    }elseif(
$_POST['type'] == 'sort'){
        
$sortVal $_POST['val'];
        
$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];
    }
}else{
    
$conditions['order_by'] = 'id DESC';
}
$users $db->getRows($tblName,$conditions);
if(!empty(
$users)){
    
$count 0;
    foreach(
$users as $user): $count++;
        echo 
'<tr>';
        echo 
'<td>'.$user['name'].'</td>';
        echo 
'<td>'.$user['email'].'</td>';
        echo 
'<td>'.$user['phone'].'</td>';
        echo 
'<td>'.$user['created'].'</td>';
        
$status = ($user['status'] == 1)?'Active':'Inactive';
        echo 
'<td>'.$status.'</td>';
        echo 
'</tr>';
    endforeach;
}else{
    echo 
'<tr><td colspan="5">No user(s) found...</td></tr>';
}
exit;

index.php

This is the main view file and it is visible to the user. When this page is loaded for the first time, all the user data is listed with search and filter option. Once user request for the filter, Ajax is used to get the respective data from the getData.php file. For your better understanding, the index.php file is divided into two parts.

Bootstrap & JavaScript:
Server side filtering script is used jQuery and Ajax to do data filter without page refresh, so you need to include jQuery library. You can omit Bootstrap CSS & JS library if you don’t want to use bootstrap table structure.

<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>

getUsers() function is used to get the users data from the getData.php file using ajax and render the received HTML in the user data list. getUsers() function accepts two parameters, type and val. Based on these two parameters it will fetch the filtered users data.

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

HTML Code:
Initially, all the users data is fetched from the users table and listed with search, sort or filter options. On selecting the sort by filter value or clicking on the search button, getUsers() function is called.

<div class="container">
    <h2>Users Search & Filter by CodexWorld</h2>
    <div class="form-group pull-left">
        <input type="text" class="search form-control" id="searchInput" placeholder="By Name or Email">
        <input type="button" class="btn btn-primary" value="Search" onclick="getUsers('search',$('#searchInput').val())"/>
    </div>
    <div class="form-group pull-right">
        <select class="form-control" onchange="getUsers('sort',this.value)">
          <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 class="loading-overlay" style="display: none;"><div class="overlay-content">Loading.....</div></div>
    <table class="table table-striped">
        <thead>
            <tr>
                <th>Name</th>
                <th>Email</th>
                <th>Phone</th>
                <th>Created</th>
                <th>Status</th>
            </tr>
        </thead>
        <tbody id="userData">
            <?php
                
include 'DB.php';
                
$db = new DB();
                
$users $db->getRows('users',array('order_by'=>'id DESC'));
                if(!empty(
$users)){ $count 0;
                    foreach(
$users as $user){ $count++;
            
?> <tr> <td><?php echo $user['name']; ?></td> <td><?php echo $user['email']; ?></td> <td><?php echo $user['phone']; ?></td> <td><?php echo $user['created']; ?></td> <td><?php echo ($user['status'] == 1)?'Active':'Inactive'?></td> </tr>             <?php } }else{ ?> <tr><td colspan="5">No user(s) found...</td></tr>             <?php ?> </tbody> </table> </div>

CSS Code:
The following CSS is used to align the search box and design the loading overlay.

.form-control{float: left;width: 70%;}
.btn{float: left;margin-left: 5px;}
/* For Loading Overlay by CodexWorld */
.container{position: relative;}
.loading-overlay{
    position: absolute;
    left: 0; 
    top: 0; 
    right: 0; 
    bottom: 0;
    z-index: 2;
    background: rgba(255,255,255,0.7);
}
.overlay-content {
    position: absolute;
    transform: translateY(-50%);
     -webkit-transform: translateY(-50%);
     -ms-transform: translateY(-50%);
    top: 50%;
    left: 0;
    right: 0;
    text-align: center;
    color: #555;
}

2 Comments

  1. Kamlesh Kumar Said...
  2. Jeb Bush Said...

Leave a reply

Connect With CodexWorld