When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Wednesday, November 17, 1999

Formatting Database Results into Newspaper-Style Columns
By Claude Rubinson


Background
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.

- continued -

Results ordered by the column then by the row
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.

Results ordered by the row then by the column
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
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.

Segment 1
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.

<%
intColumns = 1

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.ConnectionString = "YourDSNConnection"
Conn.Open

sql = "SELECT txtSiteName "
sql = sql & "FROM tblASPSites "
sql = sql & "ORDER BY txtSiteName"

set rs = server.CreateObject("ADODB.Recordset")
rs.Open sql, Conn, adOpenStatic
%>

Segment 2
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.

<%
redim ColumnArray(intColumns-1)
for i = 0 to ubound(ColumnArray)
  ColumnArray(i) = rs.RecordCount * (i+1)/(intColumns)
next

redim DataArray(rs.RecordCount/(intColumns-1),intColumns)

for i = 0 to intColumns - 1
  k = 0
  do while j < ColumnArray(i)
    DataArray(k,i) = rs("txtSiteName")
    j = j + 1
    k = k + 1
    rs.MoveNext
  loop
next

rs.Close
set rs = Nothing
conn.Close
set conn = Nothing
%>

Segment 3
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.

<table border=1 width=600 align=center>
  <%
for i = 0 to ubound(DataArray) Response.Write("<tr>") for j = 0 to intColumns - 1 Response.Write("<td>") if DataArray(i,j) <> "" then Response.Write(DataArray(i,j)) else Response.Write("&nbsp;") end if Response.Write("</td>") next Response.Write("</tr>") txtContinue = "" for j = 0 to intColumns - 1 txtContinue = txtContinue & DataArray(i+1,j) next if txtContinue = "" then Exit For end if next %>
</table>

Exciting, eh! We're almost there! To read about the limitations and customizations of this system, read on to Part 2!

  • Read Part 2

  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article