PHP SQL counting number of matches for query
How would i go about counting the number of rows that match two variables?
I have a table called: users
and fields called: username & referral
I have another table called: comments
and fields called: comment_username
This is the situation, I need to fetch the number of referrals with at least 10 comments (rows in the comments' table) that a specific user has referred.
So i was thinking the code should be something like this crude outline.
开发者_JAVA百科 $username = 'bob';
$validrefferalcount = 0;
function validreferrals($username){
$referreduser = SQL select * from users where referral='$username';
foreach ($referreduser) {
$numberofcomments = SQL count * from comments where comment_username ='$referreduser';
if ($numberofcomments >= 10){
$validreferralcount = $validreferralcount + 1;
}
}
return $validreferralcount;
}
I apologize for the bad syntax, etc...
Thanks for reading.
What about this query :
SELECT COUNT(*) FROM (
SELECT username, COUNT(*) AS c_comments
FROM users
JOIN comments ON username = comment_username
WHERE referral = 'referral'
GROUP BY username
) t
WHERE t.c_comments > 10;
You should use JOIN in your case. Something like (if I understand correctly)
SELECT count(*) FROM users
RIGHT JOIN comments ON comments.comment_username = users.username
WHERE users.referral = '$username'
You can find more information here
Since my actual post count does not allow for comments yet, some additions to christians answer.
A having clause against the count, so the >= 10 condition is matched would be a good idea
精彩评论