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!

In reply to Re: Message Board Threading with DBI/MySQL by Henri Icarus
in thread Message Board Threading with DBI/MySQL by Red Neckerson

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.