Working out top rated items?

When it comes to programming quandaries like this, I often go back to paper. Rip up a piece of paper and write some demo ratings on it for 5 products, including the database fields and their values. Physically do the activity of calculating the top 3 rated items, and carefully note the algorithm your hands and brain use to do this. Then try to replicate this algorithm in code.

This assumes you have a datestamp in the table with the ratings, without such a field I don’t think you can accomplish your task.

I would do this in a loop which was set to loop for the number of unique products in your online store. Create an array (with 10 spots) outside of the loop to store the results, and one more to store the name of the product (you could use a multidimensional array maybe). Make a query that returns the last 2 weeks worth of ratings for a single product. Calculate its rating. Load the external array and compare the new rating to see if the new rating is higher than anything in the array. If it is, put it into the array in the proper sorted position, and do the same for the name of the product. Once the loop is complete, you are left with two arrays: one of product names and one with the two-week average rating, where the rows correspond to each other.

This is just one approach to how your problem could be solved, likely not the most efficient, but it should work.

You basically need to calculate the average rating for each product, based on the last two weeks. Then sort those ratings by date and display the top ten.

Now translate that spreadsheet language into code.
Sorry if this doesn’t help at all

Both of your replys help, how ever i cant seem to think of a good efficient way to do it.

Then don’t make it efficient. Get it working inefficiently first. I won’t be providing any functional code.

If you’ve designed your database well, you can do this in a single SQL query without really even getting too complicated.

If you’ve designed your database poorly, you’re out of luck.