Recipe 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 (   id INT UNSIGNED NOT NULL,   posted_on DATETIME NOT NULL,   author CHAR(255),   subject CHAR(255),   body MEDIUMTEXT,   thread_id INT UNSIGNED NOT NULL,   parent_id INT UNSIGNED NOT NULL,   level INT UNSIGNED NOT NULL,   thread_pos INT UNSIGNED NOT NULL,   PRIMARY KEY(id) );

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-43 saves messages and properly calculates thread_pos. Sample output is shown in Figure 10-4.

A threaded message board


<?php $board = new MessageBoard(); $board->go(); class MessageBoard {     protected $db;     protected $form_errors = array();     protected $inTransaction = false;     public function __construct() {         set_exception_handler(array($this,'logAndDie'));         $this->db = new PDO('sqlite:/usr/local/data/message.db');         $this->db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);     }     public function go() {         // The value of $_REQUEST['cmd'] tells us what to do         $cmd = isset($_REQUEST['cmd']) ? $_REQUEST['cmd'] : 'show';         switch ($cmd) {             case 'read':          // read an individual message               $this->read();               break;             case 'post':          // display the form to post a message               $this->post();               break;             case 'save':          // save a posted message               if ($this->valid()) { // if the message is valid,                   $this->save();    // then save it                   $this->show();    // and display the message list               } else {                   $this->post();    // otherwise, redisplay the posting form               }               break;             case 'show':          // show a message list by default             default:               $this->show();               break;         }     }     // save() saves the message to the database     protected function save() {         $parent_id = isset($_REQUEST['parent_id']) ?                      intval($_REQUEST['parent_id']) : 0;         // Make sure pc_message doesn't change while we're working with it.         $this->db->beginTransaction();         $this->inTransaction = true;         // is this message a reply?         if ($parent_id) {             // get the thread, level, and thread_pos of the parent message             $st = $this->db->prepare("SELECT thread_id,level,thread_pos                                 FROM pc_message WHERE id = ?");             $st->execute(array($parent_id));             $parent = $st->fetch();             // a reply's level is one greater than its parent's             $level = $parent['level'] + 1;             /* what's the biggest thread_pos in this thread among messages             with the same parent? */             $st = $this->db->prepare('SELECT MAX(thread_pos) FROM pc_message                     WHERE thread_id = ? AND parent_id = ?');             $st->execute(array($parent['thread_id'], $parent_id));             $thread_pos = $st->fetchColumn(0);             // are there existing replies to this parent?             if ($thread_pos) {                 // this thread_pos goes after the biggest existing one                 $thread_pos++;             } 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 */             $st = $this->db->prepare('UPDATE pc_message SET thread_pos = thread_pos + 1                           WHERE thread_id = ? AND thread_pos >= ?');             $st->execute(array($parent['thread_id'], $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 = $this->db->query('SELECT MAX(thread_id) + 1 FROM pc_message')                            ->fetchColumn(0);             $level = 0;             $thread_pos = 0;         }         /* insert the message into the database. Using prepare() and execute()         makes sure that all fields are properly quoted */         $st = $this->db->prepare("INSERT INTO pc_message (id,thread_id,parent_id,                        thread_pos,posted_on,level,author,subject,body)                        VALUES (?,?,?,?,?,?,?,?,?)");         $st->execute(array(null,$thread_id,$parent_id,$thread_pos,                              date('c'),$level,$_REQUEST['author'],                              $_REQUEST['subject'],$_REQUEST['body']));         // Commit all the operations         $this->db->commit();         $this->inTransaction = false;     }     // show() displays a list of all messages     protected function show() {         print '<h2>Message List</h2><p>';         /* order the messages by their thread (thread_id) and their position         within the thread (thread_pos) */         $st = $this->db->query("SELECT id,author,subject,LENGTH(body) AS body_length,                        posted_on,level FROM pc_message                        ORDER BY thread_id,thread_pos");         while ($row = $st->fetch()) {             // indent messages with level > 0             print str_repeat('&nbsp;',4 * $row['level']);             // print out information about the message with a link to read it             print "<a href='" . htmlentities($_SERVER['PHP_SELF']) .             "?cmd=read&amp;id={$row['id']}'>" .             htmlentities($row['subject']) . '</a> by ' .             htmlentities($row['author']) . ' @ ' .             htmlentities($row['posted_on']) .             " ({$row['body_length']} bytes) <br/>";         }         // provide a way to post a non-reply message         print "<hr/><a href='" .               htmlentities($_SERVER['PHP_SELF']) .               "?cmd=post'>Start a New Thread</a>";     }     // read() displays an individual message     public function read() {         /* make sure the message id we're passed is an integer and really         represents a message */         if (! isset($_REQUEST['id'])) {             throw new Exception('No message ID supplied');         }         $id = intval($_REQUEST['id']);         $st = $this->db->prepare("SELECT author,subject,body,posted_on                                  FROM pc_message WHERE id = ?");         $st->execute(array($id));         $msg = $st->fetch();         if (! $msg) {             throw new Exception('Bad message ID');         }         /* don't display user-entered HTML, but display newlines as         HTML line breaks */         $body = nl2br(htmlentities($msg['body']));         // display the message with links to reply and return to the message list         $self = htmlentities($_SERVER['PHP_SELF']);         $subject = htmlentities($msg['subject']);         $author = htmlentities($msg['author']);         print<<<_HTML_ <h2>$subject</h2> <h3>by $author</h3> <p>$body</p> <hr/> <a href="$self?cmd=post&parent_id=$id">Reply</a> <br/> <a href="$self?cmd=list">List Messages</a> _HTML_;     }     // post() displays the form for posting a message     public function post() {         $safe =  array();         foreach (array('author','subject','body') as $field) {             // escape characters in default field values             if (isset($_POST[$field])) {                 $safe[$field] = htmlentities($_POST[$field]);             } else {                 $safe[$field] = '';             }             // make the error messages display in red             if (isset($this->form_errors[$field])) {                 $this->form_errors[$field] = '<span style="color: red">' .                    $this->form_errors[$field] . '</span><br/>';             } else {                 $this->form_errors[$field] = '';             }         }         // is this message a reply         if (isset($_REQUEST['parent_id']) &&         $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" />',                     $parent_id);         // if no subject's been passed in, use the subject of the parent         if (! strlen($safe['subject'])) {              $st = $this->db->prepare('SELECT subject FROM pc_message WHERE id = ?');              $st->execute(array($parent_id));              $parent_subject = $st->fetchColumn(0);             /* prefix 'Re: ' to the parent subject if it exists and                doesn't already have a 'Re:' */             $safe['subject'] = htmlentities($parent_subject);             if ($parent_subject && (! preg_match('/^re:/i',$parent_subject))) {                 $safe['subject'] = "Re: {$safe['subject']}";             }           }         } else {             $parent_field = '';         }     // display the posting form, with errors and default values     $self = htmlentities($_SERVER['PHP_SELF']);     print<<<_HTML_ <form method="post" action="$self"> <table> <tr>  <td>Your Name:</td>  <td>{$this->form_errors['author']}      <input type="text" name="author" value="{$safe['author']}" /> </td> <tr>  <td>Subject:</td>  <td>{$this->form_errors['subject']}      <input type="text" name="subject" value="{$safe['subject']}" /> </td> <tr>  <td>Message:</td>  <td>{$this->form_errors['body']}      <textarea rows="4" cols="30" wrap="physical"                name="body">{$safe['body']}</textarea> </td> <tr><td colspan="2"><input type="submit" value="Post Message" /></td></tr> </table> $parent_field <input type="hidden" name="cmd" value="save" /> </form> _HTML_;     }     // validate() makes sure something is entered in each field     public function valid() {         $this->form_errors = array();         if (! (isset($_POST['author']) && strlen(trim($_POST['author'])))) {             $this->form_errors['author'] = 'Please enter your name.';         }         if (! (isset($_POST['subject']) && strlen(trim($_POST['subject'])))) {             $this->form_errors['subject'] = 'Please enter a message subject.';         }         if (! (isset($_POST['body']) && strlen(trim($_POST['body'])))) {             $this->form_errors['body'] = 'Please enter a message body.';         }         return (count($this->form_errors) == 0);     }     public function logAndDie(Exception $e) {         print 'ERROR: ' . htmlentities($e->getMessage());         if ($this->db && $this->db->inTransaction) {             $this->db->rollback();         }         exit();     } } ?>

To properly handle concurrent usage, 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 PDO's beginTransaction( ) and commit( ) methods to accomplish this. Note that logAndDie( ), the exception handler, rolls back the transaction when appropriate if an error occured inside the transaction. Although PDO always calls rollback( ) at the end of a script if a transaction was started, explicitly including the call inside logAndDie( ) makes clearer what's happening to someone reading the code.

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. Example 10-44 shows how to display just the first message in each thread and any replies to that first message.

Limiting thread depth

<?php $st = $this->db->query(     "SELECT * FROM pc_message WHERE level <= 1 ORDER BY thread_id,thread_pos"); while ($row = $st->fetch()) {     // 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. A popular one is FUDForum (, and there are a number of others listed at .

PHP Cookbook, 2nd Edition
PHP Cookbook: Solutions and Examples for PHP Programmers
ISBN: 0596101015
EAN: 2147483647
Year: 2006
Pages: 445

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: