Results 2 tables, sort based on 2nd

ndorfnz

Very challenged with this task and looking to understand best way to accomplish.

1st off running Mysql 4.0, so cannot use subqueries as only available on 4.1

2 tables...Topics and Posts

Topics contains:
ID
TopicName
user

Posts contains:
ID
TopicID
Name
TimeStamp
Post

On my summary page, I am displaying a summary of the 2 tables.
Topic Name | User | Number Of Posts | Date of Last Update (most recent post in posts table)

Clicking into the topic name then displays all the posts.

I cannot figure out how to sort the Date Of Last Update in decending order!? Below is the code (which I know is not correct), but at least you can get a feel for the direction. I have been attempting to write in MySQL to no avail either.

$topic_query = mysql_query("SELECT * FROM topics ORDER BY ID desc");
	while ($topic = mysql_fetch_array($topic_query)) {

    if($odd = $rowCount%2 )
            {
                $color = "#3f3f3f";
            }
            else
            {
                $color = "#666666";
            }
    $topic_posts = mysql_query("SELECT * FROM posts where topicID = '$topic[ID]' ORDER BY ID"); 
    $lastUpdate = mysql_query("SELECT max(TimeStamp) FROM posts where topicID = '$topic[ID]' ORDER BY ID");
    list ($lastUpdateDate) = mysql_fetch_row($lastUpdate); 
    $time = $lastUpdateDate; 
    $today = date( "m-d-y g:ia", $time );

//note just echoing the data out in an HTML table below here...

Appreciate any guidance or direction!

Thanks!

Piranha

First: You should never have to do a query in a loop that steps through another query. It should be possible to do it in one query.

Second: If you have an old version of MySQL change it. Change provider if you have to, any good provider will have MySQL version 5.x.

Third: You probably don't need subqueries for this, a join and a group by should do it, something like this:

select topics.name, topics.user, count(posts.id) as noposts, max(posts.timestamp) as last update
from topics
left join posts on topics.id = posts.topicid
group by topics.id
order by posts.timestamp

You could order by posts.id instead.

It could be that group by and order by should change place. It could as well be that it will use the wrong timestamp, if it is like that you need a database that can handle subqueries.