jQuery Grid Plugin - jqGrid

Here's a code snippet for PHP for building SQL an search query (where clause) from the jqGRID params.

I'm not sure if this is the best/most efficient way to do this, but since I couldn't find code for doing this in the documentation anywhere, I'm posting what I'm doing.

I keep an array of the operators sent from jqGrid, and use a function to return the WHERE clause associated with it. I'm assuming that 'contains' and 'is in' mean the same thing, which is probably not what Tony meant. It's easy to change the mapping though, so feel free to do so.

$ops = array(
    'eq'=>'=', //equal
    'ne'=>'<>',//not equal
    'lt'=>'<', //less than
    'le'=>'<=',//less than or equal
    'gt'=>'>', //greater than
    'ge'=>'>=',//greater than or equal
    'bw'=>'LIKE', //begins with
    'bn'=>'NOT LIKE', //doesn't begin with
    'in'=>'LIKE', //is in
    'ni'=>'NOT LIKE', //is not in
    'ew'=>'LIKE', //ends with
    'en'=>'NOT LIKE', //doesn't end with
    'cn'=>'LIKE', // contains
    'nc'=>'NOT LIKE'  //doesn't contain
);   
/**
* Returns SQL WHERE clause
* @param string $col sql column name
* @param string $oper operator from jqGrid
* @param string $val value (right hand side)
*/
function getWhereClause($col, $oper, $val){
    global $ops;    
    if($oper == 'bw' || $oper == 'bn') $val .= '%';
    if($oper == 'ew' || $oper == 'en' ) $val = '%'.$val;
    if($oper == 'cn' || $oper == 'nc' || $oper == 'in' || $oper == 'ni') $val = '%'.$val.'%';
    return " AND $col {$ops[$oper]} '$val' ";
}

Obviously, you'll need to get the search params from the $_GET array. I do something like this:

$searchField = isset($_GET['searchField']) ? $_GET['searchField'] : false; 
$searchOper = isset($_GET['searchOper']) ? $_GET['searchOper']: false;  
$searchString = isset($_GET['searchString']) ? $_GET['searchString'] : false;

Then the query is simply: “SELECT * FROM $searchField “. getWhereClause($searchField,$searchOper,$searchString);

-paul