Use jqGrid in combination with PHP and Mysql to read data and display _ jquery

JqGrid can dynamically read and load external data. This article will use PHP and Mysql to explain how to use jqGrid to read and display data, in addition, jqGrid can dynamically read and load external data through the ajax interaction process where data can be queried by entering keywords. This article will use PHP and Mysql to explain how to use jqGrid to read and display data, and ajax interaction that can query data by entering keywords.

The following is an example of how to read the full text from a friend you like.

JqGrid has the search and edit table modules, but these modules make the entire plug-in a little bulky. In addition, I think jqGrid's search, query, edit, and add functions are not good, therefore, the author gave up jqGrid's own search and edit table modules, and used jquery's powerful tool to complete related functions, in line with the actual application of the project.

XHTML

  
  No:  Name:    
     
 
 
  
 

We have created two input boxes for query numbers and names, as well as the "add" and "delete" buttons. The new and delete functions will be explained in the following articles. In addition, there is a # list (jqGrid generates a table) and a page entry # pager in xhtml.

Javascript

Call jqGrid first. Let's take the data in the jqGrid: powerful table plug-in Application Section as an example. Call jqGrid to generate a table. Please refer to the code and comments.

$ ("# List"). jqGrid ({url: 'do. php? Action = list', // the url of the request data datatype: "json", // the requested data type colNames: ['number', 'name', 'screen size ', 'OS', 'batter', 'price (￥) ', 'operation'], // data column name (array) colModel: [// set the parameter information of each data column {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, // number of records displayed on each page rowList: [10, 20, 30], // pagination option. You can select the number of records displayed on each page from the drop-down list. pager: '# pager', // The page entry associated with table data, and the html element autowidth: true, // automatic Match Width: 275, // set the height of the gridview: true, // accelerate the display of viewrecords: true, // display the total number of records multiselect: true, // You can select multiple, the multi-choice box multiselectWidth: 25 appears. // you can specify the width of the Multi-choice column (sortable ).: True, // sortname: 'id' can be sorted, // sortorder: "desc", // sorting method: inverted, in this example, loadComplete: function (data) {// After the server request is completed, the callback function if (data. records = 0) {// if no record is returned, append the message. The delete button is unavailable $ ("p "). appendTo ($ ("# list ")). addClass ("nodata" pai.html ('no relevant data found! '); $ ("# Del_btn "). attr ("disabled", true);} else {// otherwise, the deletion prompt is displayed. The delete button is available $ ("p. nodata "). remove (); $ ("# del_btn "). removeAttr ("disabled ");}}});

For more information about jqGrid options, see jqGrid Chinese instructions -- option settings.

In addition, when we click the "query" button, we send a query keyword request to the backend php program. jqGrid responds according to the results returned by the server. Please refer to 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}, // send data page: 1 }). trigger ("reloadGrid"); // reload });});

PHP

In the previous two sections of JS code, we can see that the background url addresses for reading the list and querying business requests are both do. php? Action = list: the backend php code queries data in the mysql DATA table based on the conditions and returns the data in JSON format to the front-end jqGrid. See the php code:

Include_once ("connect. php "); $ action = $ _ GET ['action']; switch ($ action) {case 'LIST ': // list $ page = $ _ GET ['page']; // obtain the requested page number $ limit = $ _ GET ['rows ']; // GET the number of records displayed on each page $ sidx =$ _ GET ['sidx ']; // obtain the default sorting field $ sord =$ _ GET ['sord']; // obtain the sorting method if (! $ Sidx) $ sidx = 1; $ where = ''; $ title = uniDecode ($ _ GET ['title'], 'utf-8 '); // obtain the query Keyword: name if (! Empty ($ title) $ where. = "and title like '% ". $ title. "% '"; $ sn = uniDecode ($ _ GET ['sn'], 'utf-8'); // obtain the query Keyword: ID if (! Empty ($ sn) $ where. = "and sn = '$ sn'"; // Execute SQL $ result = mysql_query ("SELECT COUNT (*) AS count FROM products where deleted = 0 ". $ where); $ row = mysql_fetch_array ($ result, MYSQL_ASSOC); $ count = $ row ['Count']; // obtain the total number of records // pagination based on the number of records 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; // execute paging 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; // current page $ responce-> total = $ total_pages; // total page number $ responce-> records = $ count; // total number of records $ I = 0; // read data cyclically while ($ row = mysql_fetch_array ($ result, MYSQL_ASSOC )) {$ responce-> rows [$ I] ['id'] = $ row [id]; $ opt = "modify "; $ responce-> rows [$ I] ['cell '] = array ($ row ['sn'], $ row ['title'], $ row ['SIZE'], $ row ['OS'], $ row ['charge'], $ row ['price'], $ opt ); $ I ++;} echo json_encode ($ responce); // outputs the JSON data break; case '': echo 'bad request. '; break ;}

It is worth mentioning that when we perform a Chinese Query, that is, when entering a Chinese keyword for the query, we need to use js for escape encoding, and then php decodes the Chinese keywords when receiving them, otherwise, the Chinese character string cannot be recognized. In this example, the uniDecode function is used for decoding and the code is provided as follows:

/Process the 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) {// running -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 section describes how to use jqGrid to read data and display all the content based on PHP and Mysql. We will continue to introduce the jqgrid table-related applications.