10249

JqGrid server side query conflicts with search

Question:

Take a look at my grid; <img alt="grid" class="b-lazy" data-src="https://i.stack.imgur.com/7xwZD.png" data-original="https://i.stack.imgur.com/7xwZD.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /> The last column reviewed returns tiny int value (0 or 1) from a MySQL database. What I am trying to achieve is to only display the rows where <strong><em>reviewed = 0</em></strong> and still be able to use search to perform query and retrieve fields where <strong><em>reviewed = 1</em></strong>. I want to display rows where <strong><em>reviewed =0</em></strong> when the grid initially loads. I have tried to do this server side using code like "SELECT * FROM table WHERE reviewed = 0". In this case the query works but the search does not work. Is there a way to do this on the client? Please help I have been on this problem for days. I have tried almost everything.

Here's my server side code; //Get the requested page $page = $_GET['page']; //Get how many rows we want to have into the grid $limit = $_GET['rows']; // get index row - i.e. user click to sort. At first time sortname parameter - // after that the index from colModel $sidx = $_GET['sidx']; // sorting order - at first time sortorder $sord = $_GET['sord']; // if we not pass at first time index use the first column for the index or what you want 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); } mysql_query("SET NAMES 'utf8'"); // calculate the number of rows for the query. We need this for paging the result $result = mysql_query("SELECT COUNT(*) AS count FROM renal_apptRequest"); $row = mysql_fetch_array($result,MYSQL_ASSOC); $count = $row['count']; // calculate the total pages for the query if( $count > 0 && $limit > 0) { $total_pages = ceil($count/$limit); } else { $total_pages = 0; } // if for some reasons the requested page is greater than the total // set the requested page to total page if ($page > $total_pages) $page=$total_pages; // calculate the starting position of the rows $start = $limit*$page - $limit; // if for some reasons start position is negative set it to 0 // typical case is that the user type 0 for the requested page if($start <0) $start = 0; // the actual query for the grid data $SQL = "SELECT * FROM renal_apptRequest".$where." ORDER BY $sidx $sord LIMIT $start , $limit"; $result = mysql_query( $SQL ) or die("Couldn't execute query.".mysql_error());

Here's my client side code

$(function () { $("#list").jqGrid({ url:"grid_apptRequest.php", datatype: "json", mtype: "GET", colNames:["ID","Date","referralType","patientName","patientAddress","patientDOB","referralProvider","referralReason","contactName","contactPhone","contactEmail","contactFax","preferredTime","comments","reviewed"], colModel: [ { name: "id",index:'id', width: 55,search:true, formatter:'showlink',formatoptions:{baseLinkUrl:'renal_apptRequest_review.php', target:'_blank'}}, { name: "date",index:'date',search:true, width: 90 }, { name: "referralType",index:'referralType',search:true, width: 80}, { name: "patientName",index:'patientName',search:true, width: 120}, { name: "patientAddress",index:'patientAddress',search:true, width: 120}, { name: "patientDOB",index:'patientDOB',search:true, width: 90 }, { name: "referralProvider",index:'referralProvider',search:true, width: 90 }, { name: "referralReason",index:'referralReason',search:true, width: 120 }, { name: "contactName",index:'contactName',search:true, width: 100}, { name: "contactPhone",index:'contactPhone',search:true, width: 100}, { name: "contactEmail",index:'contactEmail',search:true, width: 100 }, { name: "contactFax",index:'contactFax',search:true, width: 80}, { name: "preferredTime",index:'preferredTime',search:true, width: 70 }, { name: "comments",index:'comments',search:true, width: 100 }, { name: "reviewed",index:'reviewed',search:true,hidedlg:true, width: 60, align: "right" } ], pager: "#pager", rowNum: 10, rowList: [10,20,30], autowidth:true, sortname: "id", sortorder: "asc", viewrecords: true, gridview: true, autoencode: true, caption: "Appointment Request", afterInsertRow: function (id, currentData, jsondata) { if(currentData.reviewed == 1){ $('#list').jqGrid('delRowData',id); } } }).navGrid("#pager", {search:true, edit:false,add:false,del:false,searchtext:"Search"});

Here's an example of JSON data that populates my grid. This is done on the server side. $cipher->decryptThis() is a decryption algorithm used to decrypt encrypted fields from the database.

$responce = new stdClass(); $responce->page = $page; $responce->total = $total_pages; $responce->records = $count; $i=0; while($row = mysql_fetch_array($result,MYSQL_ASSOC)) { $responce->rows[$i]['id']=$row['id']; $responce->rows[$i]['cell']=array($row['id'],$row['date'],$row['referralType'],$cipher->decryptThis($row['patientName']),$cipher->decryptThis($row['patientAddress']),$cipher->decryptThis($row['patientDOB']),$row['referralProvider'], $cipher->decryptThis($row['referralReason']),$cipher->decryptThis($row['contactName']) ,$cipher->decryptThis($row['contactPhone']),$cipher->decryptThis($row['contactEmail']), $row['contactFax'],$row['preferredTime'],$row['comments'], $row['reviewed'] ); $i++; } echo json_encode($responce);

Answer1:

The best way to do this is to group the data by the field of interest in my case <strong><em>reviewed</em></strong>. This snippet of code does the trick. Insert it before the caption of your grid.

grouping:true, groupingView : { groupField : ['reviewed'], groupColumnShow : [true], groupText:['<b>{0} - {1} Item(s)</b>'], groupCollapse : true, groupOrder: ['desc'] },

Answer2:

I am not a php expert. My expertise is jqgrid. In jqgrid you can do this via a hack using the <strong>afterinsertRow event</strong>. In your code add the following. This will remove rows which has got <strong>reviewed = 1</strong>

caption: "Appointment Request", afterInsertRow: function (id, currentData, jsondata) { if(currentData.reviewed == '1'){ // I am not sure if reviewed is string or number. If the above // doesn't work try currentData.reviewed == 1 $('#list').jqGrid('delRowData',id); } },

Your php code looks a bit fiddly. Some tweaking there might help you to achieve it there, but as a I said I am not a php expert so I can't guarantee that. But the above code definitely does the trick.

<a href="http://www.trirand.com/jqgridwiki/doku.php?id=wiki%3aevents" rel="nofollow">What does afterInsertRow does:</a>

<ul><li>This event fires after every inserted row.</li> <li>rowid is the id of the inserted row </li> <li>rowdata is an array of the data to be inserted into the row. This is array of type name: value, where the name is a name from colModel </li> <li>rowelem is the element from the response. If the data is xml this is the xml element of the row; if the data is json this is array containing all the data for the row </li> <li>Note: this event does not fire if gridview option is set to true</li> </ul>

Recommend

  • Display values from one associative array whose key exists as the value in another array
  • Access violation writing location when reading character via scanf_s
  • How do I nest a call to a method within a call to a method using Expression.Call
  • product of two functions
  • Execute mathematical expression and set the value to variable in SQL
  • Updating Dojo provide
  • Defined variables not working in javascript files when I use getScript
  • Group list of tuples by item
  • How can I display the parent menu item's description using Wordpress walkers?
  • IE11 throwing “SCRIPT1014: invalid character” where all other browsers work
  • Extract zip entries to another Zip file
  • Simulate click Geckofx vb,net
  • gspread or such: help me get cell coordinates (not value)
  • Groovy: Unexpected token “:”
  • How to have background script and something similar to a default popup?
  • d3 v4 drag and drop with TypeScript
  • jQuery .attr() and value
  • jQuery show() function is not executed in Safari if submit handler returns true
  • Azure Cloud Service Web Role web pages do not load
  • RectangularRangeIndicator format like triangular using dojo
  • DotNetZip - Calculate final zip size before calling Save(stream)
  • Possible to stop flickering java tooltip in heavyweight mode?
  • How to model a transition system with SPIN
  • Delete MySQLi record without showing the id in the URL
  • align graphs with different xlab
  • php design question - will a Helper help here?
  • Cant find why the layout is getting smaller
  • How to stop GridView from loading again when I press back button?
  • apache spark aggregate function using min value
  • Bitwise OR returns boolean when one of operands is nil
  • sending mail using smtp is too slow
  • Django query for large number of relationships
  • costura.fody for a dll that references another dll
  • Why is Django giving me: 'first_name' is an invalid keyword argument for this function?
  • Binding checkboxes to object values in AngularJs
  • How can I use `wmic` in a Windows PE script?
  • Net Present Value in Excel for Grouped Recurring CF
  • How to push additional view controllers onto NavigationController but keep the TabBar?
  • jQuery Masonry / Isotope and fluid images: Momentary overlap on window resize
  • How to load view controller without button in storyboard?