Documentation
Server Side Grid Paging and Sorting
This help topic shows how to implement server-side paging and sorting with the jqxGrid widget. The Grid will request data from the server when the user navigates to a new page, changes the page's size or applies a sort order. The server-side script is going to deliver the data records in JSON format. We will obtain the data from Northwind Database and especially from the Customers table. You can download the Northwind database .sql script here and run it into MySQL to create the database.By default, the Grid sends the following data to the server:
- sortdatafield - the sort column's datafield.
- sortorder - the sort order - 'asc', 'desc' or ''
- pagenum - the current page's number when the paging feature is enabled.
- pagesize - the page's size which represents the number of rows displayed in the view.
- groupscount - the number of groups in the Grid
- group - the group's name. The group's name for the first group is 'group0', for the second group is 'group1' and so on.
- filterscount - the number of filters applied to the Grid
- filtervalue - the filter's value. The filtervalue name for the first filter is "filtervalue0", for the second filter is "filtervalue1" and so on.
- filtercondition - the filter's condition. The condition can be any of these: "CONTAINS", "DOES_NOT_CONTAIN", "EQUAL", "EQUAL_CASE_SENSITIVE", NOT_EQUAL","GREATER_THAN", "GREATER_THAN_OR_EQUAL", "LESS_THAN", "LESS_THAN_OR_EQUAL", "STARTS_WITH", "STARTS_WITH_CASE_SENSITIVE", "ENDS_WITH", "ENDS_WITH_CASE_SENSITIVE", "NULL", "NOT_NULL", "EMPTY", "NOT_EMPTY"
- filterdatafield - the filter column's datafield
- filteroperator - the filter's operator - 0 for "AND" and 1 for "OR"
1. The first step is to create the file we’ll connect with. We will call the file ‘connect.php’
<?php# FileName="connect.php"$hostname = "localhost";$database = "northwind";$username = "root";$password = "";?>
<?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"; } // get data and store in a json array $pagenum = $_GET['pagenum']; $pagesize = $_GET['pagesize']; $start = $pagenum * $pagesize; $query = "SELECT SQL_CALC_FOUND_ROWS * FROM customers LIMIT $start, $pagesize"; if (isset($_GET['sortdatafield'])) { $sortfield = $_GET['sortdatafield']; $sortorder = $_GET['sortorder']; $result = mysql_query($query) or die("SQL Error 1: " . mysql_error()); $sql = "SELECT FOUND_ROWS() AS `found_rows`;"; $rows = mysql_query($sql); $rows = mysql_fetch_assoc($rows); $total_rows = $rows['found_rows']; if ($sortfield != NULL) { if ($sortorder == "desc") { $query = "SELECT * FROM customers ORDER BY" . " " . $sortfield . " DESC LIMIT $start, $pagesize"; } else if ($sortorder == "asc") { $query = "SELECT * FROM customers ORDER BY" . " " . $sortfield . " ASC LIMIT $start, $pagesize"; } $result = mysql_query($query) or die("SQL Error 1: " . mysql_error()); } } else { $result = mysql_query($query) or die("SQL Error 1: " . mysql_error()); $sql = "SELECT FOUND_ROWS() AS `found_rows`;"; $rows = mysql_query($sql); $rows = mysql_fetch_assoc($rows); $total_rows = $rows['found_rows']; } while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { $customers[] = array( 'CompanyName' => $row['CompanyName'], 'ContactName' => $row['ContactName'], 'ContactTitle' => $row['ContactTitle'], 'Address' => $row['Address'], 'City' => $row['City'], 'Country' => $row['Country'] ); } $data[] = array( 'TotalRows' => $total_rows, 'Rows' => $customers ); echo json_encode($data);?>
3. The final step is to create the Grid and bind it to the ‘Customers’ table.
<!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="../../scripts/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/jqxcheckbox.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxlistbox.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxdropdownlist.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxgrid.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxgrid.pager.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: 'CompanyName' }, { name: 'ContactName' }, { name: 'ContactTitle' }, { name: 'Address' }, { name: 'City' }, { name: 'Country' } ], url: 'data.php', root: 'Rows', beforeprocessing: function (data) { source.totalrecords = data[0].TotalRows; }, sort: function () { // update the grid and send a request to the server. $("#jqxgrid").jqxGrid('updatebounddata'); } }; var dataadapter = new $.jqx.dataAdapter(source); // initialize jqxGrid $("#jqxgrid").jqxGrid( { width: 600, source: dataadapter, theme: theme, autoheight: true, pageable: true, virtualmode: true, sortable: true, rendergridrows: function () { return dataadapter.records; }, columns: [ { text: 'Company Name', datafield: 'CompanyName', width: 250 }, { text: 'Contact Name', datafield: 'ContactName', width: 200 }, { text: 'Contact Title', datafield: 'ContactTitle', width: 200 }, { text: 'Address', datafield: 'Address', width: 180 }, { text: 'City', datafield: 'City', width: 100 }, { text: 'Country', datafield: 'Country', 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='jqxWidget'"> <div id="jqxgrid"></div> </div></body></html>