Loops within Loops: Coding an ASP-based Bulletin Board

Webmaster Note: This article provides an easy way to list nested forums. It does have some drawbacks, though. For starters, there is an upper-bound on the number of nested replies a message can have. In this example, the number is 12, although this is configurable. Furthermore, this approach is not the most efficient approach, and could be streamlined using stored procedures and fewer ADO objects within the code.

That being said, it is still a good article and good code. As a developer, there is often a tradeoff between the ease of programming and the efficiency of the script. Since Zola's Web site is not hammered with hundreds or thousands of messages every day, Zola was able to place ease of programming before efficiency. This messageboard system is good for beginners wanting to learn more about nested forums, and very useful for sites that don't expect hundreds or thousands of daily messages. If you do expect such conditions, you should invest in a commercial messageboard application.

Now, onto the article!

Recently I ran into a programmer's nightmare-due to a server upgrade, those handy cgi bulletin board scripts provided by my host no longer ran, and when I went to generate new ones, I discovered that they were "in development", web-ese for "sometime this century". But what about my 20 or so non-running boards?

I had been puzzling over how to convert my boards to ASP for some time. You see, adding the info to the database was no problem, but formatting the output was the kicker. It turned out the answer was nested loops!

Let's discuss the nature of a bulletin board for a moment. What you have is posts, and then you have responses to those posts, and responses to the responses to the posts.....my former cgi script basically looked at this, created the tree and wrote it to the page. It also had the additional annoyance of creating a new page for each post. On the surface, that sounds like a minor issue...until you have 626 pages in your site of which 400 are posts! And there was no convenient way to archive.

ASP would give me the convenience I wanted. I could store each post in my database and call it as necessary, I could limit the results on the page by date, which would make archiving a breeze, and good-bye to 400 pages worth of posts.

Viewing the posts was simple. I could use the GET method to pass the post ID number, and simply write a single page that displayed whatever post matched the ID. Adding and editing posts was equally basic. But HOW to make the main page display properly? The only board whose code I could access for free only divided posts into main posts and responses. I wanted the same formatting I could get with my cgi script.

The easiest way to determine which posts were responses and which were not was to create a field called ResponseTo. I liked my old board's feature of having the form to make a post right on the page you were using, and that solved the problem of filling in the ResponseTo field. If you made a post from the main page, the ResponseTo field was set as a hidden field valued at 0. If you made a response to a post, the ResponseTo field was set as a hidden field with a value equal to the post ID that had already been passed.

So the first part of that display page would have to be to get all the posts where ResponseTo was equal to zero. That meant it was a primary post. This was a standard looped query:

<% Set rsPosts = Server.CreateObject("ADODB.Recordset") sql = "SELECT BulletinBoard.PostID AS Post, BulletinBoard.BBName, " & _ "BulletinBoard.BBEmail, BulletinBoard.Subject, " & _ "BulletinBoard.ResponseTo, BulletinBoard.BBDate, " & _ "BulletinBoard.BBTime " & _ "FROM BulletinBoard " & _ "WHERE ResponseTo = 0 AND BBDate >(Date()-90) ORDER BY BBDate DESC" rsPosts.open sql, conn, 3, 3 If rsPosts.eof = false then %> <!-- In an Access database, you get the current date by the simple command Date(). Other databases use different syntaxes, so this code may have to be tweaked a bit. I am limiting the date so that we don't get 3000 posts on the same page. If your bulletin board was very active, you could limit the date further. If you averaged a post a day, you could make it longer. --> <!-- The extra "if eof" looks redundant but avoids the server going to the next command unless it is necessary --> <% On Error Resume Next rsPosts.MoveFirst do while Not rsPosts.eof 'I explicitly declare the value of "post" here for later use Post = rsPosts("Post") BBDate = rsPosts("BBDate") 'I have chosen to use images rather than <ul> so that I can 'use a different image depending on the "freshness" of the post If BBDate = Date() then %> <img src="../../images/anibutton.gif" align="absmiddle" width="15" height="14"> <% ElseIf BBDate = (Date() -1 ) then %> <img src="../../images/button.gif" align="absmiddle" width="15" height="14"> <% ElseIf BBDate = (Date() -2 ) then %> <img src="../../images/button2.gif" align="absmiddle" width="15" height="14"> <% Else %> <img src="../../images/button4.gif" align="absmiddle" width="15" height="14"> <% End If 'Display info about post here rsPosts.MoveNext loop %>

Getting the responses would use the same kind of query, as would getting the responses to the responses. But I realized that in order to display the posts in order, from the original post, then the response, then the response to the response and so on, I needed to nest those query loops!

So, I inserted a second query between the display info and the MoveNext loop. This query was almost identical to the first, except for a few minor changes. First, I would want to give my variables a slightly different name. If I have multiple values named PostID, there is going to be some confusion as to WHICH variable I meant.

This was easily solved by using SELECT AS. To keep it simple, PostID would be selected as PostID2, and so on. Then I would have a set of variables for the first set of posts, then the second, and so on. I would also change the criteria of my SELECT statement. I had declared the value of Post, so now I wanted all the posts where ResponseTo equaled the value of PostID as declared in the previous loop:

<% Set rsPosts2 = Server.CreateObject("ADODB.Recordset") sql = "SELECT BulletinBoard.PostID AS Post2, " & _ "BulletinBoard.BBName AS BBName2, " & _ "BulletinBoard.BBEmail AS BBEmail2, " & _ "BulletinBoard.Subject AS Subject2, " & _ "BulletinBoard.Message AS Message2, " & _ "BulletinBoard.ResponseTo AS ResponseTo2, " & _ "BulletinBoard.BBHomepageName AS BBHomepageName2, " & _ "BulletinBoard.BBHomepageURL AS BBHomepageURL2, " & _ "BulletinBoard.ImageURL AS ImageURL2, " & _ "BulletinBoard.BBDate AS BBDate2, " & _ "BulletinBoard.BBTime AS BBTime2 " & _ "FROM BulletinBoard " & _ "WHERE ResponseTo = " & Post & " ORDER BY BBDate DESC" rsPosts2.open sql, conn, 3, 3 If rsPosts2.eof = false then 'This section is a repeat of the section in the first loop, except 'all our variables are now VariableName2, and an extra blank image 'is added just before the button gif to mimic the spacing we would 'have gotten using <ul> else 'if eof is TRUE, jump right here to the end! end if rsPosts2.close set rsPosts2 = nothing %>