MYSQL Query. Selecting my latest topics, but ordering them by latest comments?

Discussion in 'PHP' started by dherald, Oct 16, 2010.

0
  1. #1
    Hi everyone,

    I have this query below. It selects my forum topics and displays them in a widget, showing users the latest forum posts

    $forum_query = mysql_query("SELECT a.id, a.title, a.user_id, a.answers_count, a.votes, a.views_count, b.time
    FROM forum_topics a INNER JOIN orum_answers b
    WHERE a.id = b.topic_id
    ORDER by id DESC LIMIT 8
    ");

    However, what I want to do is to sort the forum posts by time (b.time)

    But when I do so, I get a lot of duplicate entries, because a lot of users post in the same thread.

    Can anyone please help me modify this query?
     
    dherald, Oct 16, 2010 IP
  2. dherald

    dherald Active Member

    Messages:
    408
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    55
    #2
    Since I just solved this myself, might as well post it here:

    $forum_query = mysql_query("SELECT a.id, a.title, a.user_id, a.answers_count, a.votes, a.views_count, b.time
    FROM forum_topics a INNER JOIN orum_answers b
    WHERE a.id = b.topic_id
    ORDER by id DESC LIMIT 8
    ");

    That's the old query, right?

    So what I did is this
    SELECT FROM (
    SELECT a.id, a.title, a.user_id, a.answers_count, a.votes, a.views_count, b.time
    FROM forum_topics a INNER JOIN orum_answers b
    WHERE a.id = b.topic_id
    ORDER by time DESC LIMIT 8)
    AS dummytable GROUP BY ID ORDER BY TIME DESC LIMIT 5

    This pretty much eliminates all duplicates for "id" in the topics table and gives me the result that I want.
     
    dherald, Oct 16, 2010 IP