Search code examples
phpsqlforumpunbb

SQL- how to extract forum topics that are not repeats?

On the home page of my website I want to display the latest posts to the forum however I don't want to show the same topic twice. How can I modify the code below to do this?

http://punbb.informer.com/wiki/punbb13/integration#recent_10_posts

Basically show the latest posts, but only once for each forum topic/thread.

Solution

  • If you want only one value per topic, you could group by topic, and from each topic select the most recent post. Then, you could choose the top 10 topics.

    I'll write it in SQL, and you can translate that to PHP:

    SELECT p.id, p.message, o.subject
FROM
((SELECT t.id
  FROM posts AS p LEFT JOIN topics AS t ON p.topic_id = t.id
  GROUP BY t.id
  HAVING p.posted = MAX(p.posted) ) ids LEFT JOIN topics AS t ON ids.id = t.id) o
                                        LEFT JOIN posts AS p ON o.id = posts.topic_id
ORDER BY p.posted DESC
LIMIT '0,10'