Server Filtering
Preface
There are 2 ways to enable filtering for grid.The first one is static filtering on client side.
To enable paging on client side it requires to add property
filter: {header: true} to columns.To enable server filtering it requires to do more actions.
Let us learn it.
Data
We assume that you have data base filled with sql from previous sample.
CREATE DATABASE `company`;
USE `company`;
CREATE TABLE `staff` (
`id` int(11) AUTO_INCREMENT,
`name` VARCHAR(50),
`surname` VARCHAR(50),
`country` VARCHAR(50),
`position` VARCHAR(50),
`email` VARCHAR(50),
`age` INT,
PRIMARY KEY (`id`)
);
INSERT INTO `staff` (`id`, `name`, `surname`, `country`, `position`, `email`, `age`) VALUES
(1, "Taylor", "Davis", "Netherlands", "DevOps", "[email protected]", 36),
(2, "Isabella", "Scott", "Australia", "PHP Developer", "[email protected]", 39),
(3, "Chloe", "Woods", "Sweden", "C++ Developer", "[email protected]", 23),
(4, "Ivan", "Richardson", "Singapore", "C++ Developer", "[email protected]", 28),
(5, "Ivan", "Brown", "Taiwan", "Data Science Engineer", "[email protected]", 24),
(6, "Ella", "Brown", "Sweden", "ASP.NET Developer", "[email protected]", 32),
(7, "Elizabeth", "Scott", "USA", "iOS Developer", "[email protected]", 43),
(8, "Peter", "Johnson", "Taiwan", "C++ Developer", "[email protected]", 42),
(9, "Randy", "Martin", "Netherlands", "C++ Developer", "[email protected]", 27),
(10, "Michael", "Phillips", "UK", "Software Tester", "[email protected]", 23),
(11, "Ed", "Brown", "San Marino", "Python Developer", "[email protected]", 33),
(12, "Luis", "Richardson", "Netherlands", "Software Tester", "[email protected]", 22),
(13, "Ed", "Johnson", "Australia", "ASP.NET Developer", "[email protected]", 44),
(14, "Gavin", "Garcia", "Belgium", "DevOps", "[email protected]", 26),
(15, "Taylor", "Howard", "Taiwan", "JavaScript Developer", "[email protected]", 42),
(16, "Orlando", "Scott", "Ireland", "Frontend Developer", "[email protected]", 26),
(17, "Chloe", "Taylor", "Finland", "Java Developer", "[email protected]", 21),
(18, "Jacob", "Hill", "Japan", "Python Developer", "[email protected]", 25),
(19, "Paula", "Scott", "Netherlands", "Data Science Engineer", "[email protected]", 23),
(20, "Lily", "Miller", "Austria", "JavaScript Developer", "[email protected]", 35);
Client side code
Let's prepare HTML and js code.We would need to add property
remoteFilter: true and add property filter: {header: true} to columns.
<html>
<head>
<link href="https://cdn.fancygrid.com/fancy.min.css" rel="stylesheet">
<script src="https://cdn.fancygrid.com/fancy.min.js"></script>
<script>
document.addEventListener("DOMContentLoaded", function() {
new FancyGrid({
title: 'Server Paging',
renderTo: 'container',
width: 550,
height: 500,
data: {
remoteFilter: true,
proxy: {
url: 'load.php'
}
},
selModel: 'cell',
cellTrackOver: true,
defaults: {
resizable: true,
sortable: true,
draggable: true,
filter: {
header: true,
tip: [
'Contains: value<br>',
'Equal: 30<br>',
'Not Equal: !=value<br>'
].join("")
}
},
columns: [{
index: 'name',
title: 'Name'
}, {
index: 'surname',
title: 'SurName'
}, {
index: 'country',
title: 'Country'
}, {
index: 'position',
title: 'Position'
}, {
index: 'email',
title: 'Email'
}]
});
});
</script>
</head>
<body>
<div id="container"></div>
</body>
</html>
Server side code
We will use 2 files.The first one is light class for connecting to DataBase.
DB.php
<?
class DB {
public $host = "localhost",
$dbuser = "user_name",
$dbpassword = "password",
$dbname = "company";
function __construct(){
$this->connect();
$this->mysql_select_db();
}
function connect(){
mysql_connect($this->host,$this->dbuser,$this->dbpassword) || die('could not connect to db');
}
function mysql_select_db(){
mysql_select_db($this->dbname) || die('could not select db');
}
}
?>
Very important moment is sending headers.
It requires to send
Content-Type that is equal to application/json.And some extra code special for filtering.
load.php
<?
header('Content-Type: application/json');
require_once("DB.php");
$db = new DB();
$filter = '';
if( isset($_GET['filter']) ){
$filter .= ' WHERE ';
$_filter = urldecode($_GET['filter']);
$_filter = json_decode($_filter);
for($i=0;$ioperator);
$value = $filterItem->value;
$property = $filterItem->property;
if($operator === 'LIKE'){
$filter .= "`" . $property . "` " . $operator . " '%" . $value . "%'";
}
else{
$filter .= "`" . $property . "` " . $operator . " '" . $value . "'";
}
if($i !== count($_filter) - 1 ){
$filter .= 'AND';
}
}
}
function getOperator($operator){
switch($operator){
case 'lt':
return '<';
break;
case 'gt':
return '>';
break;
case '<=':
return 'lteq';
break;
case '>=':
return 'gteq';
break;
case 'eq':
case 'stricteq':
return '=';
break;
break;
case 'noteq':
case 'notstricteq':
return '!=';
break;
case 'like':
return 'LIKE';
break;
}
}
$sql_query = "SELECT * FROM `staff`";
if( $filter != '' ){
$sql_query .= $filter . ' ';
}
$result_db_request = mysql_query($sql_query);
for($dataArr=array();$row=mysql_fetch_assoc($result_db_request);$dataArr[]=$row){}
$data = '[ ';
for($i = 0, $iL = count($dataArr); $i < $iL; $i++){
$data .= '{ ';
$row = $dataArr[$i];
$data .= '"id": '. $row[ 'id' ] . ',"name": "'. $row[ 'name' ] . '","surname": "' . $row[ 'surname' ] . '","position": "' . $row[ 'position' ] . '","country": "' . $row[ 'country' ] . '","email": "' . $row[ 'email' ] .'"';
$data .= '},';
}
$data = substr($data, 0, -1);
$data .= ']';
$responce .= '{"success":true,"\"data\"}';
echo $responce;
?>
Live Sample
Server FilteringMethod
By default method is'GET'.To change method it requires to set it for every requests type.
data: {
proxy: {
methods: {
create: 'POST',
read: 'POST',
update: 'POST',
destroy: 'POST'
},
...
}
}
Request Params
- filter - data filters