Using jqGrid to read data and display it based on PHP and Mysql_jquery

jqGrid can dynamically read and load external data. This article will combine PHP and Mysql to explain how to use jqGrid to read and display data, as well as the ajax interaction process of querying data by entering keywords.

I will show you the renderings below. Friends who like it can read the full text.

jqGrid itself has search and edit table modules, but these modules will make the entire plug-in a bit bulky, and I think jqGrid’s search query and edit/add functions are not easy to use, so I gave up jqGrid’s own search and edit functions. The edit table module uses the jquery tool to complete related functions, which is in line with the actual application of the project.

XHTML

<div id="opt"> 
 <div id="query"> 
 <label>编号：</label><input type="text" class="input" id="sn" /> 
 <label>名称：</label><input type="text" class="input" id="title" /> 
 <input type="submit" class="btn" id="find_btn" value="查 询" /> 
 </div> 
 <input type="button" class="btn" id="add_btn" value="新 增" /> 
 <input type="button" class="btn" id="del_btn" value="删 除" /> 
</div> 
<table id="list"></table> 
<div id="pager"></div> 

We are creating two input boxes for querying the number and name, as well as the "Add" and "Delete" buttons. The add and delete functions will be specifically explained in the following article. In addition, there is a #list for placing tables (jqGrid generates tables) and a paging bar #pager in xhtml.

Javascript

First call jqGrid. We take the data in the article jqGrid: Application of Powerful Table Plug-in on this website as an example to call jqGrid to generate a table. Please see the code and comments.

$("#list").jqGrid({ 
 url:'do.php?action=list', //请求数据的url地址 
 datatype: "json", //请求的数据类型 
 colNames:['编号','名称','主屏尺寸','操作系统','电池容量', '价格(￥)','操作'], //数据列名称（数组） 
 colModel:[ //数据列各参数信息设置 
  {name:'sn',index:'sn', editable:true, width:80,align:'center', title:false}, 
  {name:'title',index:'title', width:180, title:false}, 
  {name:'size',index:'size', width:120}, 
 {name:'os',index:'os', width:120}, 
  {name:'charge',index:'charge', width:100,align:'center'}, 
 {name:'price',index:'price', width:80,align:'center'}, 
  {name:'opt',index:'opt', width:80, sortable:false, align:'center'}  
 ], 
 rowNum:10, //每页显示记录数 
 rowList:[10,20,30], //分页选项，可以下拉选择每页显示记录数 
 pager: '#pager', //表格数据关联的分页条，html元素 
 autowidth: true, //自动匹配宽度 
 height:275, //设置高度 
 gridview:true, //加速显示 
 viewrecords: true, //显示总记录数 
 multiselect: true, //可多选，出现多选框 
 multiselectWidth: 25, //设置多选列宽度 
 sortable:true, //可以排序 
 sortname: 'id', //排序字段名 
 sortorder: "desc", //排序方式：倒序，本例中设置默认按id倒序排序 
 loadComplete:function(data){ //完成服务器请求后，回调函数 
 if(data.records==0){ //如果没有记录返回，追加提示信息，删除按钮不可用 
  $("p").appendTo($("#list")).addClass("nodata").html('找不到相关数据！'); 
  $("#del_btn").attr("disabled",true); 
 }else{ //否则，删除提示，删除按钮可用 
  $("p.nodata").remove(); 
  $("#del_btn").removeAttr("disabled"); 
 } 
 } 
}); 

For jqGrid related option settings, please refer to: jqGrid Chinese documentation - option settings .

In addition, when we click the "Query" button, a query keyword request is sent to the background PHP program, and jqGrid responds based on the results returned by the server. Please see the code.

$(function(){ 
 $("#find_btn").click(function(){ 
 var title = escape($("#title").val()); 
 var sn = escape($("#sn").val()); 
 $("#list").jqGrid('setGridParam',{ 
  url:"do.php?action=list", 
  postData:{'title':title,'sn':sn}, //发送数据 
  page:1 
 }).trigger("reloadGrid"); //重新载入 
 }); 
}); 

PHP

In the previous two pieces of JS code, you can see that the background url addresses for reading lists and querying business requests are do.php?action=list. The background php code is responsible for querying the data in the mysql data table according to conditions. And return the data to the front-end jqGrid in JSON format, please see the php code:

include_once ("connect.php"); 
$action = $_GET['action']; 
switch ($action) { 
 case 'list' : //列表 
 $page = $_GET['page']; //获取请求的页数 
 $limit = $_GET['rows']; //获取每页显示记录数 
 $sidx = $_GET['sidx']; //获取默认排序字段 
 $sord = $_GET['sord']; //获取排序方式 
 if (!$sidx) 
  $sidx = 1; 
 
 $where = ''; 
 $title = uniDecode($_GET['title'],'utf-8'); //获取查询关键字：名称 
 if(!empty($title)) 
  $where .= " and title like '%".$title."%'"; 
 $sn = uniDecode($_GET['sn'],'utf-8'); //获取查询关键字：编号 
 if(!empty($sn)) 
  $where .= " and sn='$sn'"; 
 //执行SQL 
 $result = mysql_query("SELECT COUNT(*) AS count FROM products where deleted=0".$where); 
 $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; 
 if ($start < 0 ) $start = 0; 
 //执行分页SQL 
 $SQL = "SELECT * FROM products WHERE deleted=0".$where." ORDER BY $sidx $sord 
 LIMIT $start , $limit"; 
 $result = mysql_query($SQL) or die("Couldn t execute query." . mysql_error()); 
 $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]; 
  $opt = "<a href='edit.php'>修改</a>"; 
  $responce->rows[$i]['cell'] = array ( 
  $row['sn'], 
  $row['title'], 
  $row['size'], 
  $row['os'], 
  $row['charge'], 
  $row['price'], 
  $opt 
  ); 
  $i++; 
 } 
 echo json_encode($responce); //输出JSON数据 
 break; 
 case '' : 
 echo 'Bad request.'; 
 break; 
} 

It is worth mentioning that when we perform Chinese queries, that is, when entering Chinese keywords for query, we need to use js for escape encoding, and then php will decode accordingly when receiving the Chinese keywords, otherwise the Chinese will not be recognized. String phenomenon, in this example, the uniDecode function is used to decode, and the code is provided together:

/处理接收jqGrid提交查询的中文字符串 
function uniDecode($str, $charcode) { 
 $text = preg_replace_callback("/%u[0-9A-Za-z]{4}/", toUtf8, $str); 
 return mb_convert_encoding($text, $charcode, 'utf-8'); 
} 
function toUtf8($ar) { 
 foreach ($ar as $val) { 
 $val = intval(substr($val, 2), 16); 
 if ($val < 0x7F) { // 0000-007F 
  $c .= chr($val); 
 } 
 elseif ($val < 0x800) { // 0080-0800 
  $c .= chr(0xC0 | ($val / 64)); 
  $c .= chr(0x80 | ($val % 64)); 
 } else { // 0800-FFFF 
  $c .= chr(0xE0 | (($val / 64) / 64)); 
  $c .= chr(0x80 | (($val / 64) % 64)); 
  $c .= chr(0x80 | ($val % 64)); 
 } 
 } 
 return $c; 
} 

The above is what this article introduces to you using jqGrid to read and display data based on the combination of PHP and Mysql. We will continue to introduce jqgrid table-related applications to you, so stay tuned.