Forums

Another popular and easy-to-implement type of traffic magnet is the discussion forum, and we recently came across a stunning example devoted to the VW Passat at www.clubb5.com. You'd imagine that a site concerned with such a run-of-the-mill car wouldn't attract a lot of traffic, but the forums on clubb5 get hundreds of messages per day.

So how do you add a forum to your site? There are three main choices. First, you can use an external forum hosting service. These typically provide you with a forum in a frame that you can use on your site. Some are free (usually sponsored by advertising) while others are on a fee basis.

Second, you can buy an off-the-shelf forum product of which there are a huge number available, and we'll be looking at some of our favourites in a future column. The third method, and the one we'll concentrate on today, is to roll your own forum. As usual, we'll be giving our examples in VBScript/ ASP, which allows virtually anyone with a Windows PC to view the examples. Those who'd rather play with PHP, JSP or Perl will almost certainly have the ability to translate our examples into their language of choice. The only extra resource you'll need is some kind of database software - Access, SQL Server or MySQL are fine.

Think for a moment about what makes up a forum. For this example we'll build a fully-threaded message forum, since threading, or 'conversation linking', is almost essential for a forum to become popular. The basic building block is the message itself, and each message will have an author, a date/time when it was posted, a subject line and then the message body itself. If the message is a reply to a previous message, there should be some indication of this, and this in turn implies that each message should have a unique identifier.

To start, run the script below, or use a GUI tool, to create the following table:

CREATE TABLE [dbo].[messages] (
[idMessage] [int] IDENTITY (1, 1) NOT NULL ,
[idForum] [int] ,
[chrPoster] [varchar] (50) ,
[chrSubject] [varchar] (255) ,
[chrMessage] [varchar] (2000) ,
[dtDateTime] [datetime] ,
[idPred] [int]
) 
GO

We've added the idForum field to allow you to have more than one forum stored within your database. You'll also notice the arbitrary size limits on the poster's name, subject and message. Experience suggests that people generally won't type long messages into a Web-based forum, but having said that you'll almost certainly need to adjust these values to meet the needs of your particular app.

Now proceed by manually stuffing some messages into this database, using something like Access as the database client. Make sure that idMessage is incrementing properly, and set idForum to 1 for this example. Also try to create some threads by setting idPred to point to a previous message. To display these messages on your Web page, first grab a list of all of the 'root' messages and these will be the messages that have nothing in their idPrev field:

sql = "SELECT idMessage FROM messages WHERE (idForum=1 AND idPred IS NULL) ORDER BY dtDateTime DESC"

set rsMessages = dbMessages.execute(sql)

You'll now have a recordset called rsMessages, containing the message identifiers for the root messages, in descending date order; that is, with the newest messages first. Next you'll want to display these messages, but for each message you'll also want to display any subsequent replies before moving on to the next message. The way to do this is by having a recursive message display subroutine, which is able to call itself as required, and then call this for each message in our recordset:

do until rsMessages.eof
  pmessage rsMessages("idMessage"),0 
  rsMessages.movenext
loop

The actual message display subroutine takes the message identifier, so that it can grab the message and other details from the message database, and an indication of the message level so that it can indent replies correctly. We'll assume that root messages are at level 0. The message display code will be:

sub pmessage(messid,level)
  'get the message fields
  sql = "SELECT * FROM messages WHERE (idMessage = "& messid &") ORDER BY dtDateTime DESC"
  set rsPMessages = dbMessages.execute(sql)

  'indent threads
  for x = 0 to level
    response.write "  "
  next 

  response.write rsPMessages("chrSubject") & " " & rsPMessages("chrPoster") & " " & rsPMessages("dtdateTime") & " "

  response.write rsPMessages("chrMessage") & "<p>"

  'now look for any replied to this message, and recurse
  sql = "SELECT * FROM messages WHERE (idPred = "& messid &") ORDER BY dtDateTime DESC"
  set rsNMessages = dbMessages.execute(sql)
  do until rsNMessages.eof
    pmessage rsNMessages("idMessage"),level+1
    rsNMessages.movenext
  loop
end sub

There you have it, the basis for a forum system. You'll need to wrap this up in a user interface and add code for posting messages and replying to new messages, but that's easy so we'll leave it as an exercise for you.

If you use these examples to add a forum to your site, send us the URLs - likewise if you have any hints or tips.