Free jqGrid - JSON encode example using PHP

Question

Free jqGrid uses data in the following JSON name:value pairs format:

var data = {
        "page": "1",
        "records": "3",
        "rows": [
            { "DataID": "1", "DataDesc": "Test 1", "DataTitle": "Test 1" }
        ]
    };

I have the following in the PHP script:

$i=0; 
while ($row = mysql_fetch_assoc($result)) {
      $data->rows[$i]['cell']=array($row); 
      $i++;
}
print json_encode($data);

Which returns:

{"rows":[{"cell":[{"user_id":"00082563","first_name":"Peter","case_title":"Male with STI (urethritis)","case_started":"2017-06-02 10:52:10"}]}]}

Which looks to be OK. However, with the JSON parts of the code below, the grid doesn't display at all.

    function loadFirstGrid() {
        $("#FirstGrid").jqGrid({
            url: "scripts/json_test.php?user=" + user,
            dataType: "json",
            mtype: "GET",
            postData: {
                json: JSON.stringify(data)
            },
            colModel: [{
        name: "user_id",
        label: "User ID",
        width: 120
    },
    {
        name: "first_name",
        label: "Name",
        width: 400
    },
    {
        name: "case_title",
        label: "Case Title",
        width: 500
    },
    {
        name: "case_started",
        label: "Case Started",
        width: 200
    },
],
            emtyrecords: "Nothing to display",
            viewrecords: true,
            sortable: true,
            shrinkToFit: false,
            autowidth: true,
            caption: 'First Grid'
        });
    }

But if I remove the postData part to have the following, the grid displays, but of course no data.

  function loadFirstGrid() {
    $("#FirstGrid").jqGrid({
        url: "scripts/json_test.php?user=" + user,
        dataType: "json",
        mtype: "GET",
        colModel: [{...

Any ideas?

Answer 1

OK, finally worked through this and got it working with this

function loadFirstGrid() {
    $("#FirstGrid").jqGrid({
         url: "scripts/json_test.php?user=" + user,
        dataType: "json",
        mtype: "GET",
      colModel: [
            {name: "user_id", label:"User ID", width: 120},
            {name: "first_name", label:"Name", width: 400},
            {name: "case_title", label:"Case Title", width: 500},
            {name: "case_started", label:"Case Started", width: 200},
        ],
        emtyrecords: "Nothing to display",
        viewrecords: true,
        sortable: true,
        shrinkToFit: false,
        autowidth: true,
        caption: 'First Grid',
    });
}

To get the correct format JSON for jqGrid:

{"page":"1","total":"1","records":"1","rows":[{"user_id":"00082563","first_name":"Peter","case_title":"Male with STI (urethritis)","case_started":"2017-06-02 10:52:10"}]}

I used the following PHP script:

$page = '1';
$total_pages = '1';
$count = '1';

$data = (object) array('page' => $page, 'total' => $total_pages, 'records' =>$count, 'rows' => "");

  $data->page = $page;
  $data->total = $total_pages;
  $data->records = $count;

$i=0; 
while ($row = mysql_fetch_assoc($result)) {
      $data->rows=array($row); 
      $i++;
}
print json_encode($data);
?>

(NOTE: I don't care about the number of pages, total_pages and count as my grids will only ever have one primary record and multiple subgrids with only one record). So hope this helps someone; there is not much in the documentation or examples that describes how to do this with Free jqGrid ;-(

Answer 2

First of all JavaScript is case sensitive language and jqGrid will ignore parameter dataType: "json". You should fix it to datatype: "json".

Seconds, you use exotic format of the JSON data:

{
    "rows": [{
        "cell": {
            "user_id": "00082563",
            "first_name": "Peter",
            "case_title": "Male with STI (urethritis)",
            "case_started": "2017-06-02 10:52:10"
        }
    }]
}

instead of

{
    "rows": [ {
        "user_id": "00082563",
        "first_name": "Peter",
        "case_title": "Male with STI (urethritis)",
        "case_started": "2017-06-02 10:52:10"
    }]
}

or

[ {
    "user_id": "00082563",
    "first_name": "Peter",
    "case_title": "Male with STI (urethritis)",
    "case_started": "2017-06-02 10:52:10"
}]

You don't use loadonce: true and it's unclear, whether you plan to implement server side paging, sorting and filtering of data or you want to return all the data at once and jqGrid should use client side paging, sorting and filtering.

Finally, you should use name properties of colModel corresponds to the properties of user_id. It's very important to understand, that jqGrid have to assign unique id to every row of the grid (see here). Thus you have to inform jqGrid, which property contains rowid. You can use either jsonReader: { id: "user_id" } or to include property key: true in the column user_id.

The demo https://jsfiddle.net/OlegKi/qgrwymuu/1/ contains an example of described above modifications. It uses Echo service of JSFiddle to simulate server, which responses with some JSON data.