This post 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.
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 = "";?>
2. The second step is to create the file that will handle the queries. We will call the file data.php. The data.php file connects to the ‘Customers’ table from the Northwind Database and returns the data as JSON. Our goal is to send data to client in small pieces that the client requests, and respond when the page number, page size or sort order is changed by the user. In the implementation, we check for the ‘pagenum’ and ‘pagesize’ members which the Grid sends to the server and we use the values of these members to specify the range of records in the query to the database. We also make a query to find the total rows in the ‘Customers’ table. The query depends on the sortfield(Column) and the sortorder(‘ascending’ or ‘descending’). The ‘sortdatafield’ and ‘sortorder’ parameters are passed to the server by jqxGrid. The returned JSON data contains two things- the total rows in the ‘Customers’ table and the requested records.
<?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.7.2.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></head><body class='default'> <div id='jqxWidget'"> <div id="jqxgrid"></div> </div></body></html>
Let’s see how the above code works. As we need to populate the Grid on demand, we set its ‘virtualmode’ property to true. This means that the Grid will display only the records returned as array from the ‘rendergridrows’ callback function. The Grid will make requests to the server when the user clicks the ‘Next’ or ‘Previous’ buttons, changes the page’s size or sorts a column. We also set the source object’s totalrecords property to the TotalRows value returned from the server. In the source object’s initialization we set the ‘datatype’ to ‘json’ as the returned data will be JSON data and the ‘root’ member to ‘Rows’ as the records are stored in the ‘Rows’ array.
