PHP MySQL Top 5 Referers Function

Question

I have a table called users which looks like:

-id
-email
-login
-admin
-coins
-cash
-premium
-IP
-pass
-ref
-signup
-online
-promote
-activate
-banned
-rec_hash
-country
-c_changes
-sex
-daily_bonus

If say user with id 81 referred 10 people then those 10 people would have "81" in their ref column.

I would like to create a top 5 referral table but I'm having trouble with the query and displaying that in PHP, would anybody be able to help?

I FORGOT TO MENTION IF THEY HAVE NO REFERRAL IT SHOWS AS 0 HOW WOULD I EXCLUDE 0 FROM BEING SHOWN AS A REFERRAL?

score 2 · Accepted Answer · answered Nov 15 '12 at 23:33

2

You can do it in a single SQL statement like this:

SELECT ref, COUNT(*) AS num FROM users
GROUP BY ref ORDER BY num DESC LIMIT 5

But that will just get you the 5 IDs, rather than their user rows. You can then perform a further query to get the actual rows. Alternatively, use the above query with a join to do it all in one.

answered Nov 15 '12 at 23:33

Cal

7,067
25
28

How would I get their `login` by using your query? – CustomNet Nov 15 '12 at 23:35
@cal Do you agree with me, a normalization for this table will be better? In other word, creating another table for reference? – SaidbakR Nov 15 '12 at 23:35
@LiamGordon just add login, after SELECT – SaidbakR Nov 15 '12 at 23:38

score 0 · Answer 2 · answered Nov 15 '12 at 23:41

IF THEY HAVE NO REFERRAL IT SHOWS AS 0

messy design - this should be null. Regardless...

 SELECT u.login, ilv.referred
 FROM
 (SELECT ref, COUNT(*) AS referred
   FROM users
  WHERE ref IS NOT NULL
  AND ref>0
  GROUP BY ref
  ORDER BY COUNT(*) DESC
  LIMIT 0,5) ilv
INNER JOIN users u
ON ilv.ref=users.id
ORDER BY ilv.referred DESC;

score 0 · Answer 3 · answered Nov 15 '12 at 23:42

0

Or and SQL like this:

SELECT u.*, COUNT(*) as referrers FROM users r JOIN users u ON r.ref = u.id
GROUP BY u.id ORDER BY referrers DESC LIMIT 5

It is faster to use just one statement even with a join on the same table.

answered Nov 15 '12 at 23:42

iRaS

1,958
1
16
29

PHP MySQL Top 5 Referers Function

3 Answers3