This example page introduces paging and sorting for a listGrid using a remote data source.

This example is part 2 of a series of introductory examples about remote data sources. It builds upon the Introduction to remote dataSource with listGrid.

We will talk here about how you can page and sort your data that originates from a remote database. We will start with paging. Probable you were be convinced of the need for paging when you tried to press the button that caused the listGrid to load into the Introduction to remote dataSource with listGrid example page:

• It took several seconds until the listGrid was loaded.
• It is inconvenient to have 250 rows displayed in a page.

If you are using a local data source, there are two things that you have to do to make the listGrid capable of being paged and sorted:

1. Add a pager widget to the page, like this:
   <b:pagerBar>
      <b:pager for="id('pagingGrid')" width="250px" align="center">
         <b:pagerButton type="First" />
         <b:pagerButton type="Previous" />
         <b:pagerSeparator />
         <b:pagerJumper />
         <b:pagerSeparator />
         <b:pagerButton type="Next" />
         <b:pagerButton type="Last" />
      </b:pager>
   </b:pagerBar>
                  
   Snippet 1: A pager for the listGrid
   The for attribute should target the listGrid, in our example by using matching id's.
2. Specify how many rows you want to display in the listGrid. The listGrid will look like this:
   <b:listGrid dataSource="myData" id="pagingGrid" rows="10"
                rowClasses="rowClass1, rowClass2" readonly="true">
           <b:listGridCol label="Rank" select="rank"/>
           <b:listGridCol label="Rating" select="rating"/>
           <b:listGridCol label="Title" select="title"/>
           <b:listGridCol label="Year" select="year"/>
           <b:listGridCol label="Votes" select="votes"/>
           <b:listGridCol label="Director" select="director"/>
           <b:listGridCol label="Genre" select="genre"/>
   </b:listGrid>
                  
   Snippet 2: The listGrid, requesting 10 rows to be displayed
   

If you try this code without changing the remote data source, you will see that it does not work: all 250 rows are loaded, and the pager stays empty.

The not-so Local Data Source

If the data you want to display is not too large, it may be acceptable to load your data source as local data, using xi:include. We could define a local data source like this:

As you can see, we are using the same remoteDataSource3.php script to load all rows from the table. The live example below shows the result:

Example 1: Movies from a not-so local data source

You may notice that this example loads significantly faster than the last example in the Introduction to remote dataSource with listGrid example page. The reason for this is that the browser has to render only 10 rows instead of 250 rows. As said, in some cases this may suffice, but as the amount of data grows, the need for paging performed at the server increases. In the next section we will explain how to do that.

In addition to the paging that now works, you may have noticed that sorting is also working properly for this example.

The Remote Data Source that can accept Paging Requests

When a remote data source sends a request while a pager is active, the request for data will contain some parameters that allow us to return the right set of rows. For paging data, the parameters are: page and rows.

We made a copy of remoteDataSource3.php into remoteDataSource4.php and we made some changes to use the new parameters:

Let us go through the code and explain some points:

• The request always contains the GET parameters needed to access the listGrid in readonly mode, whether the requestType is JSON or XML.
• We need $_GET['page'], the page number requested, and $_GET['rows'], the number of rows requested. We must secure these parameters against hacker attacks, therefore the PHP intval() function is used to ensure that we have integer values to work with.
• We can use these numbers to build a LIMIT expression for the query to the database. This LIMIT expression will work equally well for SQLite as for MySQL. For other databases you will have to build a different kind of expression.
• For the pager to work correctly, you need to know the total number of rows in the table. Therefore, this number is retrieved using a count(*) query and its value is used to set the totalRecords attribute on the root node of the XML to be returned. It does not matter what the name of the root node tag is, the remote dataSource widget just looks at the totalRecords attribute.
• As the PHP script is coded here, the query that calculates the number of rows in the table has to be resubmitted for every new page request from the client. Although most database management systems will cache the query results, this may not be sufficient for huge tables. In such a case you must build a more intelligent server script. For example, it will be rather easy to keep the total number of rows in a session variable after first retrieval. If new rows can be added to the table, recalculating the number of rows each time a page in the listGrid is displayed, is unavoidable.

The live example for the remote paging datasource is shown below:

Example 2: Paged movies from a remote data source

As you can see, the performance of loading this example is much better. There is still one flaw however: the sorting does not work. We are going to fix this in the next section.

Sorting the Rows

It will be no surprise to you that in order to support sorting, we will need to handle a few extra parameters in the request that the remote data source sends: sortField, the field to sort by and sortDirection, to specify an ascending or descending sort. We can find these parameters in our PHP script as: $_GET['sortField'] and $_GET['sortDirection']. Below is a snippet of code that builds the ORDER BY clause that we need in the query.

For the sortDirection parameter, we make sure that the query will contain a valid value, by setting a value, either ASC or DESC, directly. Concerning the sortField, it would be best if we checked whether the field given is indeed the name of an existing column. Some database sytems provide an easy way to retrieve the column names of a table, or else you could use a configuration file, or a fixed array for this purpose. In our simple example, we chose to check the column name with a regular expression, to make sure that no SQL injection can occur. If the name is not valid, no sorting will be done.

We need to insert the ORDER BY clause into the query, as follows:

We have now a complete example that supports both paging and sorting from a remote data source. Try it out below:

Example 4: Paged movies from a remote data source

Summary

In this page and in part 1 of the series of examples about listGrid and remote data sources, we have shown how to create a remote data source. We showed how you can use paging and sorting with a remote data source. We did not cover updating of the data source.

From the protocol used to communicate between the server and the datasource on the client, we saw page, rows, sortField and sortDirection.

We discussed some hints and tips for securing the data from a remote data source against SQL injection and other types of data corruption.

Additional Resources

All code used in this page you can find in the attached zip file. See for direction on how to run the code the Introduction to listGrid using a remote data source.

You can find other examples we referred to in this page, here:

• Introduction to listGrid
• Introduction to listGrid paging and sorting using a romote data source
• Binding to a Remote Data Source (PHP) example page

Reference information can be found here:

• Client Reference