Author's Note: Now the stuff below is not the code you'd use if you were building the next Digg. It's just a simple demonstration of the concepts. But if you're looking to start building your own rating script, it's an excellent start to get you familiar with how the most elemental functions work.

There are lots of pre-packaged scripts out there to do almost anything you want, but in my experience a lot of them either do too much or don't do enough. Finding that Goldilocks style of "just right" takes a lot of searching and experimenting, or just plain hacking of existing scripts. But for simple functions, writing your own script that does exactly what you want can often be quicker and more satisfying.

Rating is one of these things. What do I mean by "rating"? I mean giving people a selection of items and letting them rate them on some sort of scale: 1 to 5 stars, 1 to 10 points, Hot/Warm/Cold, etc.

Rating is a lot easier than you might think. While you can make it very complex, at its most basic, a rating system consists of three things:

• A database table that lets you store a unique item ID and a score.
• A script that stores those values in the table.
• A script that shows the results.

If you want to get more complicated, you can add:

• A database table to store information about the items.
• Code in your storing script that makes sure the values being stored correspond to known items.
• Code in your script that shows the results to display the item information.

If you want to get really complex, you can add:

• Methods to prevent people voting for the same item more than once.
• Methods to weight votes to make certain more active/trusted users' votes count more.
• Ranking methods that use a formula based on the number of votes as well as the average to help an item with an average of 8.3 from 500 votes rank higher than an item with 5 votes, but an average of 9.

In this article, we'll leave the "more complicated" and "really complex" items aside and just concentrate on the basics.

So let's look at the three items in the simple approach.

Item 1: The Database Table

The table structure is simple...


CREATE TABLE `ratings` (
`uid` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`item_id` VARCHAR( 30 ) NOT NULL,
`rating` TINYINT( 4 ) NOT NULL
) ENGINE = MYISAM ;


That's it. Your database table is created.

"uid" is just a unique ID for the database to use to keep everything in order. It's set to an integer with an autoincrement value, meaning you don't ever need to worry about it after creating the table.

"item_id" is a value of up to 30 characters that you can give to an item you're letting people rank. For example, if you call something "Cat Photo 1", you can give it that item ID in the database.

"rating" is the numerical value of the rating the user gives the item.

Item 2: The Script That Stores The Rating

To feed the data to the script, you'll need a form. Let's look at a form that lets your users give something a rating on a scale of 1 to 5:


<form method="post" action="store-rating.php">
<input type="hidden" name="item_id" value="cat photo 1">
<input type="select" name="rating">
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
</select>
<input type="submit" value="Rate This">
</form>


Yup, that easy. Now we have to get the data from the form and put it into the database, using the store-rating.php script. First you'd put in the code that connects your script to the database. Then this is the code you'd use to store the rating.


$itemid = mysql_real_escape_string($_POST["item_id")];
$rating = intval($_POST["rating"]);
if(($rating < 1)||($rating>5)) die ("$rating is not a valid rating score");
$query = "INSERT INTO ratings(item_id,rating) VALUES ('$itemid',$rating)";
mysql_query($query);


Now, even though we're keeping this simple, we've put in a small bit of security. In line 1, we use mysql_real_escape_string to protect against someone trying to submit a MySQL command as an item ID (also known as a "SQL injection" attack). In line two, we make sure that the value put into $rating is a number. In line three, we make sure that the value of $rating is between 1 and 5 so someone doesn't try to sneak in a 0 or a 100 and skew the results.

Now the rating's stored. How do we put it to use?

Item 3: The Script That Shows The Results

This too is pretty simple. If you want to get the results for any item, you only need to do a simple query.

If you want to get all the ratings, you use this:


SELECT item_id,AVG(rating) FROM ratings GROUP BY item_id


That gives you a list of all the item IDs and their average scores. Let me break it down into more plain English:

• SELECT item_id,AVG(rating) - Remember we created a table with three fields: uid, item_id, and rating. Remember also that I said we could safely ignore uid after that. All we care about is item_id and rating. So we're telling it to select those two fields from the table. Additionally, we're telling it that the rating value should be averaged from a group of values.
• FROM ratings - This just tells it the name of the table it should use to get the values.
• GROUP BY item_id - This is what makes the averaging possible. It groups all the entries with the same item ID making it possible for the AVG() function to average all the associated rating values.

You could spool them out with the following...


$query = "SELECT item_id,AVG(rating) FROM ratings GROUP BY item_id";
$result = mysql_query($query);
while($values = mysql_fetch_row($result)){
      $itemid = $values[0];
      $rating = number_format($values[1], 2, '.', '');
      echo "The score for $itemid is $rating.<br>\n";
}


That's pretty easy. MySQL does the averaging for you, so you don't need to do any complicated extra code. All you do is use the PHP "number_format" function to cut it down to two decimal places (so 3.571867453 will be displayed as 3.57).

If you wanted to get just the rating for "cat photo 1", then it would be...


SELECT item_id,AVG(rating) FROM ratings WHERE item_id = "cat photo 1" GROUP BY item_ID


The results returned would just be the one item instead of all of them.

And That's It

As I note at the beginning of this post, you're not going to be ready to go build your own social bookmarking site now. This is an introductory tutorial. But hopefully the idea of building your own rating script looks a lot more possible than it did before you started.

This entry was posted on Wednesday, April 25th, 2007 at 9:47 pm and is filed under Techno Thoughts, Web Programming. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.