Page 1 of 1

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

Posted: Fri Sep 30, 2005 3:42 pm
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]

Posted: Fri Sep 30, 2005 6:18 pm
by ryanlwh

Code: Select all

SELECT *, AVG(vote_value) AS average FROM vote_table GROUP BY movie_id ORDER BY average DESC LIMIT 10;

Posted: Sat Oct 29, 2005 6:15 pm
by apoltix
Thanks. Sorry for the late reply. That worked. Super.