How to Display Data from Multiple Tables in jqGrid Using a JSON Response with City Mapping?

jqGrid: Handling Data from Multiple Related Tables

jqGrid, a powerful jQuery plugin, excels at displaying data from various sources. This example demonstrates how to efficiently present data from two interconnected tables: Students and Cities.

The Students table uses city IDs as foreign keys, referencing the Cities table for city names:

<code>Students:
| SID | SNAME | CITY |
|---|---|---|
| 1 | ABC | 11 |
| 2 | XYZ | 12 |
| 3 | ACX | 13 |

Cities:
| CID | CNAME |
|---|---|
| 11 | Chennai |
| 12 | Mumbai |
| 13 | Delhi |</code>

The desired output format is a single jqGrid displaying student information with the actual city names:

SID	SNAME	City
1	ABC	Chennai
2	XYZ	Mumbai
3	ACX	Delhi
4	KHG	Banglore
5	ADF	Hyderabad
6	KKR	Kolkatta

To achieve this, the server-side response is structured to include a cityMap which translates city IDs to names:

<code class="language-json">{
    "cityMap": {"11": "Chennai", "12": "Mumbai", "13": "Delhi"},
    "rows": [
        { "SID": "1",  "SNAME": "ABC", "CITY": "11" },
        { "SID": "2",  "SNAME": "XYZ", "CITY": "12" },
        { "SID": "3",  "SNAME": "ACX", "CITY": "13" },
        { "SID": "4",  "SNAME": "KHG", "CITY": "13" },
        { "SID": "5",  "SNAME": "ADF", "CITY": "12" },
        { "SID": "6",  "SNAME": "KKR", "CITY": "11" }
    ]
}</code>

The client-side jqGrid code leverages the beforeProcessing event to dynamically update column properties using the cityMap. This ensures the correct city names are displayed. The formatter: "select" option isn't directly used here, but the principle of dynamically updating column options at runtime remains the same.

Here's a simplified representation of the client-side code:

<code class="language-javascript">var filterToolbarOptions = {defaultSearch: "cn", stringResult: true, searchOperators: true},
    removeAnyOption = function ($form) { /* ... (function remains unchanged) ... */ },
    $grid = $("#list");

// ... (other code remains largely unchanged) ...

$grid.jqGrid({
    // ... (colModel remains largely unchanged) ...
    beforeProcessing: function (response) {
        // ... (logic to handle cityMap and update column properties) ...
    },
    jsonReader: { id: "SID"}
});
// ... (rest of the code remains largely unchanged) ...</code>

The beforeProcessing function would use the cityMap from the response to replace the numerical city IDs in the rows data with the corresponding city names before jqGrid renders the grid. This approach avoids complex client-side data manipulation and keeps the logic concise.

The above is the detailed content of How to Display Data from Multiple Tables in jqGrid Using a JSON Response with City Mapping?. For more information, please follow other related articles on the PHP Chinese website!