ExtJS has decided to include my PHP script in the example code released with their 2.1 API download. There are five data types that can be filtered, and each of them has their own specific options.

Grid Filter Plugin – Backend Code (in PHP)

The Plugin

 ExtJS has decided to include my PHP script in the example code released with their 2.1 API download.

The General Concept

There are five data types that can be filtered, and each of them has their own specific options.

• string
• list
• boolean
• numeric
• date

All the info needed to filter a column is passed by the filter grid plugin as a single 'filter' post variable that essentially is a multi-dimentional array.

Each filtered column passes three values, and in some cases a fourth.

• type
• value
• field
• comparison (only for date or numeric - ne, eq, gt, lt)

• This ends up making the post variable look something like this - with two filters.

  				 filter[0][data][type] : list
  	filter[0][data][value] : small,medium
  	filter[0][field] : size
  	filter[1][data][type] : boolean
  	filter[1][data][value] : true
  	filter[1][field] : visible
  	

  The predictable format of post variables makes it easy to code the server side PHP for this.

• The Code

  1. 
       
  2. $where = " 0 = 0 ";
       
  3. if (is_array($filter)) {
       
  4.     for ($i=0;$i<count($filter);$i++){
       
  5.         switch($filter[$i]['data']['type']){
       
  6.             case 'string' : $qs .= " AND ".$filter[$i]['field']." LIKE '%".$filter[$i]['data']['value']."%'"; Break;
       
  7.             case 'list' :
       
  8.                 if (strstr($filter[$i]['data']['value'],',')){
       
  9.                     $fi = explode(',',$filter[$i]['data']['value']);
       
  10.                     for ($q=0;$q<count($fi);$q++){
        
  11.                         $fi[$q] = "'".$fi[$q]."'";
        
  12.                     }
        
  13.                     $filter[$i]['data']['value'] = implode(',',$fi);
        
  14.                     $qs .= " AND ".$filter[$i]['field']." IN (".$filter[$i]['data']['value'].")";
        
  15.                 }else{
        
  16.                     $qs .= " AND ".$filter[$i]['field']." = '".$filter[$i]['data']['value']."'";
        
  17.                 }
        
  18.             Break;
        
  19.             case 'boolean' : $qs .= " AND ".$filter[$i]['field']." = ".($filter[$i]['data']['value']); Break;
        
  20.             case 'numeric' :
        
  21.                 switch ($filter[$i]['data']['comparison']) {
        
  22.                     case 'ne' : $qs .= " AND ".$filter[$i]['field']." != ".$filter[$i]['data']['value']; Break;
        
  23.                     case 'eq' : $qs .= " AND ".$filter[$i]['field']." = ".$filter[$i]['data']['value']; Break;
        
  24.                     case 'lt' : $qs .= " AND ".$filter[$i]['field']." < ".$filter[$i]['data']['value']; Break;
        
  25.                     case 'gt' : $qs .= " AND ".$filter[$i]['field']." > ".$filter[$i]['data']['value']; Break;
        
  26.                 }
        
  27.             Break;
        
  28.             case 'date' :
        
  29.                 switch ($filter[$i]['data']['comparison']) {
        
  30.                     case 'ne' : $qs .= " AND ".$filter[$i]['field']." != '".date('Y-m-d',strtotime($filter[$i]['data']['value']))."'"; Break;
        
  31.                     case 'eq' : $qs .= " AND ".$filter[$i]['field']." = '".date('Y-m-d',strtotime($filter[$i]['data']['value']))."'"; Break;
        
  32.                     case 'lt' : $qs .= " AND ".$filter[$i]['field']." < '".date('Y-m-d',strtotime($filter[$i]['data']['value']))."'"; Break;
        
  33.                     case 'gt' : $qs .= " AND ".$filter[$i]['field']." > '".date('Y-m-d',strtotime($filter[$i]['data']['value']))."'"; Break;
        
  34.                 }
        
  35.             Break;
        
  36.         }
        
  37.     }
        
  38.     $where .= $qs;
        
  39. }
        
  40. $query = "SELECT * FROM demo WHERE ".$where;
        
  
  $where = " 0 = 0 ";
  if (is_array($filter)) {
  for ($i=0;$i
  switch($filter[$i]['data']['type']){
  case 'string' : $qs .= " AND ".$filter[$i]['field']." LIKE '%".$filter[$i]['data']['value']."%'"; Break;
  case 'list' :
  if (strstr($filter[$i]['data']['value'],',')){
  $fi = explode(',',$filter[$i]['data']['value']);
  for ($q=0;$q
  $fi[$q] = "'".$fi[$q]."'";
  }
  $filter[$i]['data']['value'] = implode(',',$fi);
  $qs .= " AND ".$filter[$i]['field']." IN (".$filter[$i]['data']['value'].")";
  }else{
  $qs .= " AND ".$filter[$i]['field']." = '".$filter[$i]['data']['value']."'";
  }
  Break;
  case 'boolean' : $qs .= " AND ".$filter[$i]['field']." = ".($filter[$i]['data']['value']); Break;
  case 'numeric' :
  switch ($filter[$i]['data']['comparison']) {
  case 'ne' : $qs .= " AND ".$filter[$i]['field']." != ".$filter[$i]['data']['value']; Break;
  case 'eq' : $qs .= " AND ".$filter[$i]['field']." = ".$filter[$i]['data']['value']; Break;
  case 'lt' : $qs .= " AND ".$filter[$i]['field']." < ".$filter[$i]['data']['value']; Break;
  case 'gt' : $qs .= " AND ".$filter[$i]['field']." > ".$filter[$i]['data']['value']; Break;
  }
  Break;
  case 'date' :
  switch ($filter[$i]['data']['comparison']) {
  case 'ne' : $qs .= " AND ".$filter[$i]['field']." != '".date('Y-m-d',strtotime($filter[$i]['data']['value']))."'"; Break;
  case 'eq' : $qs .= " AND ".$filter[$i]['field']." = '".date('Y-m-d',strtotime($filter[$i]['data']['value']))."'"; Break;
  case 'lt' : $qs .= " AND ".$filter[$i]['field']." < '".date('Y-m-d',strtotime($filter[$i]['data']['value']))."'"; Break;
  case 'gt' : $qs .= " AND ".$filter[$i]['field']." > '".date('Y-m-d',strtotime($filter[$i]['data']['value']))."'"; Break;
  }
  Break;
  }
  }
  $where .= $qs;
  }
  $query = "SELECT * FROM demo WHERE ".$where;
  

  This simple bit of code is all we need on the server side to have a fully filterable set of data.