SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: 3 table search

  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2005
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    3 table search

    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:

    Code:
    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

  2. #2
    does not play well with others frezno's Avatar
    Join Date
    Jan 2003
    Location
    Munich, Germany
    Posts
    1,391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.

  3. #3
    SitePoint Evangelist
    Join Date
    Mar 2005
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    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:

    Code:
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •