1. We will enable editing on both grid and subgrid.
2. We will add a aditional parameter to the subgrid (from parent) when a new record is inserted in order to leave the tables in sync
3. When we delete a record in main grid a associated records from the child grid will be deleted.
4. We will use some of the additional function that jqGrid database driver include
In order to achive this we will need to create two files - one for the main grid and another for the child grid.
After every file we will put coments what is happen
grid.php code
require_once 'jq-config.php'; // include the jqGrid Class require_once "php/jqGrid.php"; // include the driver class require_once "php/jqGridPdo.php"; // Connection to the server $conn = new PDO(DB_DSN,DB_USER,DB_PASSWORD); // Tell the db that we use utf-8 $conn->query("SET NAMES utf8"); // Create the jqGrid instance $grid = new jqGridRender($conn); // Write the SQL Query $grid->SelectCommand = 'SELECT CustomerID, CompanyName, ContactName, Phone, City FROM customers'; // Set the table to where you update the data $grid->table = 'customers'; $grid->setPrimaryKeyId('CustomerID'); $grid->serialKey = false; // Set output format to json $grid->dataType = 'json'; // Let the grid create the model $grid->setColModel(); // Set the url from where we obtain the data $grid->setUrl('grid.php'); // Set some grid options $grid->setGridOptions(array( "rowNum"=>10, "height"=>250, "rowList"=>array(10,20,30), "sortname"=>"CustomerID" )); $grid->setColProperty('CustomerID', array("label"=>"ID", "width"=>50)); $grid->setSubGridGrid("subgrid.php"); // Enable navigator $grid->navigator = true; // disable the delete operation programatically for that table $grid->del = true; // we need to write some custom code when we are in delete mode. // get the grid oper parameter to see if we are in delete mode // jqGrid send oper parameter to identify the needed action $deloper = $_POST['oper']; // det the customer id $custid = $_POST['CustomerID']; // if the operation is del and the customerid is set if($deloper == 'del' && isset($custid) ) { // the two tables are linked via CustomerID, so let try to delete the records on both tables try { jqGridDB::beginTransaction($conn); $cust = jqGridDB::prepare($conn, "DELETE FROM customers WHERE CustomerID= ?", array($custid)); $order = jqGridDB::prepare($conn,"DELETE FROM orders WHERE CustomerID = ?", array($custid)) jqGridDB::execute($cust); jqGridDB::execute($order); jqGridDB::commit($conn); } catch(Exception $e) { jqGridDB::rollBack($conn); echo $e->getMessage(); } } $grid->renderGrid('#grid','#pager',true, null, null, true,true); $conn = null; ?>
The second thing what we do here is to disable the delete operation to be accomplished from the renderer class. This is done via
$grid->del = true;
In this case we should manually determine if we are in delete mode. Since jqGrid do this via oper parameter send to server we obtain this
parameter. If the parameter oper tell us that we are in delete mode we write your own delete queries. Additionallly to that we should known which customer should be deleted.
This is done with the following lines of code
$deloper = $_POST['oper'];
// det the customer id
$custid = $_POST['CustomerID'];
// if the operation is del and the customerid is set
if($deloper == 'del' && isset($custid) )
{
...
At end we enclose the deletion in transaction deleting the desired records on both tables in order to make the tables in sync.
So lets look at the subgrid code
subgrid.php
<?php require_once 'jq-config.php'; // include the jqGrid Class require_once "php/jqGrid.php"; // include the driver class require_once "php/jqGridPdo.php"; // Connection to the server $conn = new PDO(DB_DSN,DB_USER,DB_PASSWORD); // Tell the db that we use utf-8 $conn->query("SET NAMES utf8"); // Get the needed parameters passed from the main grid // By default we add to postData subgrid and rowid parameters in the main grid $subtable = jqGridUtils::Strip($_REQUEST["subgrid"]); $rowid = jqGridUtils::Strip($_REQUEST["rowid"]); // Create the jqGrid instance $grid = new jqGridRender($conn); // Write the SQL Query $grid->SelectCommand = "SELECT OrderID, RequiredDate, ShipName, ShipCity, Freight FROM orders WHERE CustomerID = ?"; // set the ouput format to json $grid->dataType = 'json'; //set a table $grid->table = "orders"; $grid->setPrimaryKeyId("OrderID"); // Let the grid create the model $grid->setColModel(null,array(&$rowid)); // Set the url from where we obtain the data $grid->setUrl('subgrid.php'); // Set some grid options $grid->setGridOptions(array( "width"=>540, "rowNum"=>10, "sortname"=>"OrderID", "height"=>110, "postData"=>array("subgrid"=>$subtable,"rowid"=>$rowid)) ); // set a option when we add a record to insert a CustomerID too. $grid->setNavOptions("add",array("editData"=>array("CustomerID"=>$rowid))); // Change some property of the field(s) $grid->setColProperty("RequiredDate", array( "formatter"=>"date", "formatoptions"=>array("srcformat"=>"Y-m-d H:i:s","newformat"=>"m/d/Y"), "search"=>false ) ); // enable editing $grid->navigator = true; $subtable = $subtable."_t"; $pager = $subtable."_p"; $grid->renderGrid($subtable,$pager, true, null, array(&$rowid), true,true); $conn = null; ?>
In the subgrid code there is nothing special except this line of code
$grid->setNavOptions("add",array("editData"=>array("CustomerID"=>$rowid)));
With this line we set additional parameter when we are in add mode and this is the
id of the Customer. The variable is posted and inserted succesfully.