Click here to Skip to main content
13,193,600 members (63,435 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


Posted 24 Feb 2012

Server Side Sorting with jQuery Grid using PHP and MySQL

, 24 Feb 2012
Rate this:
Please Sign up or sign in to vote.
How to implement server-side sorting with the jqxGrid widget

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.

# 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.

    #Include the connect.php file
    #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.

        // initialize 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 }

Here is the result:



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


About the Author

United States United States
This member doesn't quite have enough reputation to be able to display their biography and homepage.
Group type: Organisation

13 members

You may also be interested in...


Comments and Discussions

Questionfunction Pin
98onam29-Mar-12 3:35
member98onam29-Mar-12 3:35 
AnswerRe: not getting jgrid Pin
jqwidgets14-Mar-12 22:06
groupjqwidgets14-Mar-12 22:06 
You can download the full source code of the article from here:[^]. The source code implementation is in the php_demos folder in the archive.
You can download the Northwind Database used in the article from here:[^]

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.171018.2 | Last Updated 24 Feb 2012
Article Copyright 2012 by jqwidgets
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid