Advanced PHP/MySql Query?

Databases
1

I don’t have much experience with advanced queries, but this is what I am trying to accomplish:

a table in my database has a field for “referrer” which records the name of the person who referred that user to the site. I need to write a query that will, for each user in the table, count how many referrals they have, and output their information with the referral count.

example of table:

FNAME LNAME EMAIL REFERRER

John Doe jdoe@email.com janedoe@yahoo.com
Cary Grant cgrant@email.com janedoe@yahoo.com
Mary Smith msmith@email.com jdoe@email.com

Example of query output:

FNAME LNAME EMAIL REFERRALS

Jane Doe janedoe@yahoo.com 2
John Doe jdoe@email.com 1

I can write a query to count how many referrals a user has if I pass the user’s info in the query (WHERE referrer = ‘example’), but I don’t know how to count AND output data for all of the users in the database in one query. I am thinking this is a subquery situation, but I don’t know where to begin. Any help is greatly appreciated!

2 

SELECT 
    t1.FIRSTNAME
  , t1.LASTNAME
  , t1.EMAIL
  , COALESCE(t2.REFERRALS, 0) AS REFERRALS
FROM tablename AS t1
LEFT OUTER JOIN
  (SELECT 
       EMAIL
     , COUNT(*) AS REFERRALS
   FROM tablename
   GROUP BY EMAIL
  ) AS t2
ON t1.EMAIL = t2.EMAIL
3

Thank you! That’s exactly what I was looking for. I had forgotten all about how to do what you did there (so much so in fact I can’t even remember what it is called at the moment). Time to brush up on queries again.

Again, really appreciate it.

4

Okay, well I ran the code and the only problem is that for every user that has referrals, it is displaying that user one time for each referral with a count of 1 on each row.

What can I add that will show each person only one time with their total referral count?

5 

SELECT 
    t1.FIRSTNAME
  , t1.LASTNAME
  , t1.EMAIL
  , COALESCE(t2.REFERRALS, 0) AS REFERRALS
FROM tablename AS t1
LEFT OUTER JOIN
  (SELECT 
       REFERRER
     , COUNT(*) AS REFERRALS
   FROM tablename
   GROUP BY REFERRER
  ) AS t2
ON t1.EMAIL = t2.REFERRER

I counted the emails instead of the referrers in the subquery.