Generating HTML Tables

Generating HTML tables for displaying data is a pretty common task these days. Easy to do, easy to tweak, no special tools needed (though they do come in handy), all the end user needs is a browser. How you do it can make all the difference in how easy it is to maintain. They key to separate your display code from the data. XML and style sheets offer some interesting ways of doing this which I'll discuss in a later article, today we're just looking at old fashioned HTML. Combined with our friend the ADO recordset, we can do some wonderful things. Well, useful anyway. This may be old news to many of you, but I still see a lot of this table code being written. If nothing else when you see it you'll have a resource to point the offender to!

Let's start with the some what contrived example of needing to generate a web page that displays all the files in a specific folder. We know that the folder is updated once a day. Here is some code that shows one way of doing it that could easily be run from a SQL job:

Nothing very fancy, just using the filesystem object to both get the list of files and to write it back to disk. See how the table formatting tags are mixed in? Now suppose the user asks for the date created to be added. We only need to change one line, like this:

Fairly trivial right? What if the report needed to be sorted by file size? Or able to sort on all columns? Sorting on all columns requires more effort, we'll cover that next time too - but sorting on one column we should be able to do. But how? Just thinking while I write, I came up with these ideas:

1) Load the info into an array, write or find some sorting code, sort it, then do the file generation.

2) Maybe some kind of hack, use the OS to do something like this "dir *.* >temp.txt" which will create temp.txt containing all the files in the folder, then load & parse it back into columns, then do the file generation.

3) Load the data into a table, sort it using an order by, create the file, drop the table.

All will work, but being a SQL site and all, I think we'll try #3! Here is what I came up with:

Now we can sort on any column if we need to, we could even use a where clause to restrict the output. We've still got our display code mixed with the data and I'll bet at least one person is thinking that using SQL and a table for sorting this list is overkill?

Let's do something about making this code more generic and separating the UI/data. For that all we need is a function that creates a table from a recordset. Here is a very simple version:

Once we have that, we can change our earlier code to look like this:

Now if (when!) we change the columns included, the output portion is handled automatically. Now let me show you another advantage of this technique. Suppose you'd like to add a header row to your table. Going back to our earlier example, we would do something like this:

This works, but now we are back to having to change our code in multiple places each time we change the columns being displayed. If we use the table technique and our new function, we can leverage some meta data to do this work for us, like this:

As you can see I'm looping through the fields collections to get the column names. This code be easily extended to format the column sizes based on the size of the column, do special formatting when the data type is a number or date, etc. If you use this function in all your pages, you leverage every little improvement you make across all of those pages! That is code reuse at it's best.

Now let's return to whether we really need SQL to do the sorting. Or even to create a recordset. The answer is a conditional no. We can do the sorting other ways, but having the recordset is what makes the separation between data and UI work. We don't have to have SQL running to use a recordset though. In the next example I'm creating a disconnected recordset, loading the data into it, then using it's ability to filter and sort to customize the output.

Whether you create a table, do a select from an existing table, or create a disconnected recordset depends on the circumstances of course. The key is that by always pushing our data into a recordset we can leverage our display code heavily and that is worth something.

In a follow up article we'll look at how we can use XML to accomplish the same thing and add the ability to do client side sorting. As always I look forward to your comments. Whether you agree or disagree, take a min to add your thoughts to the discussion forum attached to the article, other readers will appreciate the additional insight only you can provide.