The messages are held in a table with columns msg_id, msg_text_id, time_posted, username, reply_id, and a load of other stuff which holds no relevance to this question. The query right now to get the seven message topics is
$query = "select msg_id as message_id, username, unix_timestamp(time_posted) as unix_time, message_text, subject from mb_messages, msg_text, msg_subject where mb_messages.msg_text_id=msg_text.msg_text_id and mb_messages.msg_subject_id=msg_subject.msg_subject_id and section_id=$section_id and reply_id=0 and valid_from<=$valid_to and valid_to>$valid_from order by valid_from desc, valid_to desc, time_posted desc limit $max_preview_rows";$max_preview_rows is of course the number of topics I want to display, the valid_from and valid_to columns are for a calender system and for a normal message would be set the same as time_posted, the reply_id is either 0 for a new topic or the msg_id of the message being replied to. Now, so far the data pulled out allows me to display "Subject: $subject posted by $username at $unix_time" but if there are any replies I want to add " last reply at $last_reply_time by $whoever". I also want to order the results by the last reply time instead of the time posted.
I was going to try using subqueries to get this info but I just discovered that I can't as the hosting I'm using for the site this message board is for is running mysql 4.0 and as I understand it, subqueries only came in with 4.1, which would explain why all I got was syntax errors when I tried. So, can anyone see another way of doing it?