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?

Answer 1

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.