Published: Tuesday, March 07, 2000
Loops within Loops: Coding an ASP-based Bulletin Board
By Zola
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
%>
|
Read Part 2
