jqGrid: How to use single search feature?

Question

How do I use the single search feature of jqGrid?

Im following the example "Single Search" under "New in version 3.7". jqGrid Demo Page

My script is almost exact save the queries used to retrieve data from the database but my script doesn't do the filtering.

Can anyone point me to a complete example of the single search feature using PHP?

I also have a question regarding the script in the demo page.

How does jQuery pass the conditions to PHP to filter the results? I've notice in the query it had a variable $where but the variable wasn't declared in the PHP script.

user1419445 · Accepted Answer

I've made up an example using the MySQL database.
First of all create a "test" database, an "items" table, and insert some records:

CREATE DATABASE IF NOT EXISTS test;

CREATE TABLE IF NOT EXISTS `items` (
  `item_id` int(11) NOT NULL,
  `item` varchar(255) DEFAULT '',
  `item_cd` varchar(255) DEFAULT '',
  PRIMARY KEY (`item_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

LOCK TABLES `items` WRITE;
INSERT INTO `items` VALUES (1,'Lorem','575878');
INSERT INTO `items` VALUES (2,'Lorem','857450');
INSERT INTO `items` VALUES (3,'ipsum','292404');
INSERT INTO `items` VALUES (4,'dolor','814131');
INSERT INTO `items` VALUES (5,'sit','962077');
INSERT INTO `items` VALUES (6,'amet,','549801');
INSERT INTO `items` VALUES (7,'sed','166822');
INSERT INTO `items` VALUES (8,'in','616758');
INSERT INTO `items` VALUES (9,'id','550799');
INSERT INTO `items` VALUES (10,'dictum','763004');
INSERT INTO `items` VALUES (11,'velit','244985');
INSERT INTO `items` VALUES (12,'est','525127');
INSERT INTO `items` VALUES (13,'suspendisse,','351690');
INSERT INTO `items` VALUES (14,'mauris','655061');
INSERT INTO `items` VALUES (15,'eget','423779');
INSERT INTO `items` VALUES (16,'imperdiet','493615');
INSERT INTO `items` VALUES (17,'ut,','864029');
INSERT INTO `items` VALUES (18,'mauris','546065');
INSERT INTO `items` VALUES (19,'vestibulum,','562819');
INSERT INTO `items` VALUES (20,'sagittis','238043');
INSERT INTO `items` VALUES (21,'ac.','867508');
INSERT INTO `items` VALUES (22,'Mauris','674897');
INSERT INTO `items` VALUES (23,'id','288097');
INSERT INTO `items` VALUES (24,'quam,','889530');
UNLOCK TABLES;

Next, create the html page "singlesearch.html":

Then create a PHP page "localset.php":

 "= '%s'",            // Equal
        'ne' => "<> '%s'",           // Not equal
        'lt' => "< '%s'",            // Less than
        'le' => "<= '%s'",           // Less than or equal
        'gt' => "> '%s'",            // Greater than
        'ge' => ">= '%s'",           // Greater or equal
        'bw' => "like '%s%%'",       // Begins With
        'bn' => "not like '%s%%'",   // Does not begin with
        'in' => "in ('%s')",         // In
        'ni' => "not in ('%s')",     // Not in
        'ew' => "like '%%%s'",       // Ends with
        'en' => "not like '%%%s'",   // Does not end with
        'cn' => "like '%%%s%%'",     // Contains
        'nc' => "not like '%%%s%%'", // Does not contain
        'nu' => "is null",           // Is null
        'nn' => "is not null"        // Is not null
    ); 
    $value = mysqli_real_escape_string($link, $_REQUEST["searchString"]);
    $where = sprintf("where %s ".$operations[$_REQUEST["searchOper"]], $_REQUEST["searchField"], $value);
}

$SQL = "SELECT item_id, item, item_cd FROM items ".$where." ORDER BY $sidx $sord";
myTrace($SQL);
$result = mysqli_query($link, $SQL);
if (!$result) {
    myTrace(mysqli_error($link));
    die("Couldn't execute query: ".mysqli_error($link));
}

if ($limit < 0) $limit = 0;

$start = ($limit * $page) - $limit;
if ($start < 0) $start = 0;

$count = mysqli_num_rows($result);
if ($count > 0) {
    $total_pages = ceil($count / $limit);
} else {
    $total_pages = 0;
}

if ($page > $total_pages) {
    $page = $total_pages;
}

$responce->page    = $page;
$responce->total   = $total_pages;
$responce->records = $count;

mysqli_data_seek($result, $start);
for ($i = 0; $row = mysqli_fetch_assoc($result); $i++) {
    if (($limit > 0) && ($i >= $limit)) break;
    $responce->rows[$i]['id']   = $row['item_id'];
    $responce->rows[$i]['cell'] = array($row['item_id'], $row['item'], $row['item_cd']);
} 
echo json_encode($responce);

mysqli_close($link);

?>

Enable the PHP mysqli extension in your PHP.INI file, adjust the connection parameters in the "localset.php" script (at line 15), and finally open the "singlesearch.html" page in your browser.

jqGrid: How to use single search feature?

Answers (1)

Related Questions