Guriddo » Topic: PHP Code for building SQL Search Query (where clause)

PHP Code for building SQL Search Query (where clause)

Guriddo
PHP Code for building SQL Search Query (where clause)

This topic has 2 replies, 3 voices, and was last updated 15 years, 1 month ago by marktees.

Creator

Topic

August 17, 2009 at 11:19 am #73949

paul

Participant

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

$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[&#39;searchField&#39;]) ? $_GET[&#39;searchField&#39;] : false;<br />
$searchOper = isset($_GET[&#39;searchOper&#39;]) ? $_GET[&#39;searchOper&#39;]: false;<br />
$searchString = isset($_GET[&#39;searchString&#39;]) ? $_GET[&#39;searchString&#39;] : false;<br />

$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

Creator

Topic

Viewing 2 replies - 1 through 2 (of 2 total)

Author

Replies
August 18, 2009 at 5:08 am #88298

admin
Keymaster

Great Dude nice function but before that how to post the values from jqgrid? plz m stuK?????

March 24, 2011 at 1:05 am #97628

marktees
Participant

waseem said:

Great Dude nice function but before that how to post the values from jqgrid? plz m stuK?????

Hey, make sure you are using something like Firebug so you can get a good view of whats happening.

By default as soon as I had the pager there and I could see the search button when I clicked find (with some parameters) it POSTED like this:

_search true

nd 1300921202001

page 1

rows 10

searchField id

searchOper eq

searchString 8

sidx id

sord

desc
Author

Replies