Documentation
Server Side Sorting with jqxGrid using PHP and MySQL
In this help topic, 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 = "";?>
<?php #Include the connect.php fileinclude ('connect.php');// Connect to the database$mysqli = new mysqli($hostname, $username, $password, $database);/* check connection */if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); exit(); }// get data and store in a json array$query = "SELECT OrderDate, ShippedDate, ShipName, ShipAddress, ShipCity, ShipCountry FROM orders";if (isset($_GET['sortdatafield'])) { $sortfields = array( "OrderDate", "ShippedDate", "ShipName", "ShipAddress", "ShipCity", "ShipCountry" ); $sortfield = $_GET['sortdatafield']; $sortorder = $_GET['sortorder']; if (($sortfield != NULL) && (in_array($sortfield, $sortfields))) { if ($sortorder == "desc") { $query = "SELECT OrderDate, ShippedDate, ShipName, ShipAddress, ShipCity, ShipCountry FROM orders ORDER BY " . $sortfield . " DESC"; } else if ($sortorder == "asc") { $query = "SELECT OrderDate, ShippedDate, ShipName, ShipAddress, ShipCity, ShipCountry FROM orders ORDER BY " . $sortfield . " ASC"; } } }$result = $mysqli->prepare($query);$result->execute();/* bind result variables */$result->bind_result($OrderDate, $ShippedDate, $ShipName, $ShipAddress, $ShipCity, $ShipCountry);/* fetch values */while ($result->fetch()) { $orders[] = array( 'OrderDate' => $OrderDate, 'ShippedDate' => $ShippedDate, 'ShipName' => $ShipName, 'ShipAddress' => $ShipAddress, 'ShipCity' => $ShipCity, 'ShipCountry' => $ShipCountry ); }echo json_encode($orders);/* close statement */$result->close();/* close connection */$mysqli->close();?>
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="../jqwidgets/styles/jqx.base.css" type="text/css" /><link rel="stylesheet" href="../jqwidgets/styles/jqx.classic.css" type="text/css" /><meta name="viewport" content="width=device-width, initial-scale=1"><script type="text/javascript" src="../jquery-1.11.1.min.js"></script><script type="text/javascript" src="../jqwidgets/jqxcore.js"></script><script type="text/javascript" src="../jqwidgets/jqxbuttons.js"></script><script type="text/javascript" src="../jqwidgets/jqxscrollbar.js"></script><script type="text/javascript" src="../jqwidgets/jqxmenu.js"></script><script type="text/javascript" src="../jqwidgets/jqxgrid.js"></script><script type="text/javascript" src="../jqwidgets/jqxgrid.selection.js"></script> <script type="text/javascript" src="../jqwidgets/jqxgrid.sort.js"></script> <script type="text/javascript" src="../jqwidgets/jqxdata.js"></script>
<div id="jqxgrid"></div>
<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', 'sort'); } }; // 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>
<!DOCTYPE html><html lang="en"><head> <link rel="stylesheet" href="../jqwidgets/styles/jqx.base.css" type="text/css" /> <link rel="stylesheet" href="../jqwidgets/styles/jqx.classic.css" type="text/css" /> <script type="text/javascript" src="../jquery-1.11.1.min.js"></script> <script type="text/javascript" src="../jqwidgets/jqxcore.js"></script> <script type="text/javascript" src="../jqwidgets/jqxbuttons.js"></script> <script type="text/javascript" src="../jqwidgets/jqxscrollbar.js"></script> <script type="text/javascript" src="../jqwidgets/jqxmenu.js"></script> <script type="text/javascript" src="../jqwidgets/jqxgrid.js"></script> <script type="text/javascript" src="../jqwidgets/jqxgrid.selection.js"></script> <script type="text/javascript" src="../jqwidgets/jqxgrid.sort.js"></script> <script type="text/javascript" src="../jqwidgets/jqxdata.js"></script> <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' } ], cache: false, url: 'data.php', sort: function () { // update the grid and send a request to the server. $("#jqxgrid").jqxGrid('updatebounddata', 'sort'); } }; var dataAdapter = new $.jqx.dataAdapter(source); // initialize jqxGrid $("#jqxgrid").jqxGrid( { source: dataAdapter, 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><script async src="https://www.googletagmanager.com/gtag/js?id=G-2FX5PV9DNT"></script><script>window.dataLayer = window.dataLayer || [];function gtag(){dataLayer.push(arguments);}gtag('js', new Date());gtag('config', 'G-2FX5PV9DNT');</script></head><body class='default'> <div id="jqxgrid"></div></body></html>