Build CRUD Web App with jqxGrid using PHP and MySQL
This help topic shows how to use the jqxGrid in CRUD application scenario and send INSERT, UPDATE and DELETE commands to the server to update a MySQL DataBase.
The first step is to create the file we’ll connect with. We will call the file ‘connect.php’
Now, lets create the file that will handle the queries. We will call the file data.php. The data.php file connects to the ‘Employees’ table from the Northwind Database and returns the data as JSON. It also checks for ‘insert’, ‘delete’ and ‘update’ properties.
Let’s see how the data.php actually works. In the following example we store the connection in a variable ($mysqli) for later use in the script.
//Connect to the database
$mysqli = new mysqli($hostname, $username, $password, $database);
/* check connection */
if (mysqli_connect_errno())
{
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
By default, the code executes a SELECT command which actually populates the jQuery Grid. The example below stores the data returned by the $mysqli->prepare($query) in the $result variable. $result->execute() executes the query. Then with $result->bind_result we bind the result from each returned row to the given variables. It's important the number of the variables to be exact as the number of the returned fields. With $result->fetch() we returns the each row in the Emloyees Table. The while loop loops through all the records in the Employees Table. The result of this query is a JSON data used to populate the Grid.
// get data and store in a json array
$query = "SELECT EmployeeID, FirstName, LastName, Title, Address, City, Country, Notes FROM employees";
// SELECT COMMAND
$result = $mysqli->prepare($query);
$result->execute();
/* bind result variables */
$result->bind_result($EmployeeID, $FirstName, $LastName, $Title, $Address, $City, $Country, $Notes);
/* fetch values */
while ($result->fetch())
{
$employees[] = array(
'EmployeeID' => $EmployeeID,
'FirstName' => $FirstName,
'LastName' => $LastName,
'Title' => $Title,
'Address' => $Address,
'City' => $City,
'Country' => $Country,
'Notes' => $Notes
);
}
echo json_encode($employees);
To insert new records into the Employees Table, we use the INSERT INTO statement. With $mysqli->prepare($query) we prepare the query. With bind_param we give the parameters to the prepared query. The final step is to execute the prepared statement. The $res variable is used to return the result of the operation to the Grid - 1 for success or 0 when some error is occurred.
if (isset($_GET['insert']))
{
// INSERT COMMAND
$query = "INSERT INTO `employees`(`FirstName`, `LastName`, `Title`, `Address`, `City`, `Country`, `Notes`) VALUES (?,?,?,?,?,?,?)";
$result = $mysqli->prepare($query);
$result->bind_param('sssssss', $_GET['FirstName'], $_GET['LastName'], $_GET['Title'], $_GET['Address'], $_GET['City'], $_GET['Country'], $_GET['Notes']);
$res = $result->execute() or trigger_error($result->error, E_USER_ERROR);
// printf ("New Record has id %d.\n", $mysqli->insert_id);
echo $res;
}
To update records, we use the UPDATE statement. The records data is passed to the server in the index.php file.
else if (isset($_GET['update']))
{
// UPDATE COMMAND
$query = "UPDATE `employees` SET `FirstName`=?, `LastName`=?, `Title`=?, `Address`=?, `City`=?, `Country`=?, `Notes`=? WHERE `EmployeeID`=?";
$result = $mysqli->prepare($query);
$result->bind_param('sssssssi', $_GET['FirstName'], $_GET['LastName'], $_GET['Title'], $_GET['Address'], $_GET['City'], $_GET['Country'], $_GET['Notes'], $_GET['EmployeeID']);
$res = $result->execute() or trigger_error($result->error, E_USER_ERROR);
// printf ("Updated Record has id %d.\n", $_GET['EmployeeID']);
echo $res;
}
To delete records, we use the DELETE FROM statement. The EmloyeeID is passed to the server in the index.php file. We delete the records by the EmployeeID.
else if (isset($_GET['delete']))
{
// DELETE COMMAND
$query = "DELETE FROM employees WHERE EmployeeID=?";
$result = $mysqli->prepare($query);
$result->bind_param('i', $_GET['EmployeeID']);
$res = $result->execute() or trigger_error($result->error, E_USER_ERROR);
// printf ("Deleted Record has id %d.\n", $_GET['EmployeeID']);
echo $res;
}
Now, let’s see how the jQuery Grid communicates with the Server. Create a new index.php file and add references to the files below:
<script type="text/javascript" src="jquery-1.11.0.min.js"></script><script type="text/javascript" src="jqxcore.js"></script><script type="text/javascript" src="jqxbuttons.js"></script><script type="text/javascript" src="jqxscrollbar.js"></script><script type="text/javascript" src="jqxmenu.js"></script><script type="text/javascript" src="jqxcheckbox.js"></script><script type="text/javascript" src="jqxlistbox.js"></script><script type="text/javascript" src="jqxdropdownlist.js"></script><script type="text/javascript" src="jqxgrid.js"></script>
In the HTML markup, we add a DIV tag for the Grid with id=”jqxgrid” and three buttons for add, remove and delete of records.
<div> <div style="float: left;" id="jqxgrid"> </div> <div style="margin-left: 30px; float: left;"> <div> <input id="addrowbutton" type="button" value="Add New Row" /> </div> <div style="margin-top: 10px;"> <input id="deleterowbutton" type="button" value="Delete Selected Row" /> </div> <div style="margin-top: 10px;"> <input id="updaterowbutton" type="button" value="Update Selected Row" />
Let’s build our jQuery Grid. At first we need to create the source object that will be used in the Grid’s initialization. The returned data from the server will be in JSON format and we set the datatype member to “json”. Then we set the datafields. Each datafield must have a name member equal to a column’s name in the Employees Table. The url of the connection is the ‘data.php’ file. The source object’s addrow, deleterow and updaterow functions are called when the Grid’s addrow, deleterow or updaterow methods are called. When the jQuery Grid’s addrow method is called, it adds the row locally and then calls the addrow function of the source object. The data that the Grid passes to the addrow function is the new row’s id and the actual row’s data. In the code below, we send the new row’s data to the server. The deleterow and updaterow functions are implemented in a similar way.
var source =
{
datatype: "json",
datafields: [
{ name: 'EmployeeID'},
{ name: 'FirstName'},
{ name: 'LastName'},
{ name: 'Title'},
{ name: 'Address'},
{ name: 'City'},
{ name: 'Country'},
{ name: 'Notes'}
],
id: 'EmployeeID',
url: 'data.php',
addrow: function (rowid, rowdata) {
// synchronize with the server - send insert command
var data = "insert=true&" + $.param(rowdata);
$.ajax({
dataType: 'json',
url: 'data.php',
data: data,
success: function (data, status, xhr) {
// insert command is executed.
}
});
},
deleterow: function (rowid) {
// synchronize with the server - send delete command
var data = "delete=true&EmployeeID=" + rowid;
$.ajax({
dataType: 'json',
url: 'data.php',
data: data,
success: function (data, status, xhr) {
// delete command is executed.
}
});
},
updaterow: function (rowid, rowdata) {
// synchronize with the server - send update command
var data = "update=true&" + $.param(rowdata);
$.ajax({
dataType: 'json',
url: 'data.php',
data: data,
success: function (data, status, xhr) {
// update command is executed.
}
});
}
};
Next, we initialize the Grid and set its source property to the source object.
// initialize jqxGrid
$("#jqxgrid").jqxGrid(
{
width: 700,
height: 350,
source: source,
theme: theme,
columns: [
{ text: 'EmployeeID', datafield: 'EmployeeID', width: 100 },
{ text: 'First Name', datafield: 'FirstName', width: 100 },
{ text: 'Last Name', 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 }
]
});
In the following code, we subscribe to the buttons click event, and call the jQuery Grid’s updaterow, deleterow and addrow methods in the event handlers.
// update row.
$("#updaterowbutton").bind('click', function () {
var datarow = generaterow();
var selectedrowindex = $("#jqxgrid").jqxGrid('getselectedrowindex');
var rowscount = $("#jqxgrid").jqxGrid('getdatainformation').rowscount;
if (selectedrowindex >= 0 && selectedrowindex < rowscount) {
var id = $("#jqxgrid").jqxGrid('getrowid', selectedrowindex);
$("#jqxgrid").jqxGrid('updaterow', id, datarow);
}
});
// create new row.
$("#addrowbutton").bind('click', function () {
var rowscount = $("#jqxgrid").jqxGrid('getdatainformation').rowscount;
var datarow = generaterow(rowscount + 1);
$("#jqxgrid").jqxGrid('addrow', null, datarow);
});
// delete row.
$("#deleterowbutton").bind('click', function () {
var selectedrowindex = $("#jqxgrid").jqxGrid('getselectedrowindex');
var rowscount = $("#jqxgrid").jqxGrid('getdatainformation').rowscount;
if (selectedrowindex >= 0 && selectedrowindex < rowscount) {
var id = $("#jqxgrid").jqxGrid('getrowid', selectedrowindex);
$("#jqxgrid").jqxGrid('deleterow', id);
}
});
The content of the index.php file is listed below: