Tutorials

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", "taylor.davis@fancygrid.com", 36),
(2, "Isabella", "Scott", "Australia", "PHP Developer", "isabella.scott@fancygrid.com", 39),
(3, "Chloe", "Woods", "Sweden", "C++ Developer", "chloe.woods@fancygrid.com", 23),
(4, "Ivan", "Richardson", "Singapore", "C++ Developer", "ivan.richardson@fancygrid.com", 28),
(5, "Ivan", "Brown", "Taiwan", "Data Science Engineer", "ivan.brown@fancygrid.com", 24),
(6, "Ella", "Brown", "Sweden", "ASP.NET Developer", "ella.brown@fancygrid.com", 32),
(7, "Elizabeth", "Scott", "USA", "iOS Developer", "elizabeth.scott@fancygrid.com", 43),
(8, "Peter", "Johnson", "Taiwan", "C++ Developer", "peter.johnson@fancygrid.com", 42),
(9, "Randy", "Martin", "Netherlands", "C++ Developer", "randy.martin@fancygrid.com", 27),
(10, "Michael", "Phillips", "UK", "Software Tester", "michael.phillips@fancygrid.com", 23),
(11, "Ed", "Brown", "San Marino", "Python Developer", "ed.brown@fancygrid.com", 33),
(12, "Luis", "Richardson", "Netherlands", "Software Tester", "luis.richardson@fancygrid.com", 22),
(13, "Ed", "Johnson", "Australia", "ASP.NET Developer", "ed.johnson@fancygrid.com", 44),
(14, "Gavin", "Garcia", "Belgium", "DevOps", "gavin.garcia@fancygrid.com", 26),
(15, "Taylor", "Howard", "Taiwan", "JavaScript Developer", "taylor.howard@fancygrid.com", 42),
(16, "Orlando", "Scott", "Ireland", "Frontend Developer", "orlando.scott@fancygrid.com", 26),
(17, "Chloe", "Taylor", "Finland", "Java Developer", "chloe.taylor@fancygrid.com", 21),
(18, "Jacob", "Hill", "Japan", "Python Developer", "jacob.hill@fancygrid.com", 25),
(19, "Paula", "Scott", "Netherlands", "Data Science Engineer", "paula.scott@fancygrid.com", 23),
(20, "Lily", "Miller", "Austria", "JavaScript Developer", "lily.miller@fancygrid.com", 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');
  }
}
?>
The second file is reading data from DataBase.
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 Filtering

Method

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