Windows Systems Administrator Jupitermedia US-CT-Darien Post A Job | Post A Resume

Formatting Database Results
By Josh Hurwitz

---

This is a simple article on HTML formatting in ASP. A while ago I needed to display database query results in HTML table columns. I needed the results to show up in different numbers of columns, so my code had to be handy for an n- column table, where "n" is any positive integer. I went through a number of iterations of code and arrived pretty quickly at the following straightforward Do Until and for constructions.

To accomplish this, I decided that I needed to use the mod operator. The mod operator returns the remainder upon dividing two terms. For example:

x mod y

would return the remainder of x/y. For example, 4 mod 2 would return 0, since 4/2 = 2, remainder 0. 4 mod 3 would return 1, since 4/3 = 1, remainder 1.

We can use the mod operator to display data formatted into N columns. For example, we can display the values of an ADO recordset in a series of columns using the mod operator. For simplicity's sake I'll not print actual data from the database just yet. Instead we'll cycle through the database rows printing "blah" for each row.

(A note: there is bound to be confusion about whether I'm talking about DATABASE rows and columns or HTML rows and columns in this article. I'll attempt to clear things up by explicitly stating whether a row or column is relevant to the database or HTML.)

<% i = 1 do until results.eof if not i mod 3 = 0 then %> blah <% else %> blah<br> <% end if i = i + 1 results.movenext loop %>

This block returns HTML code that looks like this:

	blah blah blah

	blah blah blah

	blah blah blah

	blah blah blah

	blah 

for an ADO recordset with 13 rows of data. In this case, we're displaying 3 columns of HTML; this code will work for any positive integer n, however. Until the counter (i) reaches a value divisible by n, the page prints "blah". When i reaches the right value, the page ends the row by printing "blah<br> ". The next value of i will start a new row. This works for plain HTML text, but really we want to format our output in an HTML table. This requires another step. Take a look at the following block of code (we'll change n to 5 this time, for 5 columns):

<table border=1> <tr> <% i = 1 do until results.eof if not i mod 5 = 0 then %><td>blah</td><% else %><td>blah</td></tr><tr><% end if i = i + 1 results.movenext loop %> </table>

First, I start the table by writing an HTML table tag and starting a row with <tr>. Then I set i to 1, start the do until structure, and put the data from each row in the results variable (at the moment I'm representing this with "blah") in a table cell. When I reach the end of the HTML table row, I end the current row and start a new one. We get an HTML table that looks like this:

blah	blah	blah	blah	blah
blah	blah	blah	blah	blah
blah	blah	blah

View source and note that the table does not end properly. This problem can be easily fixed, as I'll discuss below. For now, let's concentrate on getting our data out of the database.

To put actual data in these cells, we'll gather and then display the database rows using a variable that stores the results of a SQL query. Let's say we want to get information about news articles out of the "news" table in our database. Specifically, let's format an HTML table of publication names and dates of the articles. Here's the full code, with some interesting tidbits I'll explain shortly:

<% dim i dim cellString 'open ADO connection Set dbConn = Server.CreateObject("ADODB.Connection") dbConn.Open "DSN=datasource;UID=user;PWD=password" sqlstr = "select publication, date from news order by date desc" Set results = dbConn.Execute(sqlstr) 'test for data if results.eof then response.write("Sorry, none found.") 'begin formatting else %> <table border=1><tr> <% i = 1 do until results.eof cellString = "<td>" & results("publication") & _ ": " & results("date") & "</td>" if not i mod 6 = 0 then response.write(cellString) else response.write(cellString)%></tr><tr><% end if i = i + 1 results.movenext loop %> </table> <% end if %>

I'm using a SQL Server 7 database. Your SQL string may look different than the one above. Here, we're getting two kinds of article data from the "news" table: publication and date. Each article represents a row in the "news" table in the database, and each row has "publication" and "date" data. We're ordering our data by date descending, so that the most recent articles' publications and dates appear first.

The basic idea is to put the elements you want from the database into a variable, which I've called "results". In a case where the database table is empty, we'll return a string that says "Sorry, none found." If there is data in the table, then we'll go on to display it in an HTML table. First, we write a table tag and start a row. Then, to streamline the code, we'll put each new cell, complete with the data we want from the results variable, into a variable called "cellString". Cycling through results, we'll write just the contents of cellString whenever i is not divisible by 6. When i IS divisible by 6 (i.e., when 5 cells have already been added to the table), we'll write cellString AND end the HTML row and start a new one with </tr><tr>. After that...

That's it. One problem with the above code is that, unless your number of database rows happens to be equal the number of HTML columns you wish to display, the HTML code that results won't end your table rows properly (it will leave out the remaining cells in the last row). (However, the code above will display properly on most browsers.) To correct the problem would be to find out what i is when you reach results.efo, then perform an i mod n: that will be the number of "<td></td>"s you'll need to add to finish the table. Then you'll round out with a "</tr>" and presto! you have a properly formatted HTML table.

By Josh Hurwitz

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