PHP Movie site: creating average values for a Top 10 page

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
apoltix
Forum Newbie
Posts: 11
Joined: Fri Sep 30, 2005 3:27 pm
Location: Denmark

PHP Movie site: creating average values for a Top 10 page

Post by apoltix »

Hi everyone,

I've tried to figure this one out myself, but I couldn't, therefore I ask you. I'm making a Danish movie site for a customer, where you'll be able to review movies and discuss them in a forum, etc. I've made the entire site, but now all I need is the Top 10 / Bottom 10 pages.

A registered user can rate every movie (once) on a scale of 1 to 5 stars. Every single vote will be stored in a MySQL database with an unique ID, the ID of the user that made the vote, the movie ID that the user voted on, and of course the vote value itself. I just don't know how to figure out the average of these movies, and them put them in a mysql_query, and ORDER BY average DESC. The average should not be calculated every time a user enters a page, since that will require many resources of the server constantly, so I want it to be manually updated, when an administrator enters an "update top 10/bottom 10 values" page.

If you're lost, let me make it more simple:
The average of all votes of a single movie must be calculated - except this must be done with all movies, where there's a vote. When the average of that single movie, it most (that's the easiest way I can figure out) be stored in the database, and then the Top 10 movies must be printed with a query like this:
mysql_query("SELECT * FROM bio_top10 ORDER BY average DESC");

If you can find the easiest way to make this value update/calculation script, I'd be glad.

Do you follow me? Please don't hesitate replying this one ;)[/b]
ryanlwh
Forum Commoner
Posts: 84
Joined: Wed Sep 14, 2005 1:29 pm

Post by ryanlwh »

Code: Select all

SELECT *, AVG(vote_value) AS average FROM vote_table GROUP BY movie_id ORDER BY average DESC LIMIT 10;
User avatar
apoltix
Forum Newbie
Posts: 11
Joined: Fri Sep 30, 2005 3:27 pm
Location: Denmark

Post by apoltix »

Thanks. Sorry for the late reply. That worked. Super.
Post Reply