A rank users order by points

[alexcsandru]

I want to make an insert from table 'rank' , with number (rank) from the cod blow, to fild users.rank ...

 

set @rank := 0;

select @rank:=@rank+1 'rank', users.username, users.points from users order by points desc;

+------+-------------+--------+
| rank | username    | points |
+------+-------------+--------+
|    1 | ropy        |     98 |
|    2 | alexcsandru |     17 |
|    3 | bobo        |     17 |
|    4 | ovidiu      |      5 |
+------+-------------+--------+

 

How to do??

 

P.S.: to insert rank in users.rank table... and with a function  reload_player_rank() of 30 in 30 minutes (to update new rank in table users.rank) .

 

 

A solution please,

Thank you!

[alexcsandru]

nobody knows...  :'(

offf  , what to do?

[alexcsandru]

To be more exactly ..

I have table users ... something link that

+----+-------------+----------+--------+--------------+--------+------+
| id | username    | password | banned | colonization | points | rank |
+----+-------------+----------+--------+--------------+--------+------+
|  1 | alexcsandru | ******   | N      |            1 |     17 |    0 |
|  2 | ovidiu      | ******   | N      |            1 |      5 |    0 |
|  3 | ropy        | ******   | N      |            1 |     98 |    0 |
|  4 | bobo        | ******   | N      |            1 |     17 |    0 |
+----+-------------+----------+--------+--------------+--------+------+

 

and i want to update rank table, where is 0 with rank users ordered by points...

 

I need to display that with $smarty template (users.username, users.points, users.rank) , and more function of the game!

 

I build a game browser based !

 

Have a solution ?

 

Sorry for my english!

[kickstart]

Hi

 

Think I found a solution for you.

 

CREATE temporary table seq ( seq int  NOT NULL AUTO_INCREMENT, id int, PRIMARY KEY (`seq`));
INSERT INTO seq ( id, seq )
    SELECT id, NULL FROM UsersTable ORDER BY points DESC;

UPDATE UsersTable INNER join seq ON UsersTable.id = seq.id SET UsersTable.rank = seq.seq;

 

Basically a temp table using an autonumber key, and inserting into that the id of the user ordered by the points. Then using that to do the update of the rank field in the users table.

 

All the best

 

Keith

[alexcsandru]

Thanks a lot for answer, is a very interesting idea! i will back with an answer if it worked !

 

Cheers,

[alexcsandru]

Works great, thank you very much. 

[kickstart]

Hi

 

Glad I could help.

 

All the best

 

Keith

[alexcsandru]

This is much faster? right?

SET @rank=0;
UPDATE UsersTable SET rank=@rank:=@rank+1 ORDER BY score DESC;

 

C'ya