Average rating value counting

Question

I am working on a website, which has 10 star rating system. All ratings are stored in tbl_rating and have attributes:

id
heading
description
rating (number of stars, 1 to 10)
shop_id (each rating belongs to a shop - my site is a catalog of shops)

My question is, how is it the best way to count average rating for a shop (basicaly I only need to sum all ratings with particular shop_id and divide them by number of those ratings). Every shop has also column avg_rating, where I can store the value.

Is there any way to tell MySql database to count this statistics every hour? Should I count it after every new submitted rating or even every time the shop attribute avg_rating is queried? Is there any way to tell the database to do this automatically or do I have to run these actions from PHP? I am using PHP, Yii framework and MySQL database.

Thanks for any help.

Answer 1

You need a cron job (not sure if Yii has functionality to make this easier).

PHP

Basically you need to write a PHP script that re-counts the votes, and puts them in a separate table, and you need that script to run every hour or, how often you want it.

The cron command would be something like:

php updateRatings.php

MySQL

You can use a command similar to the next one as a cron.

mysql -h HOST -u USER -pPASS -e "UPDATE ..."

For the actual SQL query - you should check this question: MySQL - UPDATE query based on SELECT Query

UPDATE:

You could use a query similar to this:

UPDATE
    tbl_averages ta
SET 
    rating =
    (SELECT 
        AVG(tr.rating)
    FROM 
        tbl_rating tr
    WHERE 
        ta.shop_id = tr.shop_id)

With the table tbl_averages containing the cols shop_id and rating.