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