examples/grid-filtering/grid-filter.php

   1 <?php
   2 mysql_pconnect("localhost", "root", "") or die("Could not connect");
   3 mysql_select_db("demo") or die("Could not select database");
   4
   5 $start = ($_REQUEST["start"] == null)? 0 : $_REQUEST["start"];
   6 $count = ($_REQUEST["limit"] == null)? 20 : $_REQUEST["limit"];
   7 $sort = ($_REQUEST["sort"] == null)? "" : $_REQUEST["sort"];
   8 $dir = ($_REQUEST["dir"] == "desc")? "DESC" : "";
   9 $filter = $_REQUEST["filter"];
  10
  11 $where = " 0 = 0 ";
  12 if (is_array($filter)) {
  13         for ($i=0;$i<count($filter);$i++){
  14                 switch($filter[$i]['data']['type']){
  15                         case 'string' : $qs .= " AND ".$filter[$i]['field']." LIKE '%".$filter[$i]['data']['value']."%'"; Break;
  16                         case 'list' :
  17                                 if (strstr($filter[$i]['data']['value'],',')){
  18                                         $fi = explode(',',$filter[$i]['data']['value']);
  19                                         for ($q=0;$q<count($fi);$q++){
  20                                                 $fi[$q] = "'".$fi[$q]."'";
  21                                         }
  22                                         $filter[$i]['data']['value'] = implode(',',$fi);
  23                                         $qs .= " AND ".$filter[$i]['field']." IN (".$filter[$i]['data']['value'].")";
  24                                 }else{
  25                                         $qs .= " AND ".$filter[$i]['field']." = '".$filter[$i]['data']['value']."'";
  26                                 }
  27                         Break;
  28                         case 'boolean' : $qs .= " AND ".$filter[$i]['field']." = ".($filter[$i]['data']['value']); Break;
  29                         case 'numeric' :
  30                                 switch ($filter[$i]['data']['comparison']) {
  31                                         case 'eq' : $qs .= " AND ".$filter[$i]['field']." = ".$filter[$i]['data']['value']; Break;
  32                                         case 'lt' : $qs .= " AND ".$filter[$i]['field']." < ".$filter[$i]['data']['value']; Break;
  33                                         case 'gt' : $qs .= " AND ".$filter[$i]['field']." > ".$filter[$i]['data']['value']; Break;
  34                                 }
  35                         Break;
  36                         case 'date' :
  37                                 switch ($filter[$i]['data']['comparison']) {
  38                                         case 'eq' : $qs .= " AND ".$filter[$i]['field']." = '".date('Y-m-d',strtotime($filter[$i]['data']['value']))."'"; Break;
  39                                         case 'lt' : $qs .= " AND ".$filter[$i]['field']." < '".date('Y-m-d',strtotime($filter[$i]['data']['value']))."'"; Break;
  40                                         case 'gt' : $qs .= " AND ".$filter[$i]['field']." > '".date('Y-m-d',strtotime($filter[$i]['data']['value']))."'"; Break;
  41                                 }
  42                         Break;
  43                 }
  44         }
  45         $where .= $qs;
  46 }
  47
  48 $query = "SELECT * FROM demo WHERE ".$where;
  49 if ($sort != "") {
  50         $query .= " ORDER BY ".$sort." ".$dir;
  51 }
  52 $query .= " LIMIT ".$start.",".$count;
  53
  54 $rs = mysql_query($query);
  55 $total = mysql_query("SELECT COUNT(id) FROM demo WHERE ".$where);
  56 $total = mysql_result($total, 0, 0);
  57
  58 $arr = array();
  59 while($obj = mysql_fetch_object($rs)) {
  60         $arr[] = $obj;
  61 }
  62
  63 echo '{"total":"'.$total.'","data":'.json_encode($arr).'}';
  64 ?>