On 22-Dec-2004 Michael J. Pawlowsky wrote:
> I’m trying to come up with a more efficient method to do this.
> I have a table where people enter some info into the table.
>
<snip>
> I would like to allow the users to be able to see where they stand
> rank
> wise with everyone else.
> Right now I basically do a SELECT count(1) as entries, user_id GROUP
> BY
> user_id ORDER BY entries DESC.
<snip>
> I was wondering if anyone could think of a better way to do this.
>
Add a rank column that gets recalculated after each entry is added?
php code:
function calc_user_ranks($limit=100) {
global $dbconn;
$dbconn->Execute('SET @x:=0');
$qry = "SELECT (@x:[EMAIL PROTECTED]) AS rank, user_id, count(1) AS
entries
FROM user
GROUP BY user_id ORDER BY entries DESC LIMIT $limit";
$dbconn->Execute($qry);
while($row = $r->FetchRow())
$rank[$row['user_id']] = $row['rank'];
$dbconn->Execute('UPDATE user SET rank=0');
foreach($rank as $id => $r) {
$qry = "UPDATE user SET rank=$r WHERE user_id=$id";
$dbconn->Execute($qry);
}
}
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
