Server Side Grid Paging and Filtering
This help topic shows how to implement server-side paging and filtering 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 filter. 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 Orders 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’
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 ‘Orders’ 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 filter expression 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 ‘Orders’ table. The returned JSON data contains two things- the total rows in the ‘Orders’ table and the requested records.
In the data.php, we also check the value of the filterscount property. If there's a filter expression, the filterscount value will be greater than 0. Then we iterate through
all of the applied filters.
The following code gets the value of the filter with index i.
$filtervalue = $_GET["filtervalue" . $i];
The following code gets the condition('Contains', 'Starts With', etc.) of the current filter.
$filtercondition = $_GET["filtercondition" . $i];
The current filter's column is stored in the 'filterdatafield' property.
$filterdatafield = $_GET["filterdatafield" . $i];
The filter operator specifies the relation of the current filter with the filters from the same column. Its value can be 0 for "AND" and 1 for "OR".
For example, if you have multiple filters applied to the same column the filter expression could mean the following ( filter all records in the 'First Name' column that start with 'a' and end with 'b'). The 'and' keyword is the filter operator.
$filteroperator = $_GET["filteroperator" . $i];
Depending on the value of the $filtercondition property, we build the query's WHERE clause.
3. The final step is to create the Grid and bind it to the ‘Orders’ table.
Here’s the full source code:
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 applies a filter. 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.