California Ajax Solutions Team

Presenting database query results in web pages

Kenneth F. Greenberg

One of the most common tasks I am called upon to perform is to run a query against the contents of a database and present the results in tabular form. A query can, of course, return anything. If the returned value is a simple answer ("Jim. Your name is Jim"), then the designer should feel free to present such results in any form that is meaningful to the application. However, it is the nature of database contents to be tabular, and so it often happens that a query returns a result set that is itself best presented as a table.

Since most of the work I do these days uses a web browser as the user interface, these tables can simply be presented using the standard DOM table element and its associated structure. The advantage here is that you can style the table using standard CSS methods to look any way you (or your customer) chooses. Thus, if you are working with a customer who has a standard corporate style, you can easily conform to that style by applying the "normal" CSS rules to your query results table.

Since I am an OOP (Object Oriented Programming) enthusiast, I have no desire to "reinvent the wheel" every time I have to design a new site. Since I want to leverage previous work, I of course developed a family of table objects in JavaScript that I can use every time I need a new site. You can always just create tables on-the-fly in HTML, but this is much less work. And the basic OOP principle that you only need to get things right once still applies; since the object is already "correct," there is very little chance that you will introduce any problems if you start over with every site.

The most basic of these objects is what I call a ResultsTable, since it is used to present a result set returned by a query. There are a few other forms that I use with some frequency. The selector table presents the result set data with a radio button for each line so the user can choose one row for further processing. The checkbox table is similar, but allows for multiple selection. The dynamic XML table present results where each row of the table represents an XML document that can be expanded or contracted in place. These are described in separate articles. Here, I want to present the basics of the results table.

You might respond that a table doesn't need much in the way of description, and I would not argue that point. However, I want to lay the groundwork for the manner in which all these objects are used. If you have a good understanding of how the simple case works, the more complex variants will just be a small step to learn. Also, the ResultsTable class contains some basic features like paging the display (if your table is too large to fit comfortably on the screen) that you would have to write yourself if you were not using the object. It is also possible to retrieve data from the table into your program if you wish to do something with it later. Thus, it is a bit more than just a display element. And, perhaps more interesting to you and your customers, the ResultsTable object are sortable in the familiar "click on a heading to sort" style found throughout Microsoft Office applications and compatible software.

For a table object to be reusable, it can't have much known in advance. It would have been possible to supply length and width as properties when the table was created, but my experience has always been that no matter what I specify, I always end up changing it as the table gets filled up. So it seems best to simply take advantage of JavaScript's variable argument lists and let the user add data to the table as needed. The table will then be resized in much the same way that your browser renders the table differently based on content. So the only property we give the table is an identifier, which can them be used to find it in other JavaScript code. Since we use the identifier as the name of the table as well, the table could even be resubmitted to the server as part of a form, and easily located by server-side code.

At this point, creating a ResultsTable object is simply a matter of writing:

var myRT = new ResultsTable('myNameForThisTable');

As noted above, the string you supply will be used for both the id and the name of the table element that gets created as part of the object. Both thead and tbody elements are also created, and appended as children of the table element. This is required by the table sorting method. While you can reference the table itself (that it, the DOM table element) using its ID, calling other methods requires that you keep a reference to the JavaScript object itself, as in the above line of code.

We noted above that you are likely to have standard styles used throughout a large web application. In such a case, you will probably want to make the look and feel of the results table conform to this style. This is easily done by using an already-defined CSS class:


You can also apply classes to columns. For example, numeric data usually looks better right-justified, so you could define a class called "rjust" and apply it to columns as appropriate. A setColumnClass method is provided for this purpose.

Creating the table object does not cause it to show up on the screen, of course. To do that, you have to add it to a document element. The appendTo method is used to accomplish this, and the name simply reflects that I have an entire library full of form input element objects, and I didn't feel like inventing a new name that I would have to remember. (It is also consistent with the very popular jQuery library.)


And, of course, you will immediately note that this doesn't change the screen at all. That's because the table is still empty. You need to add rows of data, and optionally a header to the table to provide any visual information. As noted above, the way the table will look is controlled outside the table object itself. The standard CSS rules for table, td, th, and so on will control all aspects of the appearance.

In practice, you would normally add all the information to the table first before calling the appendTo method. This is not necessary, and the more complex tables (e.g., the dynamic XML table) add and delete rows on-the-fly anyway. For a static table - one that receives a query result set, shows it on the screen, and never changes it - then the normal sequence would be to create it, add a header, add rows of data, and add it to the DOM tree with appendTo.

Let's take a quick look at the most common method - addRow. It simply takes a list of strings (probably returned from a database query) and adds them to the table object as a new row. Thus, if your query asked for first name, phone extension, city, and email address, then your calls to this method would effectively be passing:

myRT.addRow('Ken','1030','Santa Rosa','');

but in the real world you are not going to be passing constant strings. What you actually pass as arguments to this method depend on how your data was returned from the database query. If your data was returned in XML, then you probably received a document that you can traverse with standard DOM manipulation methods to retrieve the strings. If you used JSON, then you can access the returned data as a JavaScript array and simply copy values into the call. You might even have used a Comma Separated Values string to return information from the database. Various applications call for different methods of moving data from the server to the client, and only you know what works best for your application.

In contrast, the addHeader method does take constant strings, and so is much easier to deal with. You simply code:


If you have added a header row and one or more data rows, you may make your table content-sortable by invoking the method:


Now, the user can click on any column header and sort the table according to that column's content. The table should automatically distinguish between character and numeric data, and sort properly. If you click the column header again, it will resort, reversing the sort order. This style of operation is very common, and users should be comfortable with its operation. Note that this method should be invoked after the data items are in the table. It needs to install click handlers for the header cells, so they at least must be present.

Here is another method that is sometimes useful. The table object keeps track of the number of rows of data that are in the table (not including the header). You can retrieve this by using the method:

var myTableLength = myRT.getRows();

And, since it is sometimes useful to read and write the cells of a table after it has been rendered, the ResultsTable class includes methods getDataValue and setDataValue. These read and write the inner HTML of the node. The latter function can be quite powerful - and potentially dangerous, so it should be used with some caution if you are writing anything other than text. You can also return an entire row of the table as an array, if this better meets the needs of your application.

This is the simplest of the table forms. You are free to use it in sites you design; it is covered by a Creative Commons type license, and you should not remove that text from the source. The source code for the results table object may be found here. The documentation for the class, as generated by jsdoc, may be found here.

This article has deliberately been kept simple, since this is the simplest class. However, you will probably want to look at the SelectorTable article if you want to use paging, as it is described more full there.