phpBB Database

Dvector

I'm working with the database from phpBB trying to get the most recent posts from a certain forum. The problem I am having is it selects the original topics and all the replies to those topics, and I would like to get just the topics.

Here is the SQL statement I am working with (I didnt write it, just modifying it to try and suit what I need):

SELECT phpbb_topics.forum_id, phpbb_topics.topic_id AS topic_id, 
phpbb_topics.topic_title AS topic_title, MAX( phpbb_posts.post_time ) AS post_time, 
MAX( phpbb_posts.post_id ) AS post_id
FROM phpbb_topics, phpbb_posts, phpbb_forums
WHERE phpbb_topics.topic_id = phpbb_posts.topic_id
AND phpbb_topics.forum_id = 8
GROUP BY phpbb_posts.post_id
ORDER BY post_time DESC 
LIMIT 10

I have tried selecting distinct rows from phpbb_topics.topic_id, but I get SQL errors then, so I must not understand the distinct keyword well enough. There might be a way to do this with a JOIN, but I'm not familiar with them. Any ideas? I can post the table structures and any other information if you need.

Thanks.

tekky

SELECT phpbb_topics.forum_id, phpbb_topics.topic_id AS topic_id, 
phpbb_topics.topic_title AS topic_title, MAX( phpbb_posts.post_time ) AS post_time, 
MAX( phpbb_posts.post_id ) AS post_id
FROM phpbb_topics, phpbb_posts, phpbb_forums
WHERE phpbb_topics.topic_id = phpbb_posts.topic_id
AND phpbb_topics.forum_id = 8
GROUP BY phpbb_topics.forum_id
ORDER BY post_time DESC 
LIMIT 10

try this maybe? (using topics table and forum_id to group them

Dvector

It looks like that only selects the oldest post from that forum. Is there a way to select a distinct topic_id? I dont understand why I get an error when trying that.