Databases
StackOverflow2013
Postsdbo
POClauses in jqGrid query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POClauses in jqGrid query
    primarykey
    data
    text
    <p>I have a grid that works perfectly well. What I need to do is to originally return certain fields from my database based on some condition for example by doing a </p> <pre><code>SELECT * FROM table WHERE reviewed = 0; </code></pre> <p>I want these results to be displayed on grid when it loads but I also want to be able to use advanced search on grid. I am new to jqgrid and I have been on this issue for quite some time now. Here's my original code.</p> <pre><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'=&gt;'=', //equal 'ne'=&gt;'&lt;&gt;',//not equal 'lt'=&gt;'&lt;', //less than 'le'=&gt;'&lt;=',//less than or equal 'gt'=&gt;'&gt;', //greater than 'ge'=&gt;'&gt;=',//greater than or equal 'bw'=&gt;'LIKE', //begins with 'bn'=&gt;'NOT LIKE', //doesn't begin with 'in'=&gt;'LIKE', //is in 'ni'=&gt;'NOT LIKE', //is not in 'ew'=&gt;'LIKE', //ends with 'en'=&gt;'NOT LIKE', //doesn't end with 'cn'=&gt;'LIKE', // contains 'nc'=&gt;'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 &gt; 0 &amp;&amp; $limit &gt; 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 &gt; $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 &lt;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()); // we should set the appropriate header information. Do not forget this. header("Content-type: text/xml;charset=utf-8"); $s = "&lt;?xml version='1.0' encoding='utf-8'?&gt;"; $s .= "&lt;rows&gt;"; $s .= "&lt;page&gt;".$page."&lt;/page&gt;"; $s .= "&lt;total&gt;".$total_pages."&lt;/total&gt;"; $s .= "&lt;records&gt;".$count."&lt;/records&gt;"; // be sure to put text data in CDATA while($row = mysql_fetch_array($result,MYSQL_ASSOC)) { $s .= "&lt;row id='". $row['id']."'&gt;"; $s .= "&lt;cell&gt;". $row['id']."&lt;/cell&gt;"; $s .= "&lt;cell&gt;". $row['date']."&lt;/cell&gt;"; $s .= "&lt;cell&gt;&lt;![CDATA[". $row['referralType']."]]&gt;&lt;/cell&gt;"; $s .= "&lt;cell&gt;&lt;![CDATA[".$cipher-&gt;decryptThis($row['patientName'])."]]&gt;&lt;/cell&gt;"; $s .= "&lt;cell&gt;&lt;![CDATA[".$cipher-&gt;decryptThis($row['patientAddress'])."]]&gt;&lt;/cell&gt;"; $s .= "&lt;cell&gt;". $cipher-&gt;decryptThis($row['patientDOB'])."&lt;/cell&gt;"; $s .= "&lt;cell&gt;&lt;![CDATA[". $row['referralProvider']."]]&gt;&lt;/cell&gt;"; $s .= "&lt;cell&gt;&lt;![CDATA[".$cipher-&gt;decryptThis($row['referralReason'])."]]&gt;&lt;/cell&gt;"; $s .= "&lt;cell&gt;&lt;![CDATA[". $cipher-&gt;decryptThis($row['contactName'])."]]&gt;&lt;/cell&gt;"; $s .= "&lt;cell&gt;".$cipher-&gt;decryptThis($row['contactPhone'])."&lt;/cell&gt;"; $s .= "&lt;cell&gt;&lt;![CDATA[".$cipher-&gt;decryptThis($row['contactEmail'])."]]&gt;&lt;/cell&gt;"; $s .= "&lt;cell&gt;". $row['contactFax']."&lt;/cell&gt;"; $s .= "&lt;cell&gt;&lt;![CDATA[". $row['preferredTime']."]]&gt;&lt;/cell&gt;"; $s .= "&lt;cell&gt;&lt;![CDATA[". $row['comments']."]]&gt;&lt;/cell&gt;"; $s .= "&lt;cell&gt;". $row['reviewed']."&lt;/cell&gt;"; $s .= "&lt;/row&gt;"; } $s .= "&lt;/rows&gt;"; echo $s; </code></pre> <p>//And here's my jqrid javascript code</p> <pre><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: 30 }, { name: "comments",index:'comments',search:true, width: 100 }, { name: "reviewed",index:'reviewed',search:true,hidedlg:true, width: 20, 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" }).navGrid("#pager", {search:true, edit:false,add:false,del:false,searchtext:"Search"}); }); </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDamilare Binutu
    1. USDamilare Binutu
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COFirst of all `mysql_xxxxx...` is deprecated, so better use `mysqli_xxxx_.....`. *Ref:* http://www.php.net/manual/en/migration55.deprecated.php
      singulars
      1. POClauses in jqGrid query
      1. USdreamweiver
    2. COsimple way to accomplish it would be to raise a ajax request to server script to extract the data from DB and then format the same output using jqgrid , you can google on jqgrid to know the syntax of using it . for jquery ajax refere,http://api.jquery.com/jquery.ajax
      singulars
      1. POClauses in jqGrid query
      1. USdreamweiver
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload