Assign places in the rating (MySQL, PHP)

Question

I have a MySQL database with the following columns:

id     company      rating_score     rating_place

I have companies and rating scores for each company. So, my db looks like:

id     company      rating_score     rating_place

75     Intel         356.23          

34     Sun           287.49

etc.

How can I assign the places (my rating_place column is empty) based on the score using php and mysql?

Thank you!

Answer 1

How about this:

update mytable set rating_place =(select count(*)+1 from mytable intb where intb.rating_score>mytable.rating_score)

----edit (after comment) aww sorry, you can't select from the same table that you're updating in mysql, so try it with a temp table:

create table mytemptable as 
    select @row := @row +1 as place, mytable.id 
        from mytable, (SELECT @row := 0) r
        order by rating_score desc;

and then just a similar update:

update mytable set rating_place = (
    select place 
        from mytemptable 
        where mytemptable.id=mytable.id
    )

after that you can drop that mytemptable.

although if you want to avoid a separate table and you can use php, you can try

$res=mysql_query("select id from mytable order by rating_score desc");
$ratings=array();
while ($r=mysql_fetch_assoc($res)) {
    $ratings[]=$r['id'];
}
foreach ($ratings as $key=>$val) {
    mysql_query("update mytable set rating_score=".($key+1)." where id=".$val);
}