Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- jqGrid search data returned from php file
- <?php
- $page = $_GET['page']; // get the requested page
- $limit = $_GET['rows']; // get how many rows we want to have into the grid
- $sidx = $_GET['sidx']; // get index row - i.e. user click to sort
- $sord = $_GET['sord']; // get the direction
- 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);
- }
- // connect to the database
- $dbhost = "host_address";
- $dbuser = "db_user";
- $dbpassword = "db_pass";
- $database = "db_name";
- $tablename
- $db = mysql_connect($dbhost, $dbuser, $dbpassword)
- or die("Connection Error: " . mysql_error());
- mysql_select_db($database) or die("Error conecting to db.");
- mysql_set_charset('utf8',$database);
- mysql_query("SET NAMES 'utf8'");
- $result = mysql_query("SELECT COUNT(*) AS count FROM $tablename");
- $row = mysql_fetch_array($result,MYSQL_ASSOC);
- $count = $row['count'];
- if( $count >0 ) {
- $total_pages = ceil($count/$limit);
- } else {
- $total_pages = 0;
- }
- if ($page > $total_pages) $page=$total_pages;
- $start = $limit*$page - $limit; // do not put $limit*($page - 1)
- $SQL = "SELECT field1, field2, field3, field4, field5 FROM $tablename "
- .$where." ORDER BY $sidx $sord LIMIT $start , $limit";
- $result = mysql_query( $SQL ) or die("Couldn?t execute query.".mysql_error());
- if ( stristr($_SERVER["HTTP_ACCEPT"],"application/xhtml+xml") ) {
- header("Content-type: application/xhtml+xml;charset=utf-8"); } else {
- header("Content-type: text/xml;charset=utf-8");
- }
- $et = ">";
- echo "<?xml version='1.0' encoding='utf-8'?$etn";
- echo "<rows>";
- echo "<page>".$page."</page>";
- echo "<total>".$total_pages."</total>";
- echo "<records>".$count."</records>";
- // be sure to put text data in CDATA
- while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
- echo "<row id='". $row[field1]."'>";
- echo "<cell>". $row[field1]."</cell>";
- echo "<cell>". $row[field2]."</cell>";
- echo "<cell><![CDATA[". $row[field3]."]]></cell>";
- echo "<cell>". $row[field4]."</cell>";
- echo "<cell>". $row[field5]."</cell>";
- echo "</row>";
- }
- echo "</rows>";
- ?>
Add Comment
Please, Sign In to add comment