Example code of MySql Query that has Server side Sorting, Filteirng, and Paging

I just set things up for server side sorting, pagination and filtering using PHP and thought this might help you. This is basically my entire Controller.php file

You'll want to be careful of the $filter variable and the trailing " AND ". If you don't have an additional WHERE element after this, you'll need to remove the last " AND " or you will get an error. Also, in my case, payment_date is my default sortby field - you'll need to change this to suit your needs.

Hope this helps.

include_once("ConnectionManager.php");
header('Content-type:text/javascript;charset=UTF-8');

$json=json_decode(stripslashes($_POST["_gt_json"]));
$pageNo = $json->{'pageInfo'}->{'pageNum'};
$pageSize = $json->{'pageInfo'}->{'pageSize'};

// Uncomment this to get contents of filterInfo array if you need to debug
/*
if(count($json->{'filterInfo'})>0){
	print_r($json->{'filterInfo'});
}
*/

if(isset($json->{'sortInfo'}[0]->{'columnId'})){
	$sortField = $json->{'sortInfo'}[0]->{'columnId'};
}
else{
	$sortField = "payment_date";
}

if(isset($json->{'sortInfo'}[0]->{'sortOrder'})){
	$sortOrder = $json->{'sortInfo'}[0]->{'sortOrder'};
}
else{
	$sortOrder = "ASC";
}

if($json->{'sortInfo'}[0]->{'sortOrder'} == "defaultsort"){
	$sortField = "payment_date";
	$sortOrder = "ASC";
}

for ($i = 0; $i < count($json->{'filterInfo'}); $i++) {
	if($json->{'filterInfo'}[$i]->{'logic'} == "equal"){
        $filter .= $json->{'filterInfo'}[$i]->{'columnId'} . "='" . $json->{'filterInfo'}[$i]->{'value'} . "' ";
	}elseif($json->{'filterInfo'}[$i]->{'logic'} == "notEqual"){
        $filter .= $json->{'filterInfo'}[$i]->{'columnId'} . "!='" . $json->{'filterInfo'}[$i]->{'value'} . "' ";	
    }elseif($json->{'filterInfo'}[$i]->{'logic'} == "less"){
        $filter .= $json->{'filterInfo'}[$i]->{'columnId'} . "<" . $json->{'filterInfo'}[$i]->{'value'} . " ";
	}elseif($json->{'filterInfo'}[$i]->{'logic'} == "lessEqual"){
        $filter .= $json->{'filterInfo'}[$i]->{'columnId'} . "<=" . $json->{'filterInfo'}[$i]->{'value'} . " ";	
    }elseif($json->{'filterInfo'}[$i]->{'logic'} == "great"){
        $filter .= $json->{'filterInfo'}[$i]->{'columnId'} . ">" . $json->{'filterInfo'}[$i]->{'value'} . " ";
    }elseif($json->{'filterInfo'}[$i]->{'logic'} == "greatEqual"){
        $filter .= $json->{'filterInfo'}[$i]->{'columnId'} . ">=" . $json->{'filterInfo'}[$i]->{'value'} . " ";		
    }elseif($json->{'filterInfo'}[$i]->{'logic'} == "like"){
        $filter .= $json->{'filterInfo'}[$i]->{'columnId'} . " LIKE '%" . $json->{'filterInfo'}[$i]->{'value'} . "%' ";		
    }elseif($json->{'filterInfo'}[$i]->{'logic'} == "startWith"){
        $filter .= $json->{'filterInfo'}[$i]->{'columnId'} . " LIKE '" . $json->{'filterInfo'}[$i]->{'value'} . "%' ";		
    }elseif($json->{'filterInfo'}[$i]->{'logic'} == "endWith"){
        $filter .= $json->{'filterInfo'}[$i]->{'columnId'} . " LIKE '%" . $json->{'filterInfo'}[$i]->{'value'} . "' ";				
    }
	$filter .= " AND ";
}

$conManager = new ConManager();
$conManager->getConnection();

if($json->{'action'} == 'load'){

//to get how many total records.
  $sql = "select id from dbtable WHERE $filter payment_date <> ''";
  $handle = mysql_query($sql);
  $totalRec = mysql_num_rows($handle);

//make sure pageNo is inbound
  if($pageNo<1||$pageNo>ceil(($totalRec/$pageSize))){
    $pageNo = 1;
  }

$sql = "select * from dbtable WHERE $filter payment_date <> '' ORDER BY " . $sortField . " " . $sortOrder . " limit " . ($pageNo - 1)*$pageSize . ", " . $pageSize;
  $handle = mysql_query($sql);
		
  $retArray = array();
  while ($row = mysql_fetch_object($handle)) {
    $retArray[] = $row;
  }
  
  $data = json_encode($retArray);
  $ret = "{data:" . $data .",\n";
  $ret .= "pageInfo:{totalRowNum:" . $totalRec . "},\n";
  $ret .= "recordType : 'object'}";
  echo $ret;

}else if($json->{'action'} == 'save'){
  $sql = "";
  $params = array();
  $errors = "";
  
  //deal with those deleted
  $deletedRecords = $json->{'deletedRecords'};
  foreach ($deletedRecords as $value){
    $params[] = $value->id;
  }
  $sql = "delete from dbtable where id in (" . join(",", $params) . ")";
  if(mysql_query($sql)==FALSE){
    $errors .= mysql_error();
  }
  
  //deal with those updated
  $sql = "";
  $updatedRecords = $json->{'updatedRecords'};
  foreach ($updatedRecords as $value){
    $sql = "update `dbtable` set ".
//fill out fields to be updated here
      "where `id`=".$value->id;
      if(mysql_query($sql)==FALSE){
        $errors .= mysql_error();
      }
  }

//deal with those inserted
  $sql = "";
  $insertedRecords = $json->{'insertedRecords'};
  foreach ($insertedRecords as $value){
    $sql = "insert into dbtable (//fields to be inserted)";
    if(mysql_query($sql)==FALSE){
      $errors .= mysql_error();
    }
  }

$ret = "{success : true,exception:''}";
  echo $ret;
}