[SOLVED] MySQL Query: order by 'whatever', change each into a integer then put in a var ?

[MasterACE14]

Morning Everyone,

 

I was wondering, I know you can run a MySQL query to select everything in a table, by using ORDER BY and then you can do it in ascending or descending order. Now once you have done that query, can you then assign a integer to each of the results, so if I ORDERED every person on my website by their 'intelligence' attribute from the database, and did it in descending order(Bigged to smallest), could I then assign each of them a Integer, so the whoever had the highest 'intelligence' out of everyone, they would be assigned 1, and the second highest would be assigned 2 etc. And then I could UPDATE every users 'rank' column in the database and put these integers into it.

 

If this is possible. How can I do it?

 

Regards ACE

[trq]

The question really is why would you need to make an extra field to store this data? You can dynamically get a users rank without the need for a new field. For example, to get the top 5.

 

SELECT user FROM tbl ORDER BY intelligence DESC LIMIT 5

 

to get the third heighest rank...

 

SELECT user FROM tbl ORDER BY intelligence DESC LIMIT 2,1

 

and the lowest rank...

 

SELECT user FROM tbl ORDER BY intelligence ASC LIMIT 1

 

You can do what you want, but you probably dont need to.

[MasterACE14]

ok, that makes sense, and then how would I modify it do display every single users rank? LIMIT $all? with $all being:

<?php
$all = mysql_num_rows( $result );

 

would that work? or is their a better way?

 

 

[pocobueno1388]

To get all of them, you could do something like this:

 

<?php

$query = "SELECT user FROM tbl ORDER BY intelligence DESC";
$result = mysql_query($query)or die(mysql_error());

$i = 1;

while ($row = mysql_fetch_assoc($result)){
   echo "Rank #$i - {$row['user']}<br>";
   $i++;
}

?>

[MasterACE14]

ok, thankyou everyone. Much Appreciated 

 

Regards ACE