PHP service data binding in DataGrid
We can use PHP web service for grid data binding and CRUD operations. Below code example illustrates how dataManager properties are used to perform CRUD operations in Remote Web services, and configure data by using the URL Adaptor.
$dataManager = new EJ\DataManager();
$dataManager->url(' http://localhost/phpservice/server.php/?action=get_Data ')->updateUrl(http://localhost/phpservice/server.php/?action=update_Data ')->insertUrl(' http://localhost/phpservice/server.php/?action=insert_Data ')->removeUrl(' http://localhost/phpservice/server.php/?action=delrec ')->adaptor('UrlAdaptor');
Data Binding
To bind data to the Grid control, you can use the PHP get_Data Web service method. This method gets the JSON data to be loaded into the grid. While using UrlAdaptor, data should be returned on demand and it should be in form of result and count.
Data binding to grid control
<?php
$col1 = new EJ\Grid\Column();
$col1->field("help_category_id")->headerText("OrderID")->isPrimaryKey(true)->textAlign("right")->width(100);
$col2 = new EJ\Grid\Column();
$col2->field("name")->headerText("CustomerID")->width(70);
$col3 = new EJ\Grid\Column();
$col3->field("parent_category_id")->headerText("EmployeeID")->textAlign("right")->width(70);
$gridColumns = array($col1,$col2,$col3);
$dataManager = new EJ\DataManager();
$dataManager->url(' http://localhost/phpservice/server.php/?action=get_Data ')->updateUrl(http://localhost/phpservice/server.php/?action=update_Data ')->insertUrl(' http://localhost/phpservice/server.php/?action=insert_Data ')->removeUrl(' http://localhost/phpservice/server.php/?action=delrec ')->adaptor('UrlAdaptor');
$grid = new EJ\Grid("Grid");
$toolbarItems = array("add","edit","delete","update","cancel");
$edit =new EJ\Grid\EditSetting();
$toolbar= new EJ\Grid\ToolbarSetting();
echo $grid ->columns($gridColumns)->allowPaging(true)->editSettings($edit->allowEditing(true)->allowDeleting(true)->allowAdding(true))->toolbarSettings($toolbar->showToolbar(true)->toolbarItems($toolbarItems))->allowSorting(true)->allowFiltering(true)->render();
?>
Code snippets for get_Data method
url:"http://localhost/phpservice/server.php/?action=get_Data"
function get_Data(){
header("Content-type:application/json");
$json_param = file_get_contents("php://input");
$params = json_decode($json_param,true);
$sorted = $params['sorted'];
$skip = $params['skip'];
$take = $params['take'];
$sortedArray = [];
$query = "SELECT * FROM Orders";
$countquery = mysql_query("SELECT COUNT(*) FROM Orders");
while ($r = mysql_fetch_array($countquery)) {
$count = $r{0};
}
if($sorted!=null){
$columncount = count($sorted);
for($i=$columncount-1; $i >= 0; $i--){
$svalue = $sorted[$i]['name'];
$firstLetter = substr($sorted[$i]['direction'],0,1);
if($firstLetter == 'a')
$direction = substr($sorted[$i]['direction'],0,3);
else
$direction =substr($sorted[$i]['direction'],0,4);
$tempQuery = $svalue." ".$direction;
array_push($sortedArray,$tempQuery);
}
$query = $query." order by ".join(",",$sortedArray);
}
if($take!=null){
$query = $query." limit ".$skip.",".$take;
}
$result = mysql_query($query);
$json=array();
while ($row = mysql_fetch_array($result)) {
array_push($json,array('OrderID' => $row{'OrderID'}, 'EmployeeID' => $row{'EmployeeID'}, 'CustomerID' => $row{'CustomerID'} ));
}
$response=array("result"=>$json,"count"=>(int)$count);
echo json_encode($response);
}
Insert Data
InsertUrl property will direct to the insert_Data method. It will add new data to the grid and database. The “/? action=insert_Data” section in the URL invokes the Web service method to get the JSON object that contains the data to be inserted to perform the insertion operation.
Code snippets for insert_Data method
insertUrl:"http://localhost/phpservice/server.php/?action=insert_Data"
function insert_Data(){
$json_insert=file_get_contents("php://input");
$obj_insert = json_decode($json_insert,true);
$insertvalue=array();
$insertvalue=$obj_insert['value'];
$oid=(int)$insertvalue['OrderID'];$cid=$insertvalue['CustomerID'];$eid=(int)$insertvalue['EmployeeID'];
$res2=mysql_query("INSERT INTO Orders(OrderID,CustomerID,EmployeeID) VALUES($oid,'$cid',$eid)");
$success=array("Inserted"=>array("Orderid"=>$oid,"Customerid"=>$cid,"Employeeid"=>$eid));
echo json_encode($sucess);
}
Updating Data
UpdateUrl property will direct to the update_Data Web service method to update grid data. This method posts the JSON object that contains the updated information to the database.
Code snippets for update_Data method
updateUrl:"http://localhost/phpservice/server.php/?action=update_Data"
|
function update_Data(){
$json_update=file_get_contents("php://input");
$obj_update = json_decode($json_update,true);
$value=array();
$key=(int)$obj_update['key'];
$value=$obj_update['value'];
$oid=(int)$value['OrderID'];
$cid=$value['CustomerID'];
$eid=(int)$value['EmployeeID'];
$sql="UPDATE Orders SET OrderID=$oid,CustomerID='$cid',EmployeeID=$eid WHERE OrderID=$key";
$res3=mysql_query($sql);
$success=array("Updated"=>array("Orderid"=>$oid,"Customerid"=>$cid,"Employeeid"=>$eid));
echo json_encode($success);
}
Deleting Data
RemoveUrl property will direct to the delrec Web service method to delete grid data. In the server, the “key” to delete the record is fetched from the posted JSON object, and the record is deleted from the database by using the Web service method.
Code example for delrec method
removeUrl:"http://localhost/phpservice/server.php/?action=delrec"
function delrec(){
$json_del=file_get_contents("php://input");
$obj_del = json_decode($json_del,true);
$key=(int)$obj_del['key'];
$res1 = mysql_query("DELETE FROM Orders WHERE OrderID = $key");
$success=array("Deleted"=>$key);
echo json_encode($success);
}
