Search code examples
phpmysqlrating

calculating a top 5 in php and mysql

Hey I've made a rating system in php and mysql and now I want to select a top 5, I've made something but if a user adds a rating (with the max) it's nr 1 ranking.

How do you guys do it in php and mysql ?

the table looks like this:

-- id

-- mid

-- uid

-- rating

The rating is a number from 1 to 5

Thanks in advance!

Solution

  • As @chaos points out, this is the idea:

    SELECT `mid`, SUM(`rating`) AS `total`
FROM `rating`
GROUP BY `mid`
ORDER BY `total` DESC
LIMIT 5

    However, to ensure that not articles with very few ratings get into the top-5 you can add a threshold, allowing only articles with more than X ratings will show up in the result, perhaps giving a more accurate picture of the top 5:

    SELECT `mid`, SUM(`rating`) AS `total`, COUNT(1) AS `nrRatings`
FROM `rating`
GROUP BY `mid`
HAVING nrRatings > 5 // This is the threshold. Only articles with more than
                     // 5 ratings will be considered
ORDER BY `total` DESC
LIMIT 5