Auto Numbering Records

brattt

To use a simple example: I have a column in my database table called 'Points'.

I am displaying these points in a repeat region, and am using an auto-numbering script to list their 'ranks' in a column next to them. This looks fine but doesn't really apply a rank as such ( and doesn't work when the list has a search function added to it).

Is their any way using php or sql to assign the highest 'point' a 'rank' of 1, second highest a rank of '2' etc.

Any guidance would be very helpful.

khendar

You'll probably have to write some php specifically to do this. Something like

$query = "select * from `table_name` order by `points` desc";
$result = mysql_query($query);
for ($i=1;$i<=mysql_num_rows($result);$i++)
{
$record = mysql_fetch_array($result);
$subquery = "update `table_name` set `rank`= $i where `points` = $record['record_id']";
$subresult = mysql_query($subquery);
}

So basically you are getting all the records in order from highest to lowest and assigning them a ranking based on their order in the result set. Wont work too well if you have multiple records with the same number of points though.

BobLennon

brattt wrote:
To use a simple example: I have a column in my database table called 'Points'.

I am displaying these points in a repeat region, and am using an auto-numbering script to list their 'ranks' in a column next to them. This looks fine but doesn't really apply a rank as such ( and doesn't work when the list has a search function added to it).

Is their any way using php or sql to assign the highest 'point' a 'rank' of 1, second highest a rank of '2' etc.

Any guidance would be very helpful.

Auto numbered columns must be unique.

khendar

Actually that code above wont work properly - You'll need to have some sort of unique identifier on the table so you can update each record individually.