Getting time and author of last reply to a thread on a forum, can't use subquery

[king arthur]

I have a slight problem. I have just realised my server is running mysql version 4.0 and therefore cannot handle subqueries. I am writing a simple messageboard system, currently the home page of the messageboard displays the different board sections and the most recent seven thread topics in each section. Unfortunately it currently only looks at the time of the original post, and not the time of the last reply in the thread as I would like it to.

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
[code]
$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";
[/code]
$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?

[fenway]

Well, you can LEFT JOIN in the table containing the reply_id, and if it's not NULL, the values can be meaningful.

[king arthur]

Right, instead of that, I decided on a slightly different approach of attack. Now, the first message in a thread has its own msg_id in the reply_id column, so in effect it is a reply to itself. This ensures that every thread has at least one reply as far as the database is concerned.

Now my query looks like
[code]
"select a.msg_id, b.username, count(*) as replies, unix_timestamp(max(b.time_posted)) as last_time
from mb_messages as a, mb_messages as b
where a.section_id=$section_id and a.reply_id=a.msg_id and b.reply_id=a.msg_id and b.valid_from<=$valid_to and b.valid_to>$valid_from
group by a.msg_id order by last_time desc limit $max_preview_rows";
[/code]

In order to keep things relatively simple I now pull the rest of the data I need, message subject, text etc, in a follow up query.

This seems to work in that it orders the thread topics in order of the time of the last reply. I also get the correct time of last reply, and number of replies (plus one) pulled out. The only thing that isn't correct is the name of the poster of the last reply. It seems to be giving me the name of the poster of the first reply not the last one. I guess this is because I'm not actually determining the row containing the last reply anywhere before the order by clause. Could I refine this query so I get the correct name? Anyone?

[fenway]

This probably has to do with the GROUP BY you're using for MAX(time_posted) -- b.username will be an arbitrary one of the group, unless I've misunderstood.

[king arthur]

Yeah I guess so, so selecting the columns from individual rows that don't appear in a group by clause is meaningless even though MySQL lets you do it. So for now I think I'll have to admit defeat and use a separate query for the name until such time as I know more what I'm doing!