+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: CodeIgniter Forum System / Discussion Area (/showthread.php?tid=11671)
CodeIgniter Forum System / Discussion Area - El Forum - 06-02-2009
[eluser]TheFuzzy0ne[/eluser]
[quote author="Colin Williams" date="1243980058"]Sounds like all my CI community stuff too
[/quote]Sounds like everything I ever started.
CodeIgniter Forum System / Discussion Area - El Forum - 06-02-2009
[eluser]Dam1an[/eluser]
Sounds like life... something more important always comes along
CodeIgniter Forum System / Discussion Area - El Forum - 06-30-2009
[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` descDoes 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 ASCEdit 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
CodeIgniter Forum System / Discussion Area - El Forum - 03-26-2011
[eluser]dhaulagiri[/eluser]
i think this project died, can anyone start forum with ion auth