How to enable the search functionality in jqGrid?

jqGrid is a powerful jQuery based, AJAX tool for representing and editing tabular data in many formats (database output in JSON, XML etc.).

It is really easy to implement, quite well documented and really easy to style (through jQuery UI themes and its excellent themeroller).

I was working on a Joomla-PHP-MySQL project. I have successfully enabled the extended show functionality, adding edit option also went smoothly. However, I got a little bit stuck while trying to enable Search. Basing on demo examples and some blogs, I have managed to develop a piece of code that successfully made the search work like a charm. Below you’d find a php file responsible for returning the get xml data request from jqgrid.

<?php
$page = $_GET['page']; // get the requested page
$limit = $_GET['rows']; // get how many rows we want to have into the grid
$sidx = $_GET['sidx']; // get index row - i.e. user click to sort
$sord = $_GET['sord']; // get the direction
if(!$sidx) $sidx =1;

//array to translate the search type
$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
);
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 " WHERE $col {$ops[$oper]} '$val' ";
}
$where = ""; //if there is no search request sent by jqgrid, $where should be empty
$searchField = isset($_GET['searchField']) ? $_GET['searchField'] : false;
$searchOper = isset($_GET['searchOper']) ? $_GET['searchOper']: false;
$searchString = isset($_GET['searchString']) ? $_GET['searchString'] : false;
if ($_GET['_search'] == 'true') {
    $where = getWhereClause($searchField,$searchOper,$searchString);
}

// connect to the database
$dbhost = "host_address";
$dbuser = "db_user";
$dbpassword = "db_pass";
$database = "db_name";
$tablename
$db = mysql_connect($dbhost, $dbuser, $dbpassword)
or die("Connection Error: " . mysql_error());

mysql_select_db($database) or die("Error conecting to db.");
mysql_set_charset('utf8',$database);
mysql_query("SET NAMES 'utf8'");
$result = mysql_query("SELECT COUNT(*) AS count FROM $tablename");
$row = mysql_fetch_array($result,MYSQL_ASSOC);
$count = $row['count'];

if( $count >0 ) {
	$total_pages = ceil($count/$limit);
} else {
	$total_pages = 0;
}
if ($page > $total_pages) $page=$total_pages;
$start = $limit*$page - $limit; // do not put $limit*($page - 1)
$SQL = "SELECT field1, field2, field3, field4, field5 FROM $tablename "
.$where." ORDER BY $sidx $sord LIMIT $start , $limit";
$result = mysql_query( $SQL ) or die("Couldn?t execute query.".mysql_error());

if ( stristr($_SERVER["HTTP_ACCEPT"],"application/xhtml+xml") ) {
header("Content-type: application/xhtml+xml;charset=utf-8"); } else {
header("Content-type: text/xml;charset=utf-8");
}
$et = ">";

echo "<?xml version='1.0' encoding='utf-8'?$et\n";
echo "<rows>";
echo "<page>".$page."</page>";
echo "<total>".$total_pages."</total>";
echo "<records>".$count."</records>";
// be sure to put text data in CDATA
while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
	echo "<row id='". $row[field1]."'>";
	echo "<cell>". $row[field1]."</cell>";
	echo "<cell>". $row[field2]."</cell>";
	echo "<cell><![CDATA[". $row[field3]."]]></cell>";
	echo "<cell>". $row[field4]."</cell>";
	echo "<cell>". $row[field5]."</cell>";
	echo "</row>";
}
echo "</rows>";
?>

If you perform the search in jqgrid, the following variables are set in the request:

• _search = TRUE
• searchField – the name of the field defined in colModel
• searchString – the string typed in the search field
• searchOper – the operator choosen in the search field (ex. equal, greater than, …)

The $ops array converts the searchOper into the SQL-compatible command (equal -> ‚=’, greated or equal -> ‚>=’, etc.). The getWhereClause generated the WHERE piece of statement, including the modification of searchString in case of some types of search (ex. ‚%’ sign in case of LIKE). Then we just check if the _SEARCH variable is true, and if so the $where statement is added to the query. The rest of the code is just the same as in the jqgrid demo.

Good luck, in case of questions, drop a comment below.