PHP Cookbook

10.15. 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 ...