Loading JSON data in jqGrid

Question

I'm having troubles when showing my json data in jqGrid.

I've searched a lot in this forum and tried various forms to make it work. I apologize if this was already answered but I really need help with this one.

At the server page I was only using JavaScriptSerializer to send the data and the jsonreader function with the default parameters (this worked ok).

I now need to paginate and have changed my server page code to work with the sidx, sord, page, rows parameters.

The resulting string from the server looks like this:

{"total":"344","page":"1","records":"8577","root":[{"Id":"1","SerialNumber":"132","Name":"ServerName"},...]}

Here is my jQuery code:

$("#list").jqGrid({
        datatype: "json",
        mtype: 'GET',
        url:'https://server/handlerpage.ashx',
        colNames:['Id','SerialNumber','Name'],
        colModel :[         
                {name:'Id', index:'Id', jsonmap:"Id", width:20},
                {name:'Name', index:'Name', jsonmap:"Name", width:120},
                {name:'SerialNumber', index:'SerialNumber', jsonmap:"SerialNumber", width:100}],    
        jsonreader: {repeatitems:false,id:'Id',root:'root'},
        pager: '#pager',
        rowNum:25,
        rowList:[25,50,75,100],
        sortname: 'Id',
        viewrecords:true,
        gridview: true,
        height:"400",
        width:"700",
        caption: 'Select from existing server',
        loadtext:'Loading, please wait'
      }).navGrid("#pager", { edit: false, add: false, search: true, del: false });

Answer 1

In order to use json data as a response from a query the response has to be formatted correctly. It is difficult to determine what is the correct response and how to get it from the documents. More problems can occur as a result of your server settings generating a warning as part of the response which will cause the grid not to load.

From the documents this is the default json reader, meaning if you format your response correctly you don't need to add anything / a custom json reader.

jsonReader : { 
  root: "rows", 
  page: "page", 
  total: "total", 
  records: "records", 
  repeatitems: true, 
  cell: "cell", 
  id: "id",
  userdata: "userdata",
  subgrid: { 
     root:"rows", 
     repeatitems: true, 
     cell:"cell" 
  } 

First make sure you have the right response format. {"page":1,"total":1,"records":"2","rows":[{"id":"1","cell":["1","mydata1"]},{"id":"2","cell":["2","mydata2"]}]}

No sample is given on the docs page but the above is correct if you only have two columns in your grid. You need to get your server query / parsing of your server query and values passed to the page that runs your sever side scripts to return this format. The example.php page from the documents gives you all the values you need.

This code will give you the proper header, to avoid error warnings and matches up with the response from above. Also note that in the docs they do not add the apostrophes around the indexes for the associative array index names. That will fail.

header('Content-type: application/json');$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['entity_name']); $i++; } echo json_encode($responce);

To rehash what is missing in the docs (it is possible that I just missed them and even if, the documents for jQgrid are epic/a masters work): 1. No mention of the need for a header statement for json (it is clear with the XML examples) 2. No sample of what a working returned string should look like 3. Lack of proper formatting of the associative array indexes. 4. No mention of how to avoid a PHP warning being returned as part of the response