Red Neckerson has asked for the wisdom of the Perl Monks concerning the following question:

Hello, Monks. I'm trying to write a threaded message board (Matt Wright's wwwboard) with perl DBI, and mySQL. The below code function sucks, and I'm trying to improve it. It works, however, I am concered about my compiling a prepare statement over and over in the below function: 

sub get_thread_replies {
my ($T_id, $M_id) = ($_[0], $_[1]);
my $sth1 = $dbh->prepare("SELECT * FROM $vars{'poststable'} WHERE thre
+ad_id = ? AND level_id = 0 AND parent_id = ? ORDER by message_id DESC
+");
$sth1->execute($T_id, $M_id);
$sth1->bind_col(1, \$message_id);
$sth1->bind_col(2, \$parent_id);
$sth1->bind_col(3, \$thread_id);
$sth1->bind_col(4, \$level_id);
$sth1->bind_col(5, \$name);
$sth1->bind_col(6, \$email);
$sth1->bind_col(7, \$location);
$sth1->bind_col(8, \$icon);
$sth1->bind_col(9, \$subject);
$sth1->bind_col(10, \$body);
$sth1->bind_col(11, \$message_url);
$sth1->bind_col(12, \$message_url_title);
$sth1->bind_col(13, \$datetime);
$sth1->bind_col(14, \$user_icon);
$sth1->bind_col(15, \$user_sig);
$sth1->bind_col(16, \$reply_subject);


while ( $sth1->fetch ) {
print "<ul><li><b><a href=\"viewpost.cgi?thread_id=$thread_id&message_
+id=$message_id\">$subject</a></b> <img src=\"$vars{'boardiconsdirurl'
+}/$icon.gif\" border=0>  <b><font color=\"black\">$name</font></b> <s
+mall><font color=\"dimgray\">$location </small> </font>$datetime\n";
&get_thread_replies($thread_id, $message_id);
print "</ul>\n";
}

}

[download]

The function recursively goes through the table looking for replies to a parent message, running the function again, until there are no more replies for that message id. I want to run the prepare statement however just once, and use execute thereafter, and not do what I am doing which is calling a prepare statement each time the function is called. Red------

Replies are listed 'Best First'.
Re: Message Board Threading with DBI/MySQL
by Henri Icarus (Beadle) on Jun 07, 2001 at 06:16 UTC
    The other thing you can do is you can store the thread id as a string of the id's of the message's replied to representing the actual thread hierarchy. This allows you to implement the same kind of thing that you see on this site where it's clear when looking at the threads which level their at.

    So, for example, message 1 has two replies (messages 2 & 3), and there are also two repies to message 2 (messages 4 & 5). The thread ids for the messages are:

    Message: 1 Thread ID: NULL
    Message: 2 Thread ID: "1"
    Message: 3 Thread ID: "1"
    Message: 4 Thread ID: "1_2"
    Message: 5 Thread ID: "1_2"

    This lets you use SQL LIKE to pick out the exact level you want, ie:

    SELECT ... WHERE thread_id LIKE "1_2%";

    to get all the messages that thread with a message at a given reply level, etc..

    You can also you regular expression searches for more complex searches. Of course such searches won't be indexable.

    Also, using this method, if you sort by the thread id when you get back a list of messages then you can easily examine the thread id string with Perl regular expresions to figure out at which level of indentation to display it!

    Good luck!

[reply]
[d/l]
[select]
      What about inserting, deleting, moving, etc? That would ruin the 'hierarchial' structure if you're not gonna rewrite all child threads :)
      I usually stick to less obvious message ID format, like a time & date format (as unique key & incremented if necessary). To solve the pointer to the parent, you can either have a parent 'pointer' in your message ID, or have an actual parent field.

      Greetz
      Beatnik
      ... Quidquid perl dictum sit, altum viditur.
[reply]
        If you have to have that kind of flexibility then of course this system doesn't work. But usually, for a message board app, you don't need that. It's a "write once and leave it" kind of application. And let's say you do delete a message, the fact that it's gone doesn't destroy the relationships of all the other messages...
[reply]
Re: Message Board Threading with DBI/MySQL
by voyager (Friar) on Jun 07, 2001 at 04:18 UTC
    Better would be to have the root message and all its replies share a single value (e.g., root_msg_id) on which you have built an index.

    Then you only need one select to pull them all in. You will still need to sort out the hierarchical relationships, but you've got parent_id, level_id, etc. in the table already.

    Any benchmarking you do will indicate that one sql select is as slow as executing several hundred (if not thousand) lines of procedural code. The slight amount of thrashing in perl to sort things is more than made up for by eliminating all but the one select.

[reply]

Back to Seekers of Perl Wisdom