mysql - update row based on order in resultant table -

mysql - update row based on order in resultant table -

i'm writing online math testing program, , working on scripts calculate rank each user got. next code works, cringe every time see it.

get_set() puts result of query $users

function rank_users_in_test($tid){ $globals['db']->get_set($users,"select user,test user_results test=$tid order points desc,time"); // $users in order rank order $rank = 1; foreach ($users $u){ $globals['db']->query("update user_results set world_rank=$rank user={$u['user']} , test={$u['test']}"); $rank++; } }

the query in loop makes me cry bit. question is, there way mysql can automatically update each user's rank based on order appeared in result on first query? there related question here, not utilize update.

i'm using mysql 5.

thanks ring0 above, next reduced running time minutes mere seconds :d

create table temp ( rank int auto_increment, user int, test int, primary key(rank) ); insert temp(user,test) (select user,test user_results test=$tid order points desc,time); update user_results ur, temp t set ur.world_rank=t.rank ur.user=t.user , ur.test=t.test; drop table temp;

mysql sql