Message board problem

[carole2]

Very Simple Message board with the following table structure:

CREATE TABLE forum (
id mediumint(9) NOT NULL auto_increment,
subject varchar(100) NOT NULL,
name varchar(100) NOT NULL,
message text NOT NULL,
date_submit datetime NOT NULL,
replyid mediumint(9) DEFAULT '0' NOT NULL,
number_replys int(11) DEFAULT '0' NOT NULL,
PRIMARY KEY (id),
UNIQUE id (id)
);

Note: replyid is the id number of the original message. All original messages will have a replyid=0. When a reply is made to the original message the replyid of the replying message will be set to the id of the original message.

The original messages (not replys) are echod out in the main message table as follows:

$sql = "SELECT *, DATE_FORMAT(date_submit, '".$date_format_forum."') AS date_written FROM forum WHERE replyid = 0 ORDER BY date_submit DESC LIMIT $x,$number_per_page";

To view the original message and replys to the original message, the user just clicks on the subject(hyperlink) of the message appearing in the main table.

My problem is that if someone replys to an older message, the reply technically is the most recent post (and I would like the original message that has just recieved a reply to appear in the first position in the main table), however currently the main table now is ordering according to the date submitted on original messages(replyid=0) only, it doesnt take into account the date on replied messages to the original post.

What is the best way to solve this problem? I hope I explained it well.

Thanks
Carole

[yelvington]

If I understand your question correctly, you want to sort and display the basenotes (where replyid=0) by the modification date of the most recent reply, decending.

I notice you have a "number_replies" column in the table. Presumably this means you are updating the parent at the same time you are creating or updating the child.

I would suggest that you add a timestamp column "date_replied" to your table structure. A timestamp is automatically updated when any update is performed on that record. So, when you increment the value of number_replies, the timestamp will be updated as well.

Instead of ORDER BY date_submit DESC, your retrieval query should include ORDER BY date_replied DESC.

You will want to keep date_submit, because it indicates the creation date of the note.

[carole2]

Great thanks, I think thats the best way, i just wanted to get some other opinions, and you understood perfectly.

Thanks
Carole.