3 table search

[Wayward780]

I'm working with the PHPBB databases to pull some custom data. If you're familiar with the application I'd like to pull a few records and display them like the viewforum.php page does.

Basically it pulls the topic name, author, views, replies, last post datetime and last post username.

The three tables in my SQL statement are phpbb_posts, phpbb_topics, phpbb_users.

Here's my SQL statement so far:

SELECT phpbb_topics.topic_id, phpbb_topics.topic_title, phpbb_topics.topic_replies, phpbb_topics.topic_views, phpbb_topics.topic_last_post_id, phpbb_users.username AS topic_username, phpbb_posts.post_time AS topic_last_post_time 
FROM phpbb_topics, phpbb_users, phpbb_posts 
WHERE phpbb_topics.forum_id = 2 AND phpbb_topics.topic_poster = phpbb_users.user_id AND phpbb_posts.post_id = phpbb_topics.topic_last_post_id 
ORDER BY topic_views DESC LIMIT 5

This works so far but the problem I'm running in to is grabbing the last posted author's username, how do I fit that in to my statement? I need one last header to turn up called topic_last_post_username. Any help would be appreciated.

My assumption is that I can't do this and that I actually need to have another SQL statement going to get that specific information aside from my current statment. Thanks.

Wayward780

[frezno]

i don't know the tablestructure but use LAST_INSDERT_ID() for the post_table.
There you get all necessary data (probabely the user_id of the writer of the post).
And with this id you get the name.

[Wayward780]

There's got to be a way for accomplishing what I need. Here's the tables I'm working with (only showing the relevant headers here):

phpbb_topics
----------------------------------------------------------------
topic_id | forum_id | topic_title | topic_poster | topic_views | topic_replies | topic_last_post_id

phpbb_posts
----------------------------------------------------------------
post_id | topic_id | forum_id | poster_id | post_time

phpbb_users
----------------------------------------------------------------
user_id | username

What I need to accomplish is to get the most popular 5 posts from the phpbb_topics database with their id, author username, replies, views, last posted time, and last posted author username.

I wrote this SQL statement to accomplish this but am having trouble getting the last posted author's username:

SELECT phpbb_topics.topic_id, phpbb_topics.topic_title, phpbb_topics.topic_replies, phpbb_topics.topic_views, phpbb_topics.topic_last_post_id, phpbb_users.username AS topic_username, phpbb_posts.post_time AS topic_last_post_time 
FROM phpbb_topics, phpbb_users, phpbb_posts 
WHERE phpbb_topics.forum_id = 2 AND phpbb_topics.topic_poster = phpbb_users.user_id AND phpbb_posts.post_id = phpbb_topics.topic_last_post_id 
ORDER BY topic_views DESC LIMIT 5

My assumption is that I need to approach this differently somehow. Any help is appreciated, thanks.

Wayward780