Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

Build CRUD Web App with jQuery Grid using PHP and MySQL

, 26 Jan 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
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 … Continue reading →

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, 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
//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 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' => $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 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.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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

jqwidgets
jQWidgets
United States United States
jQWidgets specializes in the development of platform independent and cross-browser compatible presentation layer components for building modern web-based applications for PC, Touch and Mobile. Our product provides developers with the essential set of User Interface widgets needed to streamline their development and reduce project costs.
Our goal is to help our customers deliver better web-based applications that can be accessed through any device and are pleasure to use.
Group type: Organisation

13 members

Follow on   Twitter

Comments and Discussions

 
GeneralMy vote of 5 Pinmembermanoj kumar choubey29-Aug-12 0:23 
Questionjs files Pinmember98onam29-Mar-12 23:42 
AnswerRe: js files Pingroupjqwidgets29-Mar-12 23:44 
You can download the full pack of files from here: http://www.jqwidgets.com/download/[^]

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141220.1 | Last Updated 26 Jan 2012
Article Copyright 2012 by jqwidgets
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid