I am trying to create my own forum system and am trying to replicate the way phpBB sorts posts with recent replies to the top of the list. I have all posts and replies in one table and am trying not to add a last_reply field to the table.
here goes,
I have one table lets call it T1
for this purpose we can say T1 has 3 fields:
message_ID - Auto_Incremented Primary Key (each one is unique)
parent_ID - INT (where parent_ID = 0 if not a reply and parent_id=message_id of post relpied to)
lastdate - TIMESTAMP (date posted)
Here is what I want to do with this:
First I want to select all messages with such that parent_id=0
then I want this sorted in the order of which has the most recent reply so it would have to sort through the replies of each and pick out the most recent date and sort the list of parent_id=0.
this just has my head spinning
I am using mysql 3.22
Is there a way for me to do this in just sql? if not php? am I just going to have to do something different?
I would be soooo grateful for any help!