Documentation

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’ 
<?php
# FileName="connect.php"
$hostname = "localhost";
$database = "northwind";
$username = "root";
$password = "";
?>
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. 
<?php
#Include the connect.php file
include ('connect.php');
// 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();
	}
// get data and store in a json array
$query = "SELECT EmployeeID, FirstName, LastName, Title, Address, City, Country, Notes FROM employees";
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;
	}
  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;
	}
  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;
	}
  else
	{
	// 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);
	}
$result->close();
$mysqli->close();
/* close connection */
?>
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: 
            //connection String
$connect = mysql_connect($hostname, $username, $password)
or die('Could not connect: ' . mysql_error());
The code example below sets the Northwind Database as active on the server. Every subsequent call to mysql_query() will be made on this database.
//Select The database
$bool = mysql_select_db($database, $connect);
if ($bool === False){
   print "can't find $database";
}
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. 
            // get data and store in a json array
$query = "SELECT * FROM employees";
   // SELECT COMMAND
    $result = mysql_query($query) or die("SQL Error 1: " . mysql_error());
    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
        $employees[] = array(
            'EmployeeID' =&gt; $row['EmployeeID'],
            'FirstName' =&gt; $row['FirstName'],
            'LastName' =&gt; $row['LastName'],
            'Title' =&gt; $row['Title'],
            'Address' =&gt; $row['Address'],
            'City' =&gt; $row['City'],
            'Country' =&gt; $row['Country'],
            'Notes' =&gt; $row['Notes']
          );
    }
 
    echo json_encode($employees);
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. 
            if (isset($_GET['insert']))
{
    // INSERT COMMAND
    $insert_query = "INSERT INTO `employees`(`FirstName`, `LastName`, `Title`, `Address`, `City`, `Country`, `Notes`) VALUES ('".$_GET['FirstName']."','".$_GET['LastName']."','".$_GET['Title']."','".$_GET['Address']."','".$_GET['City']."','".$_GET['Country']."','".$_GET['Notes']."')";
 
   $result = mysql_query($insert_query) or die("SQL Error 1: " . mysql_error());
   echo $result;
}
To update records, we use the UPDATE statement. The records data is passed to the server in the index.php file. 
            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;
}
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. 
            if (isset($_GET['delete']))
{
    // DELETE COMMAND
    $delete_query = "DELETE FROM `employees` WHERE `EmployeeID`='".$_GET['EmployeeID']."'";
    $result = mysql_query($delete_query) or die("SQL Error 1: " . mysql_error());
    echo $result;
}

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" />
        </div>
    </div>
</div>
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&amp;" + $.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&amp;" + $.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&amp;" + $.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)
			{
				commit(false);
			}							
		});		
}
};
Next, we initialize the Grid and set its source property to the source object. 
            var dataAdapter = new $.jqx.dataAdapter(source);
// initialize jqxGrid
$("#jqxgrid").jqxGrid(
{
    width: 500,
    height: 350,
    source: dataAdapter,
    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. 
            $("#addrowbutton").jqxButton({ theme: theme });
$("#deleterowbutton").jqxButton({ theme: theme });
$("#updaterowbutton").jqxButton({ theme: theme });
// 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: 
<!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" />
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <script type="text/javascript" src="../../scripts/jquery-1.11.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">
        $(document).ready(function () {
            // prepare the data
            var data = {};
            var theme = 'classic';
            var firstNames = ["Nancy", "Andrew", "Janet", "Margaret", "Steven", "Michael", "Robert", "Laura", "Anne"];
            var lastNames = ["Davolio", "Fuller", "Leverling", "Peacock", "Buchanan", "Suyama", "King", "Callahan", "Dodsworth"];
            var titles = ["Sales Representative", "Vice President, Sales", "Sales Representative", "Sales Representative", "Sales Manager", "Sales Representative", "Sales Representative", "Inside Sales Coordinator", "Sales Representative"];
            var address = ["507 - 20th Ave. E. Apt. 2A", "908 W. Capital Way", "722 Moss Bay Blvd.", "4110 Old Redmond Rd.", "14 Garrett Hill", "Coventry House", "Miner Rd.", "Edgeham Hollow", "Winchester Way", "4726 - 11th Ave. N.E.", "7 Houndstooth Rd."];
            var city = ["Seattle", "Tacoma", "Kirkland", "Redmond", "London", "London", "London", "Seattle", "London"];
            var country = ["USA", "USA", "USA", "USA", "UK", "UK", "UK", "USA", "UK"];
            var generaterow = function (id) {
                var row = {};
                var firtnameindex = Math.floor(Math.random() * firstNames.length);
                var lastnameindex = Math.floor(Math.random() * lastNames.length);
                var k = firtnameindex;
                row["EmployeeID"] = id;
                row["FirstName"] = firstNames[firtnameindex];
                row["LastName"] = lastNames[lastnameindex];
                row["Title"] = titles[k];
                row["Address"] = address[k];
                row["City"] = city[k];
                row["Country"] = country[k];
                row["Notes"] = row["FirstName"] + ' received a BA in computer science from the University of Washington';
                return row;
            }
            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) {
                            commit(false);
                        }
                    });
                }
            };
            var dataAdapter = new $.jqx.dataAdapter(source);
            // initialize jqxGrid
            $("#jqxgrid").jqxGrid(
            {
                width: 500,
                height: 350,
                source: dataAdapter,
                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 }
                  ]
            });
            $("#addrowbutton").jqxButton({ theme: theme });
            $("#deleterowbutton").jqxButton({ theme: theme });
            $("#updaterowbutton").jqxButton({ theme: theme });
            // 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);
                }
            });
        });
    </script>
<script async src="https://www.googletagmanager.com/gtag/js?id=G-2FX5PV9DNT"></script><script>window.dataLayer = window.dataLayer || [];function gtag(){dataLayer.push(arguments);}gtag('js', new Date());gtag('config', 'G-2FX5PV9DNT');</script></head>
<body class='default'>
    <div id='jqxWidget' style="font-size: 13px; font-family: Verdana; float: left;">
        <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" />
            </div>
        </div>
    </div>
</body>
</html>