Today, we will show how to implement server-side editing with the jqxGrid widget. The Grid will request data from the server when it is initialized. The server-side script is going to deliver the data records in JSON format. We will obtain the data from Northwind Database and especially from the Employees table. You can download the Northwind database .sql script here and run it into MySQL to create the database. You can download the Northwind database .sql script
here and run it into MySQL to create the database. When the user edits the value of a Grid cell, the Grid will make another request to the server containing the updated row’s data. The server-side script will then update the MySQL Database.
The first thing we need to do is create the file we’ll connect with. We’ll call this file connect.php
<?php# FileName="connect.php"$hostname = "localhost";$database = "northwind";$username = "root";$password = "";?>
Now, we need to create the file that will run the query and bring the data so our Grid can be populated. We will call the file data.php.
<?php#Include the connect.php fileinclude('connect.php');#Connect to the database//connection String$connect = mysql_connect($hostname, $username, $password)or die('Could not connect: ' . mysql_error());//Select The database$bool = mysql_select_db($database, $connect);if ($bool === False){ print "can't find $database";}// get data and store in a json array$query = "SELECT * FROM employees";if (isset($_GET['update'])){ // UPDATE COMMAND $update_query = "UPDATE `employees` SET `FirstName`='".$_GET['FirstName']."', `LastName`='".$_GET['LastName']."', `Title`='".$_GET['Title']."', `Address`='".$_GET['Address']."', `City`='".$_GET['City']."', `Country`='".$_GET['Country']."', `Notes`='".$_GET['Notes']."' WHERE `EmployeeID`='".$_GET['EmployeeID']."'"; $result = mysql_query($update_query) or die("SQL Error 1: " . mysql_error()); echo $result;}else{ // SELECT COMMAND $result = mysql_query($query) or die("SQL Error 1: " . mysql_error()); while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { $employees[] = array( 'EmployeeID' => $row['EmployeeID'], 'FirstName' => $row['FirstName'], 'LastName' => $row['LastName'], 'Title' => $row['Title'], 'Address' => $row['Address'], 'City' => $row['City'], 'Country' => $row['Country'], 'Notes' => $row['Notes'] ); } echo json_encode($employees);}?>
In the above code, we check whether there’s an update parameter. If there is, we send an UPDATE command to the MySQL Database. The UPDATE command includes the new cell values of the updated record.
To enable the editing, we need to include the jqxgrid.edit.js file and also to set the Grid’s editable property to true. In the source object’s initialization, we set the updaterow member to a function which makes an ajax call to the server containing the update row. The updaterow function is automatically called by the jqxGrid widget when the user edits the value of a Grid cell.
<!DOCTYPE html><html lang="en"><head> <link rel="stylesheet" href="../jqwidgets/styles/jqx.base.css" type="text/css" /> <link rel="stylesheet" href="../jqwidgets/styles/jqx.classic.css" type="text/css" /> <script type="text/javascript" src="../jquery-1.7.1.min.js"></script> <script type="text/javascript" src="../jqwidgets/jqxcore.js"></script> <script type="text/javascript" src="../jqwidgets/jqxbuttons.js"></script> <script type="text/javascript" src="../jqwidgets/jqxscrollbar.js"></script> <script type="text/javascript" src="../jqwidgets/jqxmenu.js"></script> <script type="text/javascript" src="../jqwidgets/jqxcheckbox.js"></script> <script type="text/javascript" src="../jqwidgets/jqxlistbox.js"></script> <script type="text/javascript" src="../jqwidgets/jqxdropdownlist.js"></script> <script type="text/javascript" src="../jqwidgets/jqxgrid.js"></script> <script type="text/javascript" src="../jqwidgets/jqxdata.js"></script> <script type="text/javascript" src="../jqwidgets/jqxgrid.selection.js"></script> <script type="text/javascript" src="../jqwidgets/jqxgrid.edit.js"></script> <script type="text/javascript"> $(document).ready(function () { // prepare the data var data = {}; var theme = 'classic'; var source = { datatype: "json", datafields: [ { name: 'EmployeeID' }, { name: 'FirstName' }, { name: 'LastName' }, { name: 'Title' }, { name: 'Address' }, { name: 'City' }, { name: 'Country' } ], id: 'EmployeeID', url: 'data.php', updaterow: function (rowid, rowdata) { // synchronize with the server - send update command var data = "update=true&FirstName=" + rowdata.FirstName + "&LastName=" + rowdata.LastName + "&Title=" + rowdata.Title; data = data + "&Address=" + rowdata.Address + "&City=" + rowdata.City + "&Country=" + rowdata.Country + "&Notes=''"; data = data + "&EmployeeID=" + rowdata.EmployeeID; $.ajax({ dataType: 'json', url: 'data.php', data: data, success: function (data, status, xhr) { // update command is executed. } }); } }; // initialize jqxGrid $("#jqxgrid").jqxGrid( { width: 700, height: 350, selectionmode: 'singlecell', source: source, theme: theme, editable: true, columns: [ { text: 'EmployeeID', editable: false, datafield: 'EmployeeID', width: 100 }, { text: 'First Name', columntype: 'dropdownlist', datafield: 'FirstName', width: 100 }, { text: 'Last Name', columntype: 'dropdownlist', datafield: 'LastName', width: 100 }, { text: 'Title', datafield: 'Title', width: 180 }, { text: 'Address', datafield: 'Address', width: 180 }, { text: 'City', datafield: 'City', width: 100 }, { text: 'Country', datafield: 'Country', width: 140 } ] }); }); </script></head><body class='default'> <div id="jqxgrid"> </div></body></html> 