Here is one way to make a simple message board database. This is a rudimentary example. The message author should really have its own table and there is no timestamp.
Notice there is no table for threads. There are only messages.
Code: Select all
DESCRIBE `message`
# +----------+------------------+------+-----+---------+----------------+
# | Field | Type | Null | Key | Default | Extra |
# +----------+------------------+------+-----+---------+----------------+
# | msg_num | int(10) unsigned | NO | PRI | | auto_increment |
# | reply_to | int(10) unsigned | NO | | 0 | |
# | author | varchar(32) | NO | | | |
# | subject | varchar(128) | NO | | | |
# | body | text | NO | | | |
# +----------+------------------+------+-----+---------+----------------+
SELECT * FROM `message`
# +---------+----------+--------+----------------+---------------+
# | msg_num | reply_to | author | subject | body |
# +---------+----------+--------+----------------+---------------+
# | 1 | 0 | Bill | First Post | Hello, World |
# | 2 | 1 | Amy | Re: First Post | Hi, Bill. |
# | 3 | 0 | George | Second Thread | Knock, knock. |
# | 4 | 1 | Bill | Re: First Post | Hi, Amy. |
# +---------+----------+--------+----------------+---------------+
Thread starters are those messages that reply to the imaginary message 0. To retrieve a list of threads, find the thread starters.
Code: Select all
SELECT * FROM `message` WHERE `reply_to` = 0
# +---------+----------+--------+---------------+---------------+
# | msg_num | reply_to | author | subject | body |
# +---------+----------+--------+---------------+---------------+
# | 1 | 0 | Bill | First Post | Hello, World |
# | 3 | 0 | George | Second Thread | Knock, knock. |
# +---------+----------+--------+---------------+---------------+
The next query retrieves the messages in the thread started by message 1. To allow this query to be simple, inserted messages must always reply to a thread starter. Notice that message 4 uses reply_to=1 even though it is a response to message 2.
Code: Select all
SELECT * FROM `message`
WHERE `msg_num` = 1 OR `reply_to` = 1
ORDER BY `msg_num` ASC
# +---------+----------+--------+----------------+--------------+
# | msg_num | reply_to | author | subject | body |
# +---------+----------+--------+----------------+--------------+
# | 1 | 0 | Bill | First Post | Hello, World |
# | 2 | 1 | Amy | Re: First Post | Hi, Bill. |
# | 4 | 1 | Bill | Re: First Post | Hi, Amy. |
# +---------+----------+--------+----------------+--------------+
The next queries demonstrate one way to insert a reply to message 4 while acknowledging the requirement to reply to the thread starter (message 1). Warning: Do not use a persistent connection. MySQL variables live as long as the connection. If multiple clients use the same script, they all use the same variables.
Code: Select all
SET @msg_num = (SELECT `reply_to` FROM `message` WHERE `msg_num` = 4)
# +---+
SET @subject = (SELECT `subject` FROM `message` WHERE `msg_num` = @msg_num)
# +---+
INSERT INTO `message` (`reply_to`, `author`, `subject`, `body`)
VALUES(@msg_num, 'Amy', CONCAT('Re: ', @subject), 'Hi, again, Bill.')
# +---+
SELECT * FROM `message`
WHERE `msg_num` = LAST_INSERT_ID()
# +---------+----------+--------+----------------+------------------+
# | msg_num | reply_to | author | subject | body |
# +---------+----------+--------+----------------+------------------+
# | 5 | 1 | Amy | Re: First Post | Hi, again, Bill. |
# +---------+----------+--------+----------------+------------------+
Edit: This post was recovered from search engine cache.