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?

Solution 1

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.

Solution 2

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()); 

Solution 3

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.

Comments

[jeffery_the_wind]

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... :-/