Click here to Skip to main content
Click here to Skip to main content

Server Side Sorting with jQuery Grid using PHP and MySQL

By , 24 Feb 2012
Rate this:
Please Sign up or sign in to vote.

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:

jquery-grid-server-side-sorting

License

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

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

 
Questionfunction Pinmember98onam29-Mar-12 3:35 
AnswerRe: not getting jgrid Pingroupjqwidgets14-Mar-12 22:06 

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 | Mobile
Web01 | 2.8.140421.2 | Last Updated 24 Feb 2012
Article Copyright 2012 by jqwidgets
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid