Build CRUD Web App with jQuery Grid using PHP and MySQL

In this post, we will show you how to use the jQuery Grid 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, let's 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
//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['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;
}
else 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 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;
}
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);
}
?>

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 Employees Table as an array. Each call to mysql_fetch_array() returns the next row in the Employees 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' => $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);

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 EmployeeID 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.7.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",
     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:

<!DOCTYPE html>
<html lang="en">
<head>
    <link rel="stylesheet" href="styles/jqx.base.css" type="text/css" />
    <link rel="stylesheet" href="styles/jqx.classic.css" type="text/css" />
    <script type="text/javascript" src="jquery-1.7.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>
    <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",
                 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.
                            }
                        });
                }
            };
            // 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 }
                  ]
            });

            $("#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>
</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>

Below is a screenshot of the jQuery Grid populated with data.

CodeProject