Server Side Sorting with jQuery Grid using PHP and MySQL

Today, we will show you how to implement server-side sorting with the jqxGrid widget. The Grid will request data from the server for every change of the jqxGrid’s sorting state. The server-side script is going to deliver the data records in JSON format depending on the sorting state. We will obtain the data from Northwind database and especially from the Orders table. You can download the Northwind database .sql script here and run it into MySQL to create the database.

The first thing we need to do is create the file we’ll connect with. We’ll call this file connect.php.

<?php
# FileName="connect.php"
$hostname = "localhost";
$database = "northwind";
$username = "root";
$password = "";
?>

Now we need to create the file that will run the query and bring the data so our Grid can be populated. We will call the file data.php.

<?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";
    }
    $query = "SELECT * FROM orders";
    // sort data.
    if (isset($_GET['sortdatafield']))
    {
        $sortfield = $_GET['sortdatafield'];
        $sortorder = $_GET['sortorder'];

        if ($sortfield != NULL)
        {
            if ($sortorder == "desc")
            {
                $query = "SELECT * FROM orders ORDER BY" . " " . $sortfield . " DESC";
            }
            else if ($sortorder == "asc")
            {
                $query = "SELECT * FROM orders ORDER BY" . " " . $sortfield . " ASC";
            }
        }
    }
    $result = mysql_query($query) or die("SQL Error 1: " . mysql_error());
    // get data and store in a json array
    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
        $orders[] = array(
            'OrderDate' => $row['OrderDate'],
            'ShippedDate' => $row['ShippedDate'],
            'ShipName' => $row['ShipName'],
            'ShipAddress' => $row['ShipAddress'],
            'ShipCity' => $row['ShipCity'],
            'ShipCountry' => $row['ShipCountry']
          );
    }
    echo json_encode($orders);
?>

In the above code, we create a query depending on the sortfield (column) and the sortorder (‘ascending’ or ‘descending’). The ‘sortdatafield’ and ‘sortorder’ parameters are passed to the server by jqxGrid. Then we execute the query and build an array of Orders which we return in JSON format.

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:

<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="jqxgrid.js"></script>
<script type="text/javascript" src="jqxgrid.selection.js"></script>
<script type="text/javascript" src="jqxgrid.sort.js"></script>
<script type="text/javascript" src="jqxdata.js"></script>

In the HTML markup, we add a DIV tag for the Grid with id=”jqxgrid”.

<div id="jqxgrid"></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 name from the Orders Table. The URL of the connection is the ‘data.php’ file. In order to enable the server-side sorting, we need to add a sort member. We set it to point to a function that calls the jqxGrid’s ‘updatebounddata’ function. The ‘updatebounddata’ function requests the Grid to update its data records, i.e., this function will make a server call and will pass the sort column and sort order parameters to the server. Finally, we initialize the Grid by selecting the DIV tag with id=’jqxgrid’ and calling the jqxGrid constructor.

<script type="text/javascript">
    $(document).ready(function () {
        // prepare the data
        var theme = 'classic';

        var source =
        {
             datatype: "json",
             datafields: [
                 { name: 'ShippedDate'},
                 { name: 'ShipName'},
                 { name: 'ShipAddress'},
                 { name: 'ShipCity'},
                 { name: 'ShipCountry'}
            ],
            url: 'data.php',
            sort: function()
            {
                // update the grid and send a request to the server.
                $("#jqxgrid").jqxGrid('updatebounddata');
            }
        };        

        // initialize jqxGrid
        $("#jqxgrid").jqxGrid(
        {
            source: source,
            theme: theme,
            sortable: true,
            sorttogglestates: 1,
            columns: [
                  { text: 'Shipped Date', datafield: 'ShippedDate', cellsformat: 'd', width: 200 },
                  { text: 'Ship Name', datafield: 'ShipName', width: 200 },
                  { text: 'Address', datafield: 'ShipAddress', width: 180 },
                  { text: 'City', datafield: 'ShipCity', width: 100 },
                  { text: 'Country', datafield: 'ShipCountry', width: 140 }
              ]
        });
    });
</script>

Here is the result: