• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
CodeIgniter Forum System / Discussion Area

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

Sounds like everything I ever started.

Sounds like life... something more important always comes along

[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.

/* 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.

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.


i think this project died, can anyone start forum with ion auth

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  

  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.