Forum

November 2nd, 2014
A A A
Avatar

Please consider registering
guest

sp_LogInOut Log In

Lost password?
Advanced Search

— Forum Scope —




— Match —





— Forum Options —





Minimum search word length is 4 characters - maximum search word length is 84 characters

The forums are currently locked and only available for read only access
sp_Feed Topic RSS sp_TopicIcon
PHP Code for building SQL Search Query (where clause)
17/08/2009
11:19
Avatar
paul
New Member
Forum Posts: 1
Member Since:
17/08/2009
sp_UserOfflineSmall Offline

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

18/08/2009
05:08
Avatar
waseem
Guest

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

24/03/2011
01:05
Avatar
marktees
New Member
Forum Posts: 1
Member Since:
24/03/2011
sp_UserOfflineSmall Offline

waseem said:

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


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
Forum Timezone: Europe/Sofia

Most Users Ever Online: 215

Currently Online:
29 Guest(s)

Currently Browsing this Page:
1 Guest(s)

Top Posters:

OlegK: 1255

markw65: 179

kobruleht: 144

phicarre: 132

YamilBracho: 124

Renso: 118

Member Stats:

Guest Posters: 447

Members: 11373

Moderators: 2

Admins: 1

Forum Stats:

Groups: 1

Forums: 8

Topics: 10592

Posts: 31289

Newest Members:

razia, Prankie, psky, praveen neelam, greg.valainis@pa-tech.com, swathi

Moderators: tony: 7721, Rumen[Trirand]: 81

Administrators: admin: 66

Comments are closed.
Privacy Policy   Terms and Conditions   Contact Information