PHP SQL counting number of matches for query

Question

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.

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

Answer 1

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

Answer 2

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

Answer 3

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;