help needed!?!

[seany123]

im making a page which shows the top 20 refferers on my website but i dont really fancy adding another database value for it so im gonna try doing it using the values i have already:

 

basically every user on the website has a value in the table called ref.. if its >= 1 then it corresponds to another users ID....

 

so what i need to do is find the users with the most amount of IDs in other players ref value.

 

 

so basically i need a way so it will find everyones $refcount not just mine...

 

this is how i would get my $refcount

 

//REFFERAL COUNT
$query5 = $db->execute("select * from `users` where `ref`=?",array($user->id));
$refcount = $query5->recordcount();

[RussellReal]

SELECT COUNT(*) as num FROM members GROUP BY ref ORDER BY num LIMIT 20

[seany123]

but then i wanna basically use the top 20 ids found as put them in this kind of query:

 

$query2 = $db->execute("select * from `users` where `id`=?",array(ID FOUND));
$users = $query2->fetchrow();

[RussellReal]

why?... just select the information with the same query =\

[RussellReal]

well actually, nvm that won't exactly work, well it WOULD but it would be a waste of resources, I'd just take the list for example..

 

SELECT COUNT(*) as num, ref FROM members GROUP BY ref ORDER BY num DESC LIMIT 20

 

and then run queries on each of the ref ids like so:

 

<?php
while ($r = mysql_fetch_assoc($r)) {
  $referer = mysql_fetch_object(mysql_query("SELECT * FROM members WHERE id = '{$r['ref']}'"));
  // $referer->username  will be the user's username..
  
}
?>