This weekend while experimenting with the javascript grid jqgrid, I decided to try out using a full grid as a subgrid.
Its really not that difficult given that the wiki on the jqgrid website shows how to do this. I just needed to tweak a few options to get it to work;
here is my code (php mysql back end):
1st, the html page
<!DOCTYPE html PUBLIC &quot;-//W3C//DTD HTML 4.01 Transitional//EN&quot; &quot;http://www.w3.org/TR/html4/loose.dtd&quot;> <html> <head> <title>JQGrid Test</title> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <link rel="stylesheet" type="text/css" href="../dojoproject/jquery-ui-1.8.16.custom/css/start/jquery-ui-1.8.16.custom.css"> <link rel="stylesheet" type="text/css" href="jquery.jqGrid-4.3.1/css/ui.jqgrid.css"> <style type="text/css"> html, body { margin: 0; padding: 0; font-size: 90%; } </style> <script type="text/javascript" src="../dojoproject/jquery-ui-1.8.16.custom/js/jquery-1.6.2.min.js" ></script> <script type="text/javascript" src="../dojoproject/jquery-ui-1.8.16.custom/js/jquery-ui-1.8.16.custom.min.js" ></script> <script type="text/javascript" src="jquery.jqGrid-4.3.1/js/i18n/grid.locale-en.js" ></script> <script type="text/javascript" src="jquery.jqGrid-4.3.1/js/jquery.jqGrid.min.js" ></script> <script type="text/javascript" src="../dojoproject/jqGrid-4.1.2/js/JSON-js/json2.js" ></script> <script> $(function() { $('#table').jqGrid({ jsonReader : { repeatitems: false, cell:"", id:"0" }, height:'auto', url:'/jqgrid/orders.php', datatype: 'json', mtype: 'GET', rownumbers:true, rownumWidth:35, colNames:['OrderID','CustomerID','EmployeeID','OrderDate','ShipVia','Freight','ShipName','ShipAddress'], colModel :[ {name:'OrderID', index:'OrderID',search:false,sorttype:'integer'}, {name:'CustomerID', index:'CustomerID'}, {name:'EmployeeID', index:'EmployeeID'}, {name:'OrderDate', index:'OrderDate'}, {name:'ShipVia', index:'ShipVia'}, {name:'Freight', index:'Freight'}, {name:'ShipName', index:'ShipName'}, {name:'ShipAddress', index:'ShipAddress'}, ], sortname: 'OrderID ', rowNum:10, sortorder: 'asc', width:'100%', height:'200', viewrecords: true, gridview: true, caption: 'NorthWind Orders', scrollOffset:18, multiselect:true, pager:'pager' ,cellEdit:true, cellsubmit:'clientArray', afterSaveCell:function(rowid, cellname, value, iRow, iCol){ }, subGrid:true, subGridRowExpanded: function(subgrid_id, row_id) { var subgrid_table_id; subgrid_table_id = subgrid_id+"_t"; $("#"+subgrid_id).html("<table id='"+subgrid_table_id+"' class='scroll'></table>"); $("#"+subgrid_table_id).jqGrid({ jsonReader : { repeatitems: false, cell:"", id:"0" }, url:"order_details.php?id="+row_id, datatype: "json", colNames: ['OrderID','ProductID','UnitPrice','Quantity','Discount'], colModel: [ {name:"OrderID",index:"OrderID",width:80,}, {name:"ProductID",index:"ProductID",width:130}, {name:"UnitPrice",index:"UnitPrice",width:80,align:"right"}, {name:"Quantity",index:"Quantity",width:80,align:"right"}, {name:"Discount",index:"Discount",width:100,align:"right"} ], height: '100%', rowNum:20, }); } }); }); </script> </head> <body> <table id='table'></table> <div id='pager'></div> </body> </html>
Then, the orders.php
<?php header('Content-type: application/json'); Class jqGridObject { public $page; public $total; public $records; public $rows; } $page = $_GET['page']; $rows = $_GET['rows']; $arrayOrders = array(); $mysqli = new mysqli('localhost','root','','northwind'); if ($mysqli->connect_error) { die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error); }; $resultAll = $mysqli->query('select OrderID,CustomerID,EmployeeID,OrderDate,ShipVia,Freight,ShipName, ShipAddress from orders'); $records = $resultAll->num_rows; $total = ceil($records/$rows); $skip = $page*$rows - $rows; $queryLtd ='select OrderID,CustomerID,EmployeeID,OrderDate,ShipVia,Freight,ShipName, ShipAddress from orders LIMIT '.$skip.','.$rows; if (!$result = $mysqli->query($queryLtd)) { echo "error\n"; } else { while ($obj = $result->fetch_object()) { array_push($arrayOrders,$obj); }; $myjqGrid = new jqGridObject(); $myjqGrid->total = $total; $myjqGrid->page = $page; $myjqGrid->records = $records; $myjqGrid->rows = $arrayOrders; echo json_encode($myjqGrid); } ?>
Then order_details.php
<?php header('Content-type: application/json'); Class jqGridObject { public $page; public $total; public $records; public $rows; } $id = $_GET['id'] ; $rows = $_GET['rows']; $page = $_GET['page']; $arrayDetails = array(); $mysqli = new mysqli('localhost','root','','northwind'); if ($mysqli->connect_error) { die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error); }; $resultAll = $mysqli->query('select OrderID,ProductID,UnitPrice,Quantity,Discount from order_details where orderid = '.$id); $records = $resultAll->num_rows; $total = ceil($records/$rows); $skip = $page*$rows - $rows; $queryLtd = 'select OrderID,ProductID,UnitPrice,Quantity, Discount from order_details where orderid = '.$id.' LIMIT '.$skip.','.$rows; if (!$result = $mysqli->query($queryLtd)) { echo "error\n"; } else { while ($obj = $result->fetch_object()) { array_push($arrayDetails,$obj); } $myjqGrid = new jqGridObject(); $myjqGrid->total = $total; $myjqGrid->page = $page; $myjqGrid->page = $page; $myjqGrid->records = $records; $myjqGrid->rows = $arrayDetails; echo json_encode($myjqGrid); } ?> </div> <div> </div>
Evie Barroga
August 26, 2013 at 1:03 am
Hi I see you posted this a while ago, but currently I am trying to implement server-side paging using mysqli and php with jqgrid and I am in need of some help, do you think you can lend a hand please?
I appreciate your time, I left my e-mail in case you decide to help.
Thanks!
curiouskoder
August 26, 2013 at 7:35 am
Sure just send me the details (code/questions) , I’ll see what I can do –
john does
November 29, 2013 at 1:30 am
Hi. I was wondering whether i can possibly freeze a column with jqgrid while enabling subgrid?
i know that the documentation said it cannot. But maybe you have an alternative. Thanks
curiouskoder
February 9, 2014 at 10:25 pm
Hi – try asking this on StackOverflow – there are some folks that really know jqgrid inner workings (there’s one called Oleg)
Tim Hunold
January 13, 2014 at 4:47 pm
Hey sport, I have some questions. I am trying to go up to 4 layers deep, and the parent can be either a select box or an actual table. Kinda crazy, sure could use some help.
curiouskoder
February 9, 2014 at 10:26 pm
Hi – try asking this on StackOverflow – there are some folks that really know jqgrid inner workings (there’s one called Oleg) – let me know if you are successful
ALso please be more specific with your requirements
Kris
February 9, 2014 at 7:35 am
Hi,
This was a nice post, Can you also please let me know if you can give me a sample of jqgrid with multiple subgrids of same level.. For example I want to show list of School Districts on the main grid and once we click on the main grid row, it should show the sub grids like users list, List of Addresses associated to that school district. Your help is highly appreciated.
curiouskoder
February 9, 2014 at 10:30 pm
So you need more of tree view ; where you have school districts with 2 child nodes (addresses and users) ; with the respective lists of addresses and users under each node?
Mustafa
August 21, 2014 at 4:58 am
I’ve done everything written in this example but when i run it data is not showing in the grid.
curiouskoder
August 21, 2014 at 11:19 pm
Made one correction – try again I had one array named incorrectly
Martin
February 8, 2016 at 3:38 pm
Perfect, works like a charm!! Thanks