+- 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: Trouble figuring out how to get a count from another table (multiple times) (/showthread.php?tid=1907)
Trouble figuring out how to get a count from another table (multiple times) - El Forum - 07-04-2007
[eluser]awpti[/eluser]
Okay, here's the important stuff first:
http://www.awpti.org/source/blog.phps
http://www.awpti.org/source/front.phps (View Source on this one)
Only relevant function (for the moment) is index().
In here, I do a single query to get the latest 3 ($this->awNewsLimit) posts. Good stuff, works great.
My problem is for each post, I need to get an count of all related comments so I can fill in the Comments (##) portion of the output in front.php
Is this a query issue (should I be doing some type of nested or joined query)?
Or is this an issue of just doing queries in the front.php script?
Wether it matters or not, I'm using CF's fantastic View library.
I'm stumped. Yet again
Trouble figuring out how to get a count from another table (multiple times) - El Forum - 07-04-2007
[eluser]Bulk[/eluser]
The way I would normally do it with a sub query:
Code:
$query_news = 'SELECT *,(SELECT count(*) FROM comments_table WHERE blog_id=id_field) as num_comments FROM awNews ORDER BY NewsID DESC LIMIT '.$this->awNewsLimit;id_field should be what ever the id field of the awNews table is.
Trouble figuring out how to get a count from another table (multiple times) - El Forum - 07-04-2007
[eluser]the real rlee[/eluser]
Hi, rather than use a second query to count all rows you could use MySQL's [url="http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_found-rows"]FOUND_ROWS()[/url] function to count all the rows that would have been found without the LIMIT
Trouble figuring out how to get a count from another table (multiple times) - El Forum - 07-04-2007
[eluser]awpti[/eluser]
I finally found a solution after a few hours of research and then stumbling upon an individual with a bit more SQL experience than I:
SELECT *, IF( awNewsComments.comments_news_id IS NOT NULL , count( * ) , 0 ) AS num_comments FROM awNews LEFT JOIN awNewsComments ON awNews.news_id = awNewsComments.comments_news_id GROUP BY awNews.news_id ORDER BY awNews.news_id DESC LIMIT 3;;