CODEWITHSUNDEEP
phpmysqlsqldatabase-design
rs11
rs11

Reputation: 23

storing sum() results in database vs calculating during runtime

I'm new to sql & php and unsure about how to proceed in this situation:

I created a mysql database with two tables. One is just a list of users with their data, each having a unique id. The second one awards certain amounts of points to users, with relevant columns being the user id and the amount of awarded points. This table is supposed to get new entries regularly and there's no limit to how many times a single user can appear in it.

On my php page I now want to display a list of users sorted by their point total.

My first approach was creating a "points_total" column in the user table, intending to run some kind of query that would calculate and update the correct total for each user every time new entries are added to the other table. To retrieve the data I could then use a very simple query and even use sql's sort features.

However, while it's easy to update the total for a specific user with the sum where function, I don't see a way to do that for the whole user table. After all, plain sql doesn't offer the ability to iterate over each row of a table, or am I missing a different way? I could probably do the update by going over the table in php, but then again, I'm not sure if that is even a good approach in the first place, because in a way storing the point data twice (the total in one table and then the point breakdown with some additional information in a different table) seems redundant.

A different option would be forgoing the extra column, and instead calculating the sums everytime the php page is accessed, then doing the sorting stuff with php. However, I suppose this would be much slower than having the data ready in the database, which could be a problem if the tables have a lot of entries?

I'm a bit lost here so any advice would be appreciated.

Upvotes: 2

Views: 1193

Answers (1)

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

To get the total points awarded, you could use a query similar to this:

SELECT 
    `user_name`,
    `user_id`,
    SUM(`points`.`points_award`) as `points`,
    COUNT(`points`.`points_award`) as `numberOfAwards`
FROM `users`
JOIN `points`
    ON `users`.`user_id` = `points`.`user_id`
GROUP BY `users`.`user_id`
ORDER BY `users`.`user_name` // or whatever users column you want.

Upvotes: 3

Related Questions