JSON

jqGrid

 JSON Data

JSON data is handled in a fashion very similar to that of xml data. What is important is that the definition of the jsonReader matches the data being receiveddatatype: json, (or jsonstring)
The default definition of the jsonreader is as follows:

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

datastr:
If the parameter datatype is 'json', jqGrid expects the following default format for json data.

{ 
  total: "xxx", 
  page: "yyy", 
  records: "zzz",
  rows : [
    {id:"1", cell:["cell11", "cell12", "cell13"]},
    {id:"2", cell:["cell21", "cell22", "cell23"]},
      ...
  ]
}

The tags used in this example are described in the following table:

Tag	Description
total	total pages for the query
page	current page of the query
records	total number of records for the query
rows	an array that contains the actual data
id	the unique id of the row
cell	an array that contains the data for a row

In this case, the number of the elements in the cell array should equal the number of elements in colModel.
Let's consider our example in PHP and MySQL with JSON data. In this case I assume that the json service is enabled in PHP.

<?php
include("dbconfig.php");
$page = $_REQUEST['page'];  // get the requested page
$limit = $_REQUEST['rows']; // get how many rows we want to have into the grid
$sidx = $_REQUEST['sidx']; // get index row - i.e. user click to sort
$sord = $_REQUEST['sord']; // get the direction
if(!$sidx) $sidx =1;

// connect to the database
$db = mysql_connect($dbhost, $dbuser, $dbpassword) or die("Connection Error: " . mysql_error());
mysql_select_db($database) or die("Error conecting to db.");
$result = mysql_query("SELECT COUNT(*) AS count FROM invheader a, clients b WHERE a.client_id=b.client_id".$wh);
$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)
if ($start<0) $start = 0;
$SQL = "SELECT invid,invdate,amount,tax,total,note FROM invheader ORDER BY ".$sidx." ".$sord. " LIMIT ".$start." , ".$limit;
$result = mysql_query( $SQL ) or die("Could not execute query.".mysql_error());

// Construct the json data
$response->page = $page; // current page
$response->total = $total_pages; // total pages
$response->records = $count; // total records
$i=0;
while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
    $response->rows[$i]['id']=$row[invid]; //id 
    $response->rows[$i]['cell']=array($row[invid],$row[invdate],$row[amount],$row[tax],$row[total],$row[note]);
    $i++;
} 
echo json_encode($response);
?>

The structure of the jsonReader is very similar to the xmlReader. The only missing part is the row element which is not needed for JSON data. Let's begin our walk through the jsonReader.
The first element is a root element. This element describes where our data begins. In other words, this points to the array that contains the data. If we set

jsonReader: { root:"invdata" }

then the returned string should be

{ 
  total: "xxx",
  page: "yyy",
  records: "zzz",
  invdata: [
    {id:"1", cell:["cell11", "cell12", "cell13"]},
    {id:"2", cell:["cell21", "cell22", "cell23"]}
  ]
}

The page, total and record elements describe the information needed for the pager. For example, if the jsonReader is set as follows,

jsonReader:{
  root: "invdata",
  page: "currpage"
  total: "totalpages"
  records: "totalrecords"
}

then the data should be

{ 
  totalpages: "xxx", 
  currpage: "yyy", 
  totalrecords: "zzz",
  invdata: [
    {id:"1", cell:["cell11", "cell12", "cell13"]},
    {id:"2", cell:["cell21", "cell22", "cell23"]}
  ]
}

The cell element describes the array which contains the data for the row.

jsonReader:{
  root: "invdata",
  page: "currpage"
  total: "totalpages"
  records: "totalrecords",
  cell: "invrow"
}

The data to match this description would be

{ 
  totalpages: "xxx", 
  currpage: "yyy", 
  totalrecords: "zzz",
  invdata : [
    {id:"1", invrow:["cell11", "cell12", "cell13"]},
    {id:"2", invrow:["cell21", "cell22", "cell23"]}
  ]
}

The id element descibes the unique id for the row

jsonReader:{
  root: "invdata",
  page: "currpage"
  total: "totalpages"
  records: "totalrecords",
  cell: "invrow",
  id: "invid"
}

The data for this description is:

{ 
  totalpages: "xxx", 
  currpage: "yyy", 
  totalrecords: "zzz",
  invdata : [
    {invid:"1", invrow:["cell11", "cell12", "cell13"]},
    {invid:"2", invrow:["cell21", "cell22", "cell23"]}
  ]
}

It is possible to set the cell element to an empty string. And, it is possible to set the id as number. Here is an example of these possibilities:

jsonReader:{
  root: "invdata",
  page: "currpage"
  total: "totalpages"
  records: "totalrecords",
  cell: "",
  id: "0"
}

In this case the id is the first element from the row data

{ 
  totalpages: "xxx", 
  currpage: "yyy", 
  totalrecords: "zzz",
  invdata: [
    {"1", "cell11", "cell12", "cell13"},
    {"2", "cell21", "cell22", "cell23"}
  ]
}

The repeatitems element tells jqGrid that the information for the data in the row is repeatable - i.e. the elements have the same tag cell described in cell element. Setting this option to false instructs jqGrid to search elements in the json data by name. This is the name from colModel or the name described with the jsonmap option in colModel.
Here is an example:

jsonReader:{
  root: "invdata",
  page: "currpage"
  total: "totalpages"
  records: "totalrecords",
  repeatitems: false,
  id: "0"
}

The resulting data in our example should be:

{ 
  totalpages: "xxx", 
  currpage: "yyy", 
  totalrecords: "zzz",
  invdata: [
    {invid:"1",invdate:"cell11", amount:"cell12", tax:"cell13", total:"1234", note:"somenote" },
    {invid:"2",invdate:"cell21", amount:"cell22", tax:"cell23", total:"2345", note:"some note" }
  ]
}

The id element in this case is 'invid'.
A very useful feature here is that there is no need to include all the data that is represented in colModel. Since we make a search by name, the order does not need to match the order in colModel. Hence the following string will be correctly interpreted in jqGrid.

{ 
  totalpages: "xxx", 
  currpage: "yyy", 
  totalrecords: "zzz",
  invdata: [
    {invid:"1",invdate:"cell11",  note:"somenote" },
    {invid:"2", amount:"cell22", tax:"cell23", total:"2345" }
  ]
}

---

  Last Updated: 25/5/2011 | © Sriseshaa.com jqGrid - a jQuery Plugin, 2011