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.

 

<?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

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.

discuss this topic to forum