I've created a simple forum system for my website and am having a problem when displaying the list of threads in a forum.

Basically I have 3 separate forums that each hold a number of threads which in turn have a number of posts in them.

My database for this is split into 3 tables; details below

forums - holds the info of each seperate forum

forum_id tinyint(3) PRIMARY
forum_name varchar(255)
description tinytext


forum_topics - Holds info on the topics in each forum

topic_id smallint(5) PRIMARY
forum_id tinyint(3)
topic_title tinytext
create_time datetime
started_by int(11)


forum_posts - Holds info about the forum posts

post_id mediumint(8) PRIMARY
topic_id smallint(5)
post_text text
post_time datetime
posted_by int(11)


The query that doesn't work is supposed to create a table of all the threads in a particular forum, ordered with the thread that was last updated first down to the least recently updated thread at the bottom.

I should be able to do this by ordering on the post_id column in the forum_posts table as the biggest value here will be the last post created.

The query I am using is below:

select distinct ft.topic_id, forum_id, topic_title,
date_format(create_time, '%b %e %Y at %r') as created_time, started_by from forum_topics as ft
left join forum_posts on ft.topic_id = forum_posts.topic_id
where forum_id = $_GET[id]
order by post_id desc limit $num_topics";


This query gives me all the information I need but it's just not ordered correctly, it just orders by the last created topic, not the last updated topic.

I have tried this with a right join but that just seems to give me multiple table rows for each topic.

$_GET[id] is the id of the forum that the user is in.

I hope this makes sense and would really appreciate help.

If this isn't clear, I'll try and explain it better, just tell me what is unclear.

Cheers,

Nick