home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam  

Book HomePHP CookbookSearch this book

10.16. Program: Storing a Threaded Message Board

Storing and retrieving threaded messages requires extra care to display the threads in the correct order. Finding the children of each message and building the tree of message relationships can easily lead to a recursive web of queries. Users generally look at a list of messages and read individual messages far more often then they post messages. With a little extra processing when saving a new message to the database, the query that retrieves a list of messages to display is simpler and much more efficient.

Store messages in a table structured like this:

CREATE TABLE pc_message (
  posted_on DATETIME NOT NULL,
  author CHAR(255),
  subject CHAR(255),

The primary key, id, is a unique integer that identifies a particular message. The time and date that a message is posted is stored in posted_on, and author, subject, and body are (surprise!) a message's author, subject, and body. The remaining four fields keep track of the threading relationships between messages. The integer thread_id identifies each thread. All messages in a particular thread have the same thread_id. If a message is a reply to another message, parent_id is the id of the replied-to message. level is how many replies into a thread a message is. The first message in a thread has level 0. A reply to that level message has level 1, and a reply to that level 1 message has level 2. Multiple messages in a thread can have the same level and the same parent_id. For example, if someone starts off a thread with a message about the merits of BeOS over CP/M, the angry replies to that message from CP/M's legions of fans all have level 1 and a parent_id equal to the id of the original message.

The last field, thread_pos, is what makes the easy display of messages possible. When displayed, all messages in a thread are ordered by their thread_pos value.

Here are the rules for calculating thread_pos:

  • The first message in a thread has thread_pos = 0.

  • For a new message N, if there are no messages in the thread with the same parent as N, N's thread_pos is one greater than its parent's thread_pos.

  • For a new message N, if there are messages in the thread with the same parent as N, N's thread_pos is one greater than the biggest thread_pos of all the messages with the same parent as N.

  • After new message N's thread_pos is determined, all messages in the same thread with a thread_pos value greater than or equal to N's have their thread_pos value incremented by 1 (to make room for N).

The message board program, message.php, shown in Example 10-4 saves messages and properly calculates thread_pos. Sample output is shown in Figure 10-5.

Figure 10-5

Figure 10-5. A threaded message board

Example 10-4. message.php

require 'DB.php';

// a helpful database debugging function
function log_die($ob) { print '<pre>'; print_r($ob); print '</pre>'; }

// connect to the database
$dbh = DB::connect('mysql://test:@localhost/test') or die("Can't connect");
if (DB::isError($dbh)) { log_die($dbh); }

// The value of $_REQUEST['cmd'] tells us what to do
switch ($_REQUEST['cmd']) {
case 'read':                      // read an individual message
case 'post':                      // display the form to post a message
case 'save':                      // save a posted message
     if (pc_message_validate()) { // if the message is valid,
         pc_message_save();       // then save it
         pc_message_list();       // and display the message list
     } else {
         pc_message_post();       // otherwise, redisplay the posting form
case 'list':                      // display a message list by default

// pc_message_save() saves the message to the database
function pc_message_save() {
    global $dbh;

    $parent_id = intval($_REQUEST['parent_id']);

    /* MySQL syntax for making sure pc_message doesn't change while
     * we're working with it. We also have to lock the tables that
     * hold the thread and pc_message sequences
    $dbh->query('LOCK TABLES pc_message WRITE, thread_seq WRITE, pc_message_seq WRITE');

    // is this message a reply?
    if ($parent_id) {

        // get the thread, level, and thread_pos of the parent message 
        $parent = $dbh->getRow("SELECT thread_id,level,thread_pos
                                FROM pc_message 
                                WHERE id = $parent_id");

        // a reply's level is one greater than its parents 
        $level = $parent->level + 1;

        /* what's the biggest thread_pos in this thread among messages
           with the same parent? */
        $thread_pos = $dbh->getOne("SELECT MAX(thread_pos) FROM pc_message 
            WHERE thread_id = $parent->thread_id AND parent_id = $parent_id");

        // are there existing replies to this parent?
        if ($thread_pos) {
            // this thread_pos goes after the biggest existing one
        } else {
            // this is the first reply, so put it right after the parent 
            $thread_pos = $parent->thread_pos + 1;

        /* increment the thread_pos of all messages in the thread that
           come after this one */
        $dbh->query("UPDATE pc_message SET thread_pos = thread_pos + 1 
            WHERE thread_id = $parent->thread_id AND thread_pos >= $thread_pos");

        // the new message should be saved with the parent's thread_id 
        $thread_id = $parent->thread_id;
    } else {
        // the message is not a reply, so it's the start of a new thread 
        $thread_id = $dbh->nextId('thread');
        $level = 0;
        $thread_pos = 0;
    // get a new id for this message 
    $id = $dbh->nextId('pc_message');

    /* insert the message into the database. Using prepare() and execute()
       makes sure that all fields are properly quoted */
    $prh = 
        $dbh->prepare("INSERT INTO pc_message (id,thread_id,parent_id,
                       VALUES (?,?,?,?,NOW(),?,?,?,?)");


    // Tell MySQL that others can use the pc_message table now
    $dbh->query('UNLOCK TABLES');

// pc_message_list() displays a list of all messages
function pc_message_list() {
    global $dbh;

    print '<h2>Message List</h2><p>';

    /* order the messages by their thread (thread_id) and their position
       within the thread (thread_pos) */
    $sth = $dbh->query("SELECT id,author,subject,LENGTH(body) AS body_length,
                       posted_on,level FROM pc_message
                       ORDER BY thread_id,thread_pos");
    while ($row = $sth->fetchRow()) {
        // indent messages with level > 0
        print str_repeat('&nbsp;,4 * $row->level);
        // print out information about the message with a link to read it
<a href="$_SERVER[PHP_SELF]?cmd=read&id=$row->id">$row->subject</a> by 
$row->author @ $row->posted_on ($row->body_length bytes)

    // provide a way to post a non-reply message
    printf('<hr><a href="%s?cmd=post">Start a New Thread</a>',

// pc_message_read() displays an individual message
function pc_message_read() {
    global $dbh;
    /* make sure the message id we're passed is an integer and really
       represents a message */
    $id = intval($_REQUEST['id']) or die("Bad message id");
    if (! ($msg = $dbh->getRow(
        "SELECT author,subject,body,posted_on FROM pc_message WHERE id = $id"))) {
        die("Bad message id");

    /* don't display user-entered HTML, but display newlines as
       HTML line breaks */
    $body = nl2br(strip_tags($msg->body));

    // display the message with links to reply and return to the message list
<h3>by $msg->author</h3>
<a href="$_SERVER[PHP_SELF]?cmd=post&parent_id=$id">Reply</a>
<a href="$_SERVER[PHP_SELF]?cmd=list">List Messages</a>

// pc_message_post() displays the form for posting a message
function pc_message_post() {
    global $dbh,$form_errors;
    foreach (array('author','subject','body') as $field) {
        // escape characters in default field values
        $$field = htmlspecialchars($_REQUEST[$field]);
        // make the error messages display in red
        if ($form_errors[$field]) {
            $form_errors[$field] = '<font color="red">' . 
                $form_errors[$field] . '</font><br>';

    // is this message a reply
    if ($parent_id = intval($_REQUEST['parent_id'])) {

        // send the parent_id along when the form is submitted
        $parent_field = 
            sprintf('<input type="hidden" name="parent_id" value="%d">',

        // if no subject's been passed in, use the subject of the parent
        if (! $subject) {
            $parent_subject = $dbh->getOne('SELECT subject FROM pc_message
                                        WHERE id = ?',array($parent_id));
            /* prefix 'Re: ' to the parent subject if it exists and
               doesn't already have a 'Re:' */
            $subject = htmlspecialchars($parent_subject);
            if ($parent_subject && (! preg_match('/^re:/i',$parent_subject))) {
                $subject = "Re: $subject";

    // display the posting form, with errors and default values
<form method="post" action="$_SERVER[PHP_SELF]">
 <td>Your Name:</td>
 <td>$form_errors[author]<input type="text" name="author" value="$author">
 <td>$form_errors[subject]<input type="text" name="subject" value="$subject">
 <td>$form_errors[body]<textarea rows="4" cols="30" wrap="physical" 
<tr><td colspan="2"><input type="submit" value="Post Message"></td></tr>
<input type="hidden" name="cmd" value="save">


// pc_message_validate() makes sure something is entered in each field
function pc_message_validate() {
    global $form_errors;

    $form_errors = array();

    if (! $_REQUEST['author']) {
        $form_errors['author'] = 'Please enter your name.';
    if (! $_REQUEST['subject']) {
        $form_errors['subject'] = 'Please enter a message subject.';
    if (! $_REQUEST['body']) {
        $form_errors['body'] = 'Please enter a message body.';

    if (count($form_errors)) {
        return false;
    } else {
        return true;

To properly handle concurrent usage, pc_message_save( ) needs exclusive access to the msg table between the time it starts calculating the thread_pos of the new message and when it actually inserts the new message into the database. We've used MySQL's LOCK TABLE and UNLOCK TABLES commands to accomplish this. With other databases, the syntax may vary, or you may need to start a transaction at the beginning of the function and commit the transaction at the end.

The level field can be used when displaying messages to limit what you retrieve from the database. If discussion threads become very deep, this can help prevent your pages from growing too large. For example, here's how to display just the first message in each thread and any replies to that first message:

$sth = $dbh->query(
    "SELECT * FROM msg WHERE level <= 1 ORDER BY thread_id,thread_pos");
while ($row = $sth->fetchRow()) {
    // display each message

If you're interested in having a discussion group on your web site, you may want to use one of the existing PHP message board packages. The most popular is Phorum (http://www.phorum.org/), and there are a number of others listed at http://www.zend.com/apps.php?CID=261 .

Library Navigation Links

Copyright © 2003 O'Reilly & Associates. All rights reserved.