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);
}

Answer 2

Just sort by rating! This approach is just wrong as you would have to shift modify all data above a certain rank if you insert something. Bad data structure.

Well if you only insert something once or twice a year you could argue that integer sorting is faster, but well thats just a very minimal difference as sorting is based on Tree indexes and not on comparision.

So I have seen solutions like Andrew G. Johnson's. You could also tweak this further and only update entries with a higher score. You could also create a trigger that does it automatically for you.

But let me explain why this is wrong:

Its redundant data. Its not atomic and consistent. In a good atabase design you should always (if possible) store every information only at one point so it can be modified, deleted in an atomic way. So you can avoid any inconsistencies and complications in the first place. If you really wan't to "cache" the ranking, do it in your application.

So what are your alternatives to this if you really want to have database fields called like this?

Create a mysql view based on the sorted query. You can also do caching there AFAIK if thats your goal.

But the better option for caching would be just to let the mysql query cache do the work for you. That would be the very best option.

I see no reason what so ever to do what you are trying to do, only valid arguments against it.

Answer 3

SELECT @row := 0;
UPDATE
    `table`
SET
    `table`.`rating_place` = (@row := @row +1)
ORDER BY
    `table`.`rating_score` DESC;

PS: If you will be sending the queries from PHP, you will need to split the two, since PHP MySQL extension normally allows only single query per call.

yourQueryFunc('SELECT @row := 0;');
yourQueryFunc('
    SELECT @row := 0;
    UPDATE
        `table`
    SET
        `table`.`rating_place` = (@row := @row +1)
    ORDER BY
        `table`.`rating_score` DESC;');

Answer 4

If you are just trying sort by highest rating to lowest add this to the end of your SQL query:

ORDER BY rating_score DESC

Or lowest to highest:

ORDER BY rating_score ASC

If you still want to do this your way [which I'd advise against] try this:

UPDATE mytable SET rating_place=(SELECT COUNT(*)+1 FROM mytable tablecheck WHERE tablecheck.rating_score > mytable.rating_score)

Answer 5

I would do a select using the order by desc clause and then update each row with the rating.

Answer 6

While Andrew G. Johnson is correct, you may not need to even store this information in the database.

The answer I have for you is simple: "Why do you want to store this in the database?"

If you have actually have a good reason, then you have a few choices based on how static the data is. If the data is created then inserted all at once, then ORDER BY rating_score DESC at the end of your statement should do it (if rating_place is assigned automatically from 1).

Otherwise, I would do something in a dedicated PHP page that, once your 2 columns are read, assigns the rating_place. If you manually enter data into your database, it shouldn't hurt to have to open the page. If data collection is automated, go ahead and throw a call to the "update_places_page" that updates the rating.

Edit:

Another option is just to create a view for rating_score that takes the top 20 and orders reorders them, then select from the new view and the actual table based on rating_score.