CODEWITHSUNDEEP
phpmysql
WebNovice
WebNovice

Reputation: 2220

Recalculate values in MySQL tables

I have a web application that stores points in a table, and total points in the user table as below:

User Table
user_id | total_points

Points Table
id | date | user_id | points

Every time a user earns a point, the following steps occur:

1. Enter points value to points table
2. Calculate SUM of the points for that user
3. Update the user table with the new SUM of points (total_points)

The values in the user table might get out of sync with the sum in the points table, and I want to be able to recalculate the SUM of all points for every user once in a while (eg. once a month). I could write a PHP script that could loop through each user in the user table and find the sum for that user and update the total_points, but that would be a lot of SQL queries.

Is there a better(efficient) way of doing what I am trying to do? Thanks...

Upvotes: 0

Views: 468

Answers (3)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79939

You have to user Triggers for this, to make the users total points in sync with the user_points table. Something like:

Create Trigger UpdateUserTotalPoints AFTER INSERT ON points
FOR EACH ROW Begin
    UPDATE users u
    INNER JOIN 
    (
       SELECT user_id, SUM(points) totalPoints
       FROM points
      GROUP BY user_id
    ) p ON u.user_id = p.user_id
   SET u.total_points = p.totalPoints;
END;

SQL Fiddle Demo

Note that: As noted by @FireLizzard, if these records in the second table, are frequently updated or delted, you have to have other AFTER UPDATE and AFTER DELETE triggers as well, to keep the two tables in sync. And in this case the solution that @FireLizzard will be better in this case.

Upvotes: 2

phaazon
phaazon

Reputation: 2002

If you want it once a month, you can’t deal with just MySQL. You have too « logic » code here, and put too logic in database is not the correct way to go. The trigger of Karan Punamiya could be nice, but it will update the user_table on every insert in points table, and it’s not what you seem to want.

For the fact you want to be able to remove points, just add bsarv new negated rows in points, don’t remove any row (it will break the history trace).

If you really want it periodically, you can run a cron script that does that, or even call your PHP script ;)

Upvotes: 0

Ethan Reesor
Ethan Reesor

Reputation: 2180

A more efficient way to do this would be the following:

User Table
user_id

Points Table
id | date | user_id | points

Total Points View
user_id | total_points

A view is effectively a select statement disguised as a table. The select statement would be: SELECT "user_id", SUM("points") AS "total_points" FROM "Points Table" GROUP BY "user_id". To create a view, execute CREATE VIEW "Total Points View" AS <SELECT STATEMENT> where SELECT STATEMENT is the previous select statement.

Once the view has been created, you can treat it as you would any regular table.

P.S.: I don't know that the quotes are necessary unless your table names actually contain spaces, but it's been a while since I worked with MySQL, so I don't remember it's idiosyncrasies.

Upvotes: 2

Related Questions