CODEWITHSUNDEEP
phpmysqlfunctionranking
CustomNet
CustomNet

Reputation: 652

PHP MySQL Top 5 Referers Function

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?

Upvotes: 0

Views: 191

Answers (3)

iRaS
iRaS

Reputation: 2136

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.

Upvotes: 0

symcbean
symcbean

Reputation: 48387

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;

Upvotes: 0

Cal
Cal

Reputation: 7157

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.

Upvotes: 2

Related Questions