Welcome Guest, Not a member yet? Register   Sign In
CodeIgniter Forum System / Discussion Area
#21

[eluser]TheFuzzy0ne[/eluser]
[quote author="Colin Williams" date="1243980058"]Sounds like all my CI community stuff too Smile[/quote]

Sounds like everything I ever started.
#22

[eluser]Dam1an[/eluser]
Sounds like life... something more important always comes along
#23

[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
#24

[eluser]dhaulagiri[/eluser]
i think this project died, can anyone start forum with ion auth




Theme © iAndrew 2016 - Forum software by © MyBB