How can I get jqGrid toolbar search working?

Question

At http://trirand.com/blog/jqgrid/jqgrid.html, under "New in Version 3.7" > "Column Search", there is a method explained to search, and it hasn't worked yet for me. I've added:

jQuery("#toolbar").jqGrid('filterToolbar',{stringResult: true,searchOnEnter : false});

and less essential code from the example. My server saw slightly different JSON requests, but no _search=true and no search term, ever.

http://trirand.com/blog/jqgrid/jqgrid.html also gives an incomplete example of server-side code. The SQL statement is given in the example PHP:

$SQL = "SELECT item_id, item, item_cd FROM items ".$where." ORDER BY $sidx $sord LIMIT $start , $limit";

but, while $sidx, $sord, $start, and $limit all have code to define them, $where is not defined (or referenced) anywhere else on the page.

How can I get a column search like the page documents, where my server is being hit by the appropriate requests?

Could you post the jqGrid code which you use? Has the `` element which you use the `id="toolbar"`? Which version of jqGrid you use? — Oleg, May 09 '11 at 19:24
The table has another ID which I used in my code. I'm using jqGrid 4.0. — Christos Hayward, May 09 '11 at 21:36

score 8 · Accepted Answer · answered May 10 '11 at 10:15

8

The filterToolbar method should be called on the same element which you use to define the grid. Look at the working example used it.

I can't help you with the PHP part of your question, because I don't use PHP myself. Nevertheless the demo files from the jqGrid download page seems to contain some PHP code examples which could be helpful for you.

answered May 10 '11 at 10:15

Oleg

220,925
34
403
798

Yes the PHP code on the demo page for jqgrid is not correct, because it doesn't incorporate any of the searching functionality. References an undefined `$where` clause variable for the sql... :-/ – jeffery_the_wind Aug 26 '12 at 08:48

score 3 · Answer 2 · edited Oct 26 '12 at 12:28

Thanks to the previous author for the starting point for the problem solution. Here is ready to use piece of the server-side PHP code implementing search request (from jqGrid) processing:

$filters = $_POST['filters'];
$search = $_POST['_search'];

    $where = "";

if(($search==true) &&($filters != "")) {


        $filters = json_decode($filters);
        $where = " where ";
        $whereArray = array();
        $rules = $filters->rules;
        $groupOperation = $filters->groupOp;
        foreach($rules as $rule) {

            $fieldName = $rule->field;
            $fieldData = mysql_real_escape_string($rule->data);
            switch ($rule->op) {
           case "eq":
                $fieldOperation = " = '".$fieldData."'";
                break;
           case "ne":
                $fieldOperation = " != '".$fieldData."'";
                break;
           case "lt":
                $fieldOperation = " < '".$fieldData."'";
                break;
           case "gt":
                $fieldOperation = " > '".$fieldData."'";
                break;
           case "le":
                $fieldOperation = " <= '".$fieldData."'";
                break;
           case "ge":
                $fieldOperation = " >= '".$fieldData."'";
                break;
           case "nu":
                $fieldOperation = " = ''";
                break;
           case "nn":
                $fieldOperation = " != ''";
                break;
           case "in":
                $fieldOperation = " IN (".$fieldData.")";
                break;
           case "ni":
                $fieldOperation = " NOT IN '".$fieldData."'";
                break;
           case "bw":
                $fieldOperation = " LIKE '".$fieldData."%'";
                break;
           case "bn":
                $fieldOperation = " NOT LIKE '".$fieldData."%'";
                break;
           case "ew":
                $fieldOperation = " LIKE '%".$fieldData."'";
                break;
           case "en":
                $fieldOperation = " NOT LIKE '%".$fieldData."'";
                break;
           case "cn":
                $fieldOperation = " LIKE '%".$fieldData."%'";
                break;
           case "nc":
                $fieldOperation = " NOT LIKE '%".$fieldData."%'";
                break;
            default:
                $fieldOperation = "";
                break;
                }
            if($fieldOperation != "") $whereArray[] = $fieldName.$fieldOperation;
        }
        if (count($whereArray)>0) {
            $where .= join(" ".$groupOperation." ", $whereArray);
        } else {
            $where = "";
        }
    }


    // evaluating $sidx, $sord, $start, $limit 

    $SQL = "SELECT id, brandName, name, description FROM products".$where." ORDER BY $sidx $sord LIMIT $start , $limit"; 
    $result = mysql_query( $SQL ) or die("Couldn't execute query.".mysql_error());

score 2 · Answer 3 · answered Nov 13 '11 at 03:07

You may want to try this code for simplest case:

        $filters = $_GET['filters'];

        $where = "";
        if (isset($filters)) {
            $filters = json_decode($filters);
            $where = " where ";
            $whereArray = array();
            $rules = $filters->rules;

            foreach($rules as $rule) {
                $whereArray[] = $rule->field." like '%".$rule->data."%'";
            }
            if (count($whereArray)>0) {
                $where .= join(" and ", $whereArray);
            } else {
                $where = "";
            }
        }

Before using in production make sure you are handling cases when $_GET['filters'] contains garbage instead of json, and field names/values are properly escaped. Otherwise there are plenty of space for SLQ injections.

score 0 · Answer 4 · answered Feb 23 '13 at 15:05

0

Thanks for posting your code!

The only change was that I had to unescape double-quotes in the 'filters' parameter to get it working:

$filters = str_replace('\"','"' ,$_POST['filters']);

answered Feb 23 '13 at 15:05

Robert Imhoff

471
4
6

How can I get jqGrid toolbar search working?

4 Answers4

Linked