Creating Data Tables With PEAR Structures_DataGrid

Table Talk

As a developer who builds customized PHP applications for clients on a regular basis, I’m always on the lookout for new and interesting PHP widgets that could reduce the time I spend writing code. And one such widget is PEAR’s Structures_DataGrid package, which provides a handy way to display, sort and edit structured data in a table.

This might not sound particularly earth-shattering to you; after all, it’s not that difficult to write PHP code to display data in a table or even to sort it using different criteria. What makes Structures_DataGrid special, though, is how easy it makes the whole process – a task that might easily take a few hours to code manually can be accomplished in a little less than ten minutes using Structures_DataGrid. And that, in my book, is certainly something to write home about!

In this article, I’ll be introducing you to the Structures_DataGrid package, showing you how it can be used to display structured data in tabular form. I’ll be showing you how to hook it up to various data sources (including a CSV file, an RSS feed and an Excel spreadsheet), and how to format the resulting output so it’s as pretty (or as ugly) as you want it to be. I’ll also introduce you to some of the package’s more advanced functions, which allow you to interactively page through, and sort, data tables with minimal effort.

Laying The Foundation

This tutorial makes a couple of assumptions. First, it assumes that you understand HTML, know the basics of PHP scripting, and are familiar with processing both SQL result sets and XML data with PHP. Second, it assumes that you have an Apache/PHP/MySQL development environment already set up, and that you’ve managed to successfully install the Structures_DataGrid package. For your reference, the Structures_DataGrid package is freely available from, and is currently maintained by Olivier Guilyardi and Mark Wiesemann.

In order to read data from external sources into a datagrid, it’s necessary to install various data source “drivers” for Structures_DataGrid. These driver classes are not bundled with the base package, but must be installed separately. In a similar manner, there also exist various “renderers”, which allow you to control how the datagrid is rendered; these renderers, too, need to be individually installed. This tutorial makes use of a large number of data sources and renderers and so, in order to get all the examples working, you should make sure that you have the following packages installed on your development system:

  • Structures_DataGrid_DataSource_Array
  • Structures_DataGrid_DataSource_CSV
  • Structures_DataGrid_DataSource_MDB2
  • Structures_DataGrid_DataSource_RSS
  • Structures_DataGrid_DataSource_XML
  • Structures_DataGrid_Renderer_HTMLSortForm
  • Structures_DataGrid_Renderer_HTMLTable
  • Structures_DataGrid_Renderer_Pager
  • Structures_DataGrid_Renderer_XML

You can install these packages manually, or using the PEAR installer, as below:

If the thought of manually installing all the packages above is turning you green, there’s also an alternative: the Structures_DataGrid manual also offers an “everything but the kitchen sink” option, which installs all available data source drivers and renderers in one swell foop, using the PEAR installer. Here’s how:

If you choose this option, keep in mind that many of the data source drivers are dependent on other PEAR packages being available on the system (such as MDB2 or XML_Serializer); if these packages are not present, the drivers may not be installed correctly.

I should mention at this point that this tutorial is intended mainly for users new to Structures_DataGrid. As such, it includes a general introduction to the package and how it works, but shies away from very complex examples. Once you’ve worked your way through this tutorial, you’ll find many more sophisticated applications for Structures_DataGrid in the package documentation.

On The Grid

With all that nasty installation stuff out of the way, let’s get started with a simple example, which demonstrates how Structures_DataGrid works and gets you up and running with the package quickly. Here goes:

This script reads an array containing various values and converts it to a datagrid. Here's what the output looks like:

How does this work? Pretty simple, actually. There are two basic steps to creating and populating a datagrid with Structures_DataGrid.

  1. The first step is to initialize an instance of the Structures_DataGrid class, and use the object's bind() method is used to bind the grid to a data source. This data source can be an array, a SQL result set, an XML file or any other supported source of data. In this case, the data source is an array, which I've conveniently populated with some sample data at the top of the script. The bind() method accepts three arguments: the source data to bind, an array of configuration options, and the name of the data source driver to use - in this case, the Array driver.
  2. Once bind()-ing is complete, the next step is to actually display the datagrid using a renderer. A number of renderers exist; the simplest (and also the default) is the HTML table renderer. Calling the render() method takes care of rendering the datagrid to the output Web page.

In case something goes wrong during the binding or rendering processes - for example, if the necessary data source driver cannot be found - the Structures_DataGrid class raises an error using the standard PEAR error-handling mechanism. This error can be trapped and handled by a user-defined error handler, in this case the errHandler() function, which terminates script processing and displays an error message.

Notice also that the column headings in the datagrid are clickable. Clicking one of these headings automatically sorts the datagrid by that column, in either ascending or descending order. Total amount of code you had to write to enable this functionality? Zero. Is that cool or what?

Country Bumpkin

In the real world, it's unlikely that your data source will be an array defined at design-time. It's much more likely that your data source will be an SQL result set or a collection of XML nodes, which need to be converted into a sortable datagrid. With this in mind, let's take a look at another example, this one building a datagrid from an SQL result set:

Here's the output:

This example uses the example MySQL 'world' database, which you can get from, and the Structures_DataGrid MDB2 driver, which is used to connect to and execute queries on the database server. It assumes that PEAR's MDB2 database abstraction layer is installed on the system, together with the appropriate MySQL-capable driver.

What's different about this script? The primary difference lies in the first argument to bind(): it's not a PHP array, but rather an SQL query. The output of this query is used to automatically populate the datagrid, with Structures_DataGrid automatically figuring out the column names based on the data in the query result set. The DSN string to connect to the database is specified in the second argument to bind(), as a configuration option in key-value format.

Don't want to see so many columns in the datagrid? The easiest way to change this is by modifying your SQL query to explicitly list the result set fields you need. You can also limit the number of rows in the datagrid, by specifying the maximum number of rows in the Structures_DataGrid object constructor: Consider the following revision of the previous script, which illustrates:

And here's the revised output:

The appearance of the HTML table generated by the default renderer can also do with some improvement. The quickest way to do this is with some CSS, like the style rules below:

And now, when you render the datagrid again, you'll see this far easier-on-the-eyes version:

A Good Feed

So far, you've seen Structures_DataGrid in action with two data sources: a PHP array and an SQL result set. But you can populate a datagrid with data from many other sources, including a CSV file, an XML document, an RSS feed and even an Excel spreadsheet. Drivers for all these data sources are available from PEAR; simply install them (and their dependents) and you're good to go!

To begin, let's look at an example of populating a datagrid from an XML document tree. Here's the code:

And here's the output:

It should be noted that, assuming a reasonably simple XML document, the XML driver used by Structures_DataGrid can automatically detect and link XML nodes to datagrid rows and columns - you can see this from the output of the previous listing. In case your XML document is more complex than the simple example above, however, you might need to use PHP's DOM functions to massage the XML into a new, simpler format that can be easily understood by Structures_DataGrid.

You can also hook your datagrid up to an RSS feed, as in the following example, which fills the datagrid with a list of recent posts from the Starcraft 2 blog:

And here's the output:

Pay attention to the $options array in this script: it contains the 'fields' option, which lets you configure which fields should appear in the datagrid. This option is particularly useful when you need to restrict the amount of data appearing in your datagrid.

An Excel-lent Adventure

The Structures_DataGrid package also supports other data formats, such as comma-separated (CSV) files and Excel spreadsheets. To illustrate this, consider the following CSV file, which holds sales information by region and year:

To turn this into a datagrid, use the following script:

And here's the output:

Various options can be passed to the CSV driver to configure how the source data is read and used to populate the datagrid. The 'header' option tells Structures_DataGrid that the CSV data includes a header row; this row is then automatically used for the datagrid table header. The 'delimiter' and 'enclosure' options tell Structures_DataGrid which character are used to separate and enclose CSV field values. And the 'fields' option specifies which fields from the source data should actually make an appearance in the datagrid. Notice also that when binding a datagrid to a CSV file, the first argument to bind() is not the data itself, but rather the file path and name of the CSV source file.

You can also bind to data saved in a Microsoft Excel spreadsheet, such as the one below:

Here's the code to create a datagrid from this Excel data:

Note that in order to use this script, you must have the PHPExcelReader library installed (in addition to the Excel data source driver for Structures_DataGrid). This library is freely available from Once you've got it installed, the rest of the script should be fairly easy to follow, as it follows the same structure as previous ones. Here's the output:

Flipping The Page

Near the beginning of this tutorial, I told you that there were two pieces to every datagrid: the data source, and the renderer. Over the last few pages, I've focused on binding a datagrid to data from a variety of different sources, completely neglecting the other half of the puzzle: rendering the datagrid. Let's now put this right, by spending a little time talking about the different types of renderers supported by Structures_DataGrid.

One of the most interesting renderers supported by Structures_DataGrid is a Pager renderer, which can be used to segment a datagrid into discrete "pages" for easier navigation and generate navigation links to move back and forth between the pages. This renderer piggybacks on PEAR's Pager object, and is illustrated in the next listing:

You'll remember, from a previous example, how datagrids created with Structures_DataGrid allow users to sort datagrid columns, with no extra coding required by the developer. So too is it with datagrid paging: calling the render() method with the 'Pager' argument automatically generates page navigation links for the datagrid, and clicking these links lets you move back and forth within the pages of the datagrid...all with no additional coding needed. Here's what the output looks like:

When paging datagrids in this manner, remember to pass the number of rows per page as an argument to the Structures_DataGrid object constructor, as in the previous listing. If you like, you can also pass other Pager-supported options to the renderer, via the 'pagerOptions' key of the configuration array; you'll see an example of this on the next page.

Sorting It Out

Another interesting renderer is the HTMLSortForm renderer, which generates a form (using the HTML_QuickForm package) that lets you sort the datagrid in an interactive manner. To see it in action, consider the following listing:

Here's what the output looks like:

Here, the HTMLSortForm renderer generates a form which allows the user to specify both the sorting sequence and the sorting order of each field of the datagrid. As with the Pager renderer, you don't need to write a single line of additional code to deal with the values submitted through the sorting form - Structures_DataGrid will automatically read your selection and sort the datagrid accordingly.

Various options may be passed to the renderer; these control the number of fields that can be displayed for sorting, whether the sorting controls should be displayed as radio buttons or selection lists, and the text labels that appear on the different form controls.

XML Marks The Spot

Finally, there's an XML renderer, which can be used to render a datagrid in XML format. This is a neat way to do SQL-to-XML conversion on the fly. Consider the following example, which illustrates:

This script first creates a datagrid and populates it with the result of the SQL query, using the MDB2 data source driver to bind to the query results. It then renders the result using the XML renderer, which returns an XML string representation of the datagrid. A number of options are passed to the renderer to configure the XML output: the 'useXMLDecl' option specifies whether the XML output should include the XML header or not; the 'outerTag' options specifies the name of the root XML elements; and the 'rowTag' element specifies the name of the element representing each row of the data grid.

If you like, you can even save the XML output to a file. Simply add the 'saveToFile' and 'filename' options to specify whether the XML datagrid should be saved to a file, and the name of the corresponding file. Here's the revised code:

And now, if you look in the file 'out.xml', you should see something like this:

And that's about all we have time for. Over the last few pages, I gave you a quick introduction to the Structures_DataGrid package, showing you how to quickly generate a sortable HTML data table from various data sources. I showed you how to populate a datagrid using various data sources, including an SQL query, an XML or RSS document, a CSV data file, a PHP array and an Excel spreadsheet. Finally, I gave you a fast introduction to various renderers supported by Structures_DataGrid, showing you how they could be used to add interactive paging and sorting features to a datagrid, or convert a datagrid into a customized XML document.

I hope you enjoyed this tutorial, and that it will save you some time the next time you sit down to work with data tables and PHP. Happy coding!

Copyright Melonfire 2007. All rights reserved.