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
$connect = mysql_connect($hostname, $username, $password)
or die('Could not connect: ' . mysql_error());
$bool = mysql_select_db($database, $connect);
if ($bool === False){
print "can't find $database";
}
$query = "SELECT * FROM orders";
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());
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 datafield
s. 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 () {
var theme = 'classic';
var source =
{
datatype: "json",
datafields: [
{ name: 'ShippedDate'},
{ name: 'ShipName'},
{ name: 'ShipAddress'},
{ name: 'ShipCity'},
{ name: 'ShipCountry'}
],
url: 'data.php',
sort: function()
{
$("#jqxgrid").jqxGrid('updatebounddata');
}
};
$("#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: