[eluser]watonlyme[/eluser]
[quote author="Xeoncross" date="1221956435"]Ok, I need some MySQL Help for this.
I have four tables that store the 1) Forum Categories, 2) Forums, 3) Topics, 4) Posts/Replies.
When you browse a forum I list all the topics (limit 10) in that forum - but all good forums also show the
USERNAME/ICON and the
COUNT() of posts/replies to that topic.
Originally I was doing MASSIVE Left Joins in MYSQL that joined the
USERS,
COUNT(*) POSTS, and
TOPICS tables so that I could get this data. However, after filling my DB with 3,000 users, 3,500 topics, and 13,000 posts/replies I decided that I would join the
TOPICS/USERS tables so that I could show data on the TOPIC author - and then do a separate query to COUNT() the number of posts that match the topic id.
Code:
/* Fetch Topics and User data */
SELECT ci_forum_test_topics.*, ci_forum_test_users.email, ci_forum_test_users.username
FROM (`ci_forum_test_topics`)
JOIN `ci_forum_test_users` ON ci_forum_test_users.id = ci_forum_test_topics.user_id
WHERE `forum_id` = '1' AND `status` = 1
ORDER BY `date` desc LIMIT 10
/* Count the Number of Replies */
SELECT COUNT(*) AS `numrows` FROM (`ci_forum_test_posts`) WHERE `topic_id` = '3473' AND `status` = 1 ORDER BY `date` desc
Does anyone know of a better way to do this? Some forums keep a
post_count column in each topic row so that they know how many replies there are to that topic. This saves them from needing to run a COUNT() query.
However, don't think that I want to mess with updating that number for each post that is added/deleted.[/quote]
Hello there!
Please view the appended SQL script. This is apparently working for me, so it might just work on your forum system.
Code:
SELECT a.id, a.name, b.id, b.name, b.desc, COUNT(c.id) AS numtopics, COUNT(d.id) AS numposts FROM ci_forums_cat AS a LEFT JOIN (SELECT ci_forums_forums.id, ci_forums_forums.name, ci_forums_forums.desc,ci_forums_forums.cid FROM ci_forums_forums) AS b ON a.id = b.cid LEFT JOIN (SELECT ci_forums_topics.id, ci_forums_topics.fid FROM ci_forums_topics) AS c ON c.fid = b.id LEFT JOIN (SELECT ci_forums_posts.id, ci_forums_posts.tid FROM ci_forums_posts) AS d ON d.tid = c.id GROUP BY a.id,b.id ASC
Edit as necessary. If it is not what you're asking for, please notify me. Thank you for reading this, and I hope this would help your forum system.
Regards,
watonlyme