By Claude Rubinson
Developers often desire to display the result of their database query in a series of columns. Josh Hurwitz recently authored an article (Formatting Database Results) for 4GuysFromRolla demonstrating how to accomplish such a task when the results are unordered. However, an ordered series of results is often preferred. This article develops an ASP script that resolves the results of an ordered database query into any number of specified "balanced newspaper" columns whereby the results flow down one column and are continued at the top of the next. Each column has approximately the same number of elements and retains the ordering specified by the database query.
This is not what we want. We'd like to have the results sorted by row then by column, not by column then by row.
This is what we want. Notice that the results are sorted first by the row and then by the column.
The obvious solution is through the use of tables, but proper formatting is difficult to achieve since HTML's output is fixed. Once a row has been displayed, further modification is not possible. One cannot, therefore, process and display a table's columns one at a time. Rather, formatting must be applied prior to processing the HTML, thereby resolving each column simultaneously.
I attempted to solve this dilemma through a number of different approaches. The first was to parse back and forth through a single recordset assigning the records to the table cells as appropriate. The attempt was unsuccessful on two counts. First, it was rather difficult to keep track of where one column ended and another began. The result was frequent duplication of the query results, whereby the last element of one column was repeated as the first element of the next. Even if an appropriate algorithm was developed, the continuous parsing of the recordset was unacceptably time consuming.
A second approach involved the use of multiple recordsets. Theoretically, each recordset could contain one column's worth of results. By stepping though each recordset at the same time each column could be resolved simultaneously. However, the problem of duplicate results was still evident. Moreover, the solution was inelegant and a drain on system resources.
I found the solution in the use of a multidimensional array. Not only is this solution an efficient use of resources in that it employs only one recordset, but it provides far more flexibility than the other solutions I had attempted.
The script is composed of three distinct segments. The first segment contains basic initialization parameters. The second segment sorts the results of the recordset into the multidimensional array. The third segment outputs the contents of the array into a properly formatted table.
intColumns specifies the number of columns that the table will display. The script does not rely on any particular SQL commands, so any SQL statement may be used to construct the recordset. However, the order direction specified by the SQL statement is critical as it defines the sorting of the HTML output.
The displayed code opens the recordset with a static cursor, but this is only in order to enable the
RecordCount() function. (Note: Since we used an ADO constant,
adOpenStatic, we must either use the
METADATA tag or include
ADOVBS.inc.) Also note that those wishing to conserve system resources or use a faster cursor can use the SQL
Count() function to achieve comparable results.
Two arrays are actually used to sort the recordset results into the multidimensional array. A single dimension array (
ColumnArray) is dimensionalized against the number of columns; the array possesses one element per column. The value of each element identifies the last record which will populate the associated column.
The multidimensional array (
DataArray) is dimensionalized against the number of records and the number of columns. Each dimension of the array represents a separate column of the table.
A set of nested loops populates the multidimensional array with the results of the recordset. i represents the columns, k represents the rows, and j keeps track of the cursor's current position within the recordset.
This section of code outputs the array contents to HTML. Again, a nested loop is used. The inner loop outputs the cells, the outer loop moves through the rows. Note that the script checks the contents of the array element. If the element is empty, a non-breaking space is written; this ensures proper appearance of the empty cell.
The second part of this segment checks the following row for the presence of data. If each cell of the following row is empty, the contents of the array have been exhausted and the loop is exited.
Exciting, eh! We're almost there! To read about the limitations and customizations of this system, read on to Part 2!