Welcome Guest, Not a member yet? Register   Sign In
Trouble figuring out how to get a count from another table (multiple times)
#1

[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 Smile
#2

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

[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 Wink
#4

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




Theme © iAndrew 2016 - Forum software by © MyBB