Server Side Filtering using jQuery Ajax PHP and MySQL
"unknow" - 13/Jul/2016
"unknow" - 13/Jul/2016
[SHOWTOGROUPS=4,20]
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.
Database Table Creation
For this example script, we’ll create a simple table (users) with some basic columns where users data would be stored.
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.
[/SHOWTOGROUPS]
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.
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=utf8mb4 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;
}
}[/SHOWTOGROUPS]
