Sit back, relax, and imagine this. You run a huge international movie database site. And, your database software is MySQL. You want visitors of your site to be able to rate movies, so that you can rank the movies. Here's how to get started...

First, let's work with some assumptions:
1) Your MySQL table that stores the movies is called 'table_movie.'
2) You uniquely identify each movie stored in 'table_movie' with an ID number, which you have labeled 'movieID.'
3) The rating system will be on a 10-point scale.

Modify the MySQL table
You will need to add to 2 more integer ('INT') columns to 'table_movie'; I would set the sizes to 255. Let's call them 'totalScore' and 'numberOfVotes.' Later on, you will calculate the rating by dividing totalScore/numberOfVotes. Get the logic?

rating.php
You can have visitors rate movies by either submitting a form (of method='POST') or by clicking links. For convenience, let's say you opted for choice 2. Here's an example link:

Reminder: Variables that are retrieved from the URL are stored in the $_GET array. With that in mind, let's start writing rating.php:


The above code first extracted the current values of 'totScore' and 'numberOfVotes' for the movie specified by the movie ID number. Those values were then updated; and the new rating was calculated and printed/echoed out. Lastly, 'table_movie' was updated with these new values.

The function used to calculate the rating was bcdiv. This function has 3 arguments: the dividend, the divisor, and the number of decimal places to round to (respectively). In this case, we round to the tenth place. Note that the '++$numberOfVotes' will increment the 'numberOfVotes' variable before it's used.

That was simple enough. Now, let's say you want to create a "Top 10 Movies" list, as determined by your visitors' ratings. Here's how..

top10.php

Let's break the code down. First, look at the WHERE clause. I specified 'numberOfVotes > 20' so that a movie needs to have been rated more than 20 times before appearing on your top 10 list. I then ordered the results by '(totalScore/numberOfVotes) DESC.' As you recall, totalScore/numberOfVotes calculates the rating. The 'DESC' means to sort the ratings from highest to lowest. (The alternative would be 'ASC,' or ascending, which will transform your list to be a "Top 10 Worst Movies" list.) Finally, "LIMIT 0,10" limits the query to return only the first 10 results. To get a better idea of how 'LIMIT' works, here are a few examples and explanations:
» LIMIT 1,10 — returns results 2-11
» LIMIT 10,10 — returns results 10-20
» LIMIT 100,1 — returns result 100

Finally, the while loop just iterates through and prints out the movie names. Of course, you can modify the query to return more data, so that you can also print out the number of votes and rating associated with each movie. But, I'll leave that for you.

» Bookmark this Tutorial
» Bookmark IceMelon