The objective of this example page is to explain how to set up a remote data source for a listGrid widget. For practical reasons we have chosen PHP 5 as our server scripting language. We hope that the code is clear enough to be understandable if your preferred language is not PHP. The database that contains the data for the remote data source is a SQLite3 database. Minimal changes to the code are needed to use the code for a MySQL database.

This example page builds upon the Introduction to the listGrid example. It is assumed that you are familiar with the example.

We expand on the listGrid introduction by showing how to create a simple remote data source. In this first step we will present the same data source content, but now retrieved from a remote data source. First we will show how to retrieve static data and next we will retrieve the data from a database. In the next parts of this tutorial we will discuss paging and sorting of data in a listGrid retrieved from the remote data source.

A more complete, but also a much more complex example is provided in the Binding to a Remote Data Source (PHP) example page.

The Local Data Source

Let us start with the listGrid itself, as used in the Introduction to the listGrid example:

Next, take a look at the data source as defined in this example:

For small amounts of static data, it is acceptable to define the data source of your listGrid locally as in the example. For larger amounts of data, or for dynamic data, you should define a remote data source.

The Remote Data Source

The definition of a remote data source is done by specifying e:behavior="b:remoteData" in the b:dataSource tag, like this:

As you can see, we need to tell now with the url attribute, where the actual data can be found. In our example the script remoteDataSource1.php is given as the value of the url attribute, and it will serve the data. We specified that the dataType of the data to be returned by the PHP script should be XML. An alternative would be to ask the PHP script to return JSON data. These are the only changes we need to make to listGridExample.html to transform it into listGridRemoteExample1.html.

In addition, we need a server script, remoteDataSource1.php, to feed the listGrid with data. The PHP script looks like this:

You may feel a bit cheated, because the data that is returned looks very static indeed. It makes clear however, that the data returned from a server can be the same as as data from a local data source. It also makes clear what the format of the data should look like when the server script returns data.

Note the header function that should be used as the first statement in the PHP script. It tells the browser to interpret the data that is sent from the server as XML instead of text. If you do not put this header statement as the very first thing to return, the server will return a text/html type header instead, and the Backbase client will not be able to interpret your data properly.

Example 1: The listGrid with a remote data source

The files used for the example shown above are contained in a zip file attached to this page. You need listGridRemoteExample1.html and remoteDataSource1.php to execute it.

Remote Data from a Database

In practice, data to be displayed in a ListGrid will often originate from a database, therefore we will now look at how we can retrieve data from a database and send this data to the client. For the purpose of this example, we have the same data in a table called contacts, created as follows:

The data in the table is stored in a relational way, which means that every row in the table corresponds to a record tag in the XML document and the tag names will correspond to field (column) names in the table. In this way we can perform simple processing to transform the table data into an XML document.

We have chosen to use the PDO (Persistent Data Objects) layer of PHP here, together with an SQLite database. PDO is available for PHP 5 and higher. Using the PDO abstraction layer has the major advantage that our code is also applicable to other databases in addition to SQLite, such as MySQL, with minimal changes to the code.

Looking at the code, you will notice the following:

• The creation of the PDO object is the only database specific statement. If you would use MySQL instead of SQLite, the construction of the PDO object would look something like:
  $dbHandle = new PDO("mysql:dbname=$dbname;host=localhost", 'myuser', 'secret');
• The code to generate the XML is very generic, because the column names in our database table are chosen to be the same as the tags used in the XML. It is also very simple: just concatenate some strings together, with the variable data as retrieved from the database used to construct the tag names form the column/field names and the tag contents from the field contents.
• To make the XML manipulation more secure, it is recommended to use the PHP DOM or SimpleXML extensions to build an XML tree and then serialize it. Here the only measure we took to secure the data is using the htmlspecialchars() PHP function, to ensure proper escaping of the data retrieved from the database.

You can check what the PHP script returns by invoking it directly, as in the live example below:

Example 2: The data returned by the PHP script, after retrieving it from the database

As you can see, the data is the same as the static data we saw before. Of course, we can now update the data in the database, which means that the listGrid would also show the updated data when it is re-displayed.

Example 3: The listGrid with data retrieved from a database

To complete this page, we are going to show a larger table, which you may also have encountered in other examples: the movies table. To use this table, of which there is a copy in the databindingTutorial.db database, we only need to adapt the specification of the listGrid and we need to adapt the SQL query in the PHP script. We did a bit more here: you have to press a button to load the listGrid into the page. We have a few reasons for this:

• It makes clear that it takes time to load larger amounts of data.
• It makes this page a bit more user-friendly, because the data is only loaded if you request it. Therefore the page loads faster on start-up, and seemingly performs better.

Example 4: The listGrid with more data: movies

The code for this example is found in listGridRemoteExample3.html, movieGrid3.xml and remoteDataSource3.php. The only difference between remoteDataSource2.php and remoteDataSource3.php is that:

$sqlGetView = 'SELECT * FROM contacts';

is replaced by

It may seem that it would be fairly easy to build a generic PHP component that can display any table from a database by just providing the name of the database and the name of the table. As we saw on this page, generating proper XML is a bit more tricky that just concatenating strings together. For example, to make sure that strings are properly escaped on output, that user input is secured against SQL injection, and that care is taken of various character encodings such as UTF-8 and ISO-8859-1, can be very tricky.

Additional Resources

All code used in this page can be found in the attached zip file. To run the code, unzip it to a folder that is accessible by your server. You should have PHP and SQLite installed and the PDO-SQLite driver for PHP should be enabled. This can easily be achieved if you download and install the XAMPP package from www.apachefriends.org. On a windows PC, you must edit the php.ini file in the apache/bin folder and remove the ; in front of extension=php_pdo_sqlite.dll and extension=php_sqlite.dll. Place the downloaded code in a subfolder of htdocs and also put your Backbase client code there. Make sure that the paths in the HTML files point correctly to your Backbase installation.

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

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

Reference information can be found here:

• Client Reference