Ordering Ratings in PHP and MySQL - Make Fair Ratings Just Like IMDB
If you have some sort of voting on your site for something (e.g. a rating out of 10), chances are you may want to have a list showing the top rated or the worst rated. Most people may just think there is an easy solution, just sort by the average rating. The reality of it is that method would not be fair at all, or accurate for that matter.
For example, we have a movie site and users vote for movies (a rating out of 10) and we want to have a list of the top movies and worst movies on the site. Say we have the top movie with 1000 votes averaging around 9.5/10, then we have a new movie added and 5 people give it a perfect score, making its average 10/10. Now if we sort by the average then we get this new movie with 5 votes (10/10) beating the top movie with 1000 votes (9.5/10) which is hardly fair.
This tutorial will show you a simple but very effective solution to ordering ratings accurately. It will be based off this tutorial where you make an AJAX rating system.
First, create a database if you haven't already done so.
Now copy the code below, save it, run it once, then delete it.
Note: Change your MySQL log in information and database name accordingly.
I have added in some movies and imaginary votes as an example, feel free to add/remove your own movies and edit the ratings/votes for them. If you are going to add movies just make sure the rating you set is less than or equal to the votes * 10.
Now for the script to show the top and worst movies we have, based on ratings.
Save this as top.php.
Note: Change your MySQL log in information and database name accordingly.
Code Breakdown:
<a href="top.php?order=top">Best</a> | <a href="top.php?order=bottom">Worst</a> - We will have a little link at the top to select between the list of the best and the worst.
if($_GET['order'] == 'bottom') { - This will check if the user wants to view a list of the worst movies. Otherwise it will just go with the default, the top movies.
$order = ''; - In MySQL, the order is ascending, so we do not need to write it.
$order = ' DESC'; - We have to type DESC to make it descending, so it will order by the top rated.
$result = mysql_query("SELECT * FROM `movies` ORDER BY (rating/votes) * LN(votes) ".$order); - This is the query we will make to retrieve and order the movies sorted by their rating and weighted by the number of votes they have. It uses the LN() function built-in to MySQL, LN() will be explained at the end of the tutorial.
if($data['votes'] <= 5) { - Even though it is weight by the votes, it is probably best to just exclude movies that have 5 votes or less.
continue; - The continue statement will skip all of the code below it (in the block of the loop) but it will continue with the loop.
There you have it, a fair way to sort your ratings out based on the votes cast. It should look something like this:
Finally, some information about the LN() function in MySQL. The LN() function returns the logarithm of a given number. In this case it is base 10, so it returns the exponent required on 10 to get the specified number. For example, LN(10) is 1, LN(100) is 2, and so on.
For example, we have a movie site and users vote for movies (a rating out of 10) and we want to have a list of the top movies and worst movies on the site. Say we have the top movie with 1000 votes averaging around 9.5/10, then we have a new movie added and 5 people give it a perfect score, making its average 10/10. Now if we sort by the average then we get this new movie with 5 votes (10/10) beating the top movie with 1000 votes (9.5/10) which is hardly fair.
This tutorial will show you a simple but very effective solution to ordering ratings accurately. It will be based off this tutorial where you make an AJAX rating system.
First, create a database if you haven't already done so.
Now copy the code below, save it, run it once, then delete it.
<?php
mysql_connect ('localhost', 'USERNAME', 'PASSWORD');
mysql_select_db('DATABASE');
// holds the movie information
mysql_query("CREATE TABLE IF NOT EXISTS `movies` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(50) NOT NULL,
`votes` int(11) NOT NULL,
`rating` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
");
// default info
mysql_query("INSERT INTO `movies` (`id`, `title`, `votes`, `rating`) VALUES
(1, 'The Dark Knight', 17, 165),
(2, 'The Godfather', 55, 449),
(3, 'Fight Club', 43, 394),
(4, 'Star Wars', 71, 630),
(5, 'The Matrix', 56, 499),
(6, 'The Lord of the Rings', 54, 503);");
?>
Note: Change your MySQL log in information and database name accordingly.
I have added in some movies and imaginary votes as an example, feel free to add/remove your own movies and edit the ratings/votes for them. If you are going to add movies just make sure the rating you set is less than or equal to the votes * 10.
Now for the script to show the top and worst movies we have, based on ratings.
Save this as top.php.
<a href="top.php?order=top">Best</a> | <a href="top.php?order=bottom">Worst</a>
<table cellspacing="2" cellpadding="5" border="1">
<tr>
<td>Rank</td><td>Title</td><td>Rating</td>
</tr>
<?php
mysql_connect ("localhost", "USERNAME", "PASSWORD");
mysql_select_db("DATABASE");
if($_GET['order'] == 'bottom') {
$order = '';
}else{
$order = ' DESC';
}
// to set a limit just add on LIMIT 0, 100 to the end of the query (this would limit it to 100)
$result = mysql_query("SELECT * FROM `movies` ORDER BY (rating/votes) * LN(votes) ".$order);
$i = 1;
while($data = mysql_fetch_array($result)) {
if($data['votes'] <= 5) {
continue;
}
echo '<tr>';
echo '<td>'.$i.'</td>';
echo '<td>'.$data['title'].'</td><td>'.round($data['rating']/$data['votes'], 1).' out of 10 ('.$data['votes'].' votes)</td>';
echo '</tr>';
$i++;
}
?>
</table>
Note: Change your MySQL log in information and database name accordingly.
Code Breakdown:
<a href="top.php?order=top">Best</a> | <a href="top.php?order=bottom">Worst</a> - We will have a little link at the top to select between the list of the best and the worst.
if($_GET['order'] == 'bottom') { - This will check if the user wants to view a list of the worst movies. Otherwise it will just go with the default, the top movies.
$order = ''; - In MySQL, the order is ascending, so we do not need to write it.
$order = ' DESC'; - We have to type DESC to make it descending, so it will order by the top rated.
$result = mysql_query("SELECT * FROM `movies` ORDER BY (rating/votes) * LN(votes) ".$order); - This is the query we will make to retrieve and order the movies sorted by their rating and weighted by the number of votes they have. It uses the LN() function built-in to MySQL, LN() will be explained at the end of the tutorial.
if($data['votes'] <= 5) { - Even though it is weight by the votes, it is probably best to just exclude movies that have 5 votes or less.
continue; - The continue statement will skip all of the code below it (in the block of the loop) but it will continue with the loop.
There you have it, a fair way to sort your ratings out based on the votes cast. It should look something like this:
Finally, some information about the LN() function in MySQL. The LN() function returns the logarithm of a given number. In this case it is base 10, so it returns the exponent required on 10 to get the specified number. For example, LN(10) is 1, LN(100) is 2, and so on.
Pages (1) 1
Tutorial Statistics
Date Added: 23-12-2008
Votes: 8
Rating: 7.8
Views: 1157
Author: scott
Main Category:
PHP
Sub-Category:
General Development
Rate:
Votes: 8
Rating: 7.8
Views: 1157
Author: scott
Main Category:
PHP
Sub-Category:
General Development
Rate:
