1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
|
<?php
// database connection settings
$server = 'localhost';
$username = 'demo';
$password = '';
$database = 'demo'; // 'gridfiltersdemo' in sample sql
// connect to database
mysql_pconnect($server, $username, $password) or die("Could not connect");
mysql_select_db($database) or die("Could not select database");
// collect request parameters
$start = isset($_REQUEST['start']) ? $_REQUEST['start'] : 0;
$count = isset($_REQUEST['limit']) ? $_REQUEST['limit'] : 20;
$sort = isset($_REQUEST['sort']) ? $_REQUEST['sort'] : '';
$dir = isset($_REQUEST['dir']) ? $_REQUEST['dir'] : 'ASC';
$filters = isset($_REQUEST['filter']) ? $_REQUEST['filter'] : null;
// GridFilters sends filters as an Array if not json encoded
if (is_array($filters)) {
$encoded = false;
} else {
$encoded = true;
$filters = json_decode($filters);
}
// initialize variables
$where = ' 0 = 0 ';
$qs = '';
// loop through filters sent by client
if (is_array($filters)) {
for ($i=0;$i<count($filters);$i++){
$filter = $filters[$i];
// assign filter data (location depends if encoded or not)
if ($encoded) {
$field = $filter->field;
$value = $filter->value;
$compare = isset($filter->comparison) ? $filter->comparison : null;
$filterType = $filter->type;
} else {
$field = $filter['field'];
$value = $filter['data']['value'];
$compare = isset($filter['data']['comparison']) ? $filter['data']['comparison'] : null;
$filterType = $filter['data']['type'];
}
switch($filterType){
case 'string' : $qs .= " AND ".$field." LIKE '%".$value."%'"; Break;
case 'list' :
if (strstr($value,',')){
$fi = explode(',',$value);
for ($q=0;$q<count($fi);$q++){
$fi[$q] = "'".$fi[$q]."'";
}
$value = implode(',',$fi);
$qs .= " AND ".$field." IN (".$value.")";
}else{
$qs .= " AND ".$field." = '".$value."'";
}
Break;
case 'boolean' : $qs .= " AND ".$field." = ".($value); Break;
case 'numeric' :
switch ($compare) {
case 'eq' : $qs .= " AND ".$field." = ".$value; Break;
case 'lt' : $qs .= " AND ".$field." < ".$value; Break;
case 'gt' : $qs .= " AND ".$field." > ".$value; Break;
}
Break;
case 'date' :
switch ($compare) {
case 'eq' : $qs .= " AND ".$field." = '".date('Y-m-d',strtotime($value))."'"; Break;
case 'lt' : $qs .= " AND ".$field." < '".date('Y-m-d',strtotime($value))."'"; Break;
case 'gt' : $qs .= " AND ".$field." > '".date('Y-m-d',strtotime($value))."'"; Break;
}
Break;
}
}
$where .= $qs;
}
// query the database
$query = "SELECT * FROM demo WHERE ".$where;
if ($sort != "") {
$query .= " ORDER BY ".$sort." ".$dir;
}
$query .= " LIMIT ".$start.",".$count;
$rs = mysql_query($query);
$total = mysql_query("SELECT COUNT(id) FROM demo WHERE ".$where);
$total = mysql_result($total, 0, 0);
$arr = array();
while($obj = mysql_fetch_object($rs)) {
$arr[] = $obj;
}
// return response to client
echo '{"total":"'.$total.'","data":'.json_encode($arr).'}';
|
