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 ($connect) for later use in the script. The “die” part will be executed if the connection fails:
By default, the code executes a SELECT command which actually populates the jQuery Grid. The example below stores the data returned by the mysql_query() function in the $result variable. Next, we use the mysql_fetch_array() function to return the first row from the Emloyees Table as an array. Each call to mysql_fetch_array() returns the next 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.
To insert new records into the Employees Table, we use the INSERT INTO statement. The mysql_query() function is used to send the $insert_query. The new records data is passed to the server in the index.php file.
To update records, we use the UPDATE statement. The records data is passed to the server in the index.php file.
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.
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.1.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",
cache: false,
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, position, commit) {
// synchronize with the server - send insert command var data =
"insert=true&" + $.param(rowdata);
$.ajax({
dataType:
'json',
url:
'data.php',
data: data,
cache: false,
success:
function (data, status, xhr) {
// insert command is executed. commit(true);
},
error: function(jqXHR, textStatus, errorThrown)
{
commit(false);
}
});
},
deleterow:
function (rowid, commit) {
// synchronize with the server - send delete command var data =
"delete=true&" + $.param({EmployeeID: rowid});
$.ajax({
dataType:
'json',
url:
'data.php',
cache: false,
data: data,
success:
function (data, status, xhr) {
// delete command is executed. commit(true);
},
error: function(jqXHR, textStatus, errorThrown)
{
commit(false);
}
});
},
updaterow:
function (rowid, rowdata, commit) {
// synchronize with the server - send update command var data =
"update=true&" + $.param(rowdata);
$.ajax({
dataType:
'json',
url:
'data.php',
cache: false,
data: data,
success:
function (data, status, xhr) {
// update command is executed. commit(true);
},
error: function(jqXHR, textStatus, errorThrown)
Next, we initialize the Grid and set its source property to the source object.
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.
The content of the index.php file is listed below: