jqGrid - Dynamic tables on jQuery. Complicated Search

Introduction

In my opinion, jqGrid is the most successful and most convenient plugin for jQuery at the moment, working with database tables via AJAX requests and PHP (There is also a version for ASP.NET).

I will omit all the details in this article, focusing your attention only on the question of how to make a complex table search using this plugin.

For a start, I advise you to meet:

• with the plugin itself here ;
• with examples of his work here ;
• with the article “Introduction to jqGrid” , which describes how to link jqGrid + php + Mysql (or other databases).

How to get data WITHOUT the where clause in this article will not be described. It is assumed that you have read the article “Introduction to jqGrid” and now you want not only to sort the results and break them into pages, but to do complex searches on several fields of the table at once.

Please pay special attention to the example located at the link above, titled "Searching (4.0) new" -> "Show query in search".
')
And so, we created an HTML document:

<table id="newapi"></table> <div id="pnewapi"></div> 

Enable JavaScript code:

 jQuery("#grps1").jqGrid({ //     url:'ajax/get.php', //  -    datatype: "json", //  - colNames:['Inv No', 'Date', 'Client', 'Amount','Tax','Total','Notes'], colModel:[ {name:'id', key : true, index:'id', width:55, searchtype:"integer"}, {name:'invdate',index:'invdate', width:90}, {name:'name', index:'name', width:100}, {name:'amount',index:'amount', width:80, align:"right", searchtype:"number"}, {name:'tax',index:'tax', width:80, align:"right", searchtype:"number"}, {name:'total',index:'total', width:80,align:"right", searchtype:"number"}, {name:'note',index:'note', width:150, sortable:false} ], rowNum:10, width:700, rowList:[10,20,30], pager: '#pgrps1', //     sortname: 'invdate', viewrecords: true, sortorder: "desc" }); jQuery("#grps1").jqGrid('navGrid','#pgrps1', //    {edit:false,add:false,del:false}, //    ,    .      : ""  "" {}, //    {}, //    {}, //    { multipleSearch:true, //     multipleGroup:true, //      showQuery: true //    } ); 

We connected the necessary styles and scripts (you can download it here ):

 <link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/redmond/jquery-ui.css" rel="stylesheet" type="text/css"/> <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.5/jquery.min.js"></script> <script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/jquery-ui.min.js"></script> <link rel="stylesheet" type="text/css" href="css/ui.jqgrid.css"/> <script type="text/javascript" src="src/grid.loader.js"></script> 

Please note that I do not connect the library with one file or separate parts, as it could be done, but I use the file “grid.loader.js”, which also comes with the sources on the official website of the plugin. I changed it a bit and provide a modified code snippet:

 ... var pathtojsfiles = "src/"; // set include to false if you do not want some modules to be included var modules = [ { include: true, incfile:'i18n/grid.locale-ru.js'}, //       'ru' ... 

Using this file it is convenient to manage the components of this module, as well as select its language.

Advanced Search

Advanced Search Form:


Now the sweetest. What does the jqGrid give us when searching?
POST or GET parameters (depending on jqGrid settings):

1. $ _REQUEST ['_ search '] - A boolean value, if the query is conditional on a search, it takes a true value;
2. $ _REQUEST [' filters '] - An object with conditions in the json view;
3. $ _REQUEST [' searchField '] - The name of the field to search for (if the condition is simple);
4. $ _REQUEST [' searchOper '] - Comparison operation for a search field (if the condition is simple);
5. $ _REQUEST [' searchString '] - The value of the field to search for (if the condition is simple);

Now, knowing this data, it is necessary to generate the “where” condition string and pass it on to the necessary procedure, which previously only took the name of the column to be sorted, the direction of sorting, the page number and the number of records per page (for pagination ).

Here is the code that needs to be inserted before the query into the database (in the example, a POST request is used):

 if($_search){ if (isset($_POST['filters'])) $filters = $_POST['filters'];//    if (isset($_POST['searchField'])) $searchField = $_POST['searchField']; //     () if (isset($_POST['searchOper'])) $searchOper = $_POST['searchOper']; //     () if (isset($_POST['searchString'])) $searchString = $_POST['searchString']; //     () $searchString = generateSearchString($filters, $searchField, $searchOper, $searchString); } 

Here is the "heart" of this article, the function of generating conditions for searching in the database.

The generateSearchString () function:

 function generateSearchString($filters, $searchField, $searchOper, $searchString){ $where = ''; if($filters){ $filters = json_decode($filters); $where .= self::generateSearchStringFromObj($filters); } return $where; } 

The recursive function generateSearchStringFromObj () of generating conditions for fetching from the database:

  function generateSearchStringFromObj($filters){ $where = ''; //     if(count($filters)) foreach($filters->rules as $index => $rule){ $rule->data = addslashes($rule->data); $where .= "`".preg_replace('/-|\'|\"/', '', $rule->field)."`"; switch($rule->op){ //          jqGrid case 'eq': $where .= " = '".$rule->data."'"; break; case 'ne': $where .= " != '".$rule->data."'"; break; case 'bw': $where .= " LIKE '".$rule->data."%'"; break; case 'bn': $where .= " NOT LIKE '".$rule->data."%'"; break; case 'ew': $where .= " LIKE '%".$rule->data."'"; break; case 'en': $where .= " NOT LIKE '%".$rule->data."'"; break; case 'cn': $where .= " LIKE '%".$rule->data."%'"; break; case 'nc': $where .= " NOT LIKE '%".$rule->data."%'"; break; case 'nu': $where .= " IS NULL"; break; case 'nn': $where .= " IS NOT NULL"; break; case 'in': $where .= " IN ('".str_replace(",", "','", $rule->data)."')"; break; case 'ni': $where .= " NOT IN ('".str_replace(",", "','", $rule->data)."')"; break; } //   ,      if(count($filters->rules) != ($index + 1)) $where .= " ".addslashes($filters->groupOp)." "; } //     $isSubGroup = false; if(isset($filters->groups)) foreach($filters->groups as $groupFilters){ $groupWhere = self::generateSearchStringFromObj($groupFilters); //     ,    if($groupWhere){ //   ,           //       if(count($filters->rules) or $isSubGroup) $where .= " ".addslashes($filters->groupOp)." "; $where .= $groupWhere; $isSubGroup = true; // , ,        } } if($where) return '('.$where.')'; return ''; //   } 

PS

Inline search form:


ATTENTION: this formula will not work in inline search. In the inline search, jqGrid will pass in the query parameters the column names and their values.

 echo $_POST['id']; //  3,         id  3 

Thus, to implement inline-search by `id`, it is enough to write such code:

 $where = "`id` = " . (int)$_POST['id']; 

To search by several fields, the condition will be supplemented with additional strings joined together by an “AND”. Naturally, instead of equality, you can use any condition allowed for your database.

The source file for the AJAX request handler from jqGrid. (Carefully read the comments, the script will work only if you set up a connection to the database and enter my request to the database instead of my module).