View Full Version : SUM of values stored in a function?

07-17-2012, 05:02 PM
Hi, I am currently working on making my website more efficient and just trying to get some advice on the best way to do something.

It's a fantasy football website. The tables I have are teams, fixtures, results and teamselections. I used to have a formula in excel to calculate the points after each team had played. Then I would import into the results table in its own column - points.

I have since thought that it would be better to let php calculate the points scored for each game. So I have created a function that will succesfully do that. Great.

So the next part would be to calculate each users total points, i.e the total sum of all the teams points that they have selected. Before when I had a dedicated points column in the results table I would simply SELECT SUM(points). But now I don't have a points column and instead have a function so am not sure how to do this or if I should go back to the way I used to do it.

If anyone has any ideas I would be very much appreciated.

07-17-2012, 05:22 PM
I don't see why using SUM in MySQL should be an issue. Its true that you should let the server side code do calculations/processing, and let databases give you data. However, I would say that summing all a user's points would count as returning data, not processing, as its a valid datapoint you want to retrieve, plus, SUM is already built into MySQL.

You could just let PHP calculate the points and then add them to a column in MySQL, or if you have all the scores stored in the database, you can make a view and sum them all together there.

Don't hesitate to let the database do what it needs to do, just don't try to figure out the value of pi out to 10 places :)

07-17-2012, 05:52 PM
Thanks for your reply.

So are you saying that I should keep a points column in mysql and update it using php? rather than not physically storing it anywhere?

07-17-2012, 06:21 PM
As I'm not sure of your structure/exact purpose, I can't say 100%, but from what you've said so far, I don't see why you shouldn't. If you really don't wanna, you can do the view like I said, so its not actually stored, but calculated on demand, or create a simple stored procedure to do it.

07-17-2012, 08:45 PM
I like the idea of a procedure doing it for me, that way there is less work involved keeping it upto date. Do you know where there are any examples of similar code?

These are my main tables





I can calculate how many points each teams get based on the number of goals they score and how many goals there opponent scored. I created this to calculate the points for the home team and away team.

$ht_league_dif = $ht_league_id - $at_league_id;
if ($ht_league_dif <= 0) {
$ht_league_dif = 0; }

$at_league_dif = $at_league_id - $ht_league_id;
if ($at_league_dif <= 0) {
$at_league_dif = 0; }

if ($ht_goals > $at_goals) {
$ht_result_points = 30;
$at_result_points = 0; }
elseif ($ht_goals == $at_goals) {
$ht_result_points = 10;
$at_result_points = 10; }
elseif ($ht_goals < $at_goals) {
$ht_result_points = 0;
$at_result_points = 30; }

$ht_goals_points = $ht_goals * 5;
$at_goals_points = $at_goals * 5;

if ($ht_goals == 0) {
$at_cs_points = 5; }

if ($at_goals == 0) {
$ht_cs_points = 5; }

$ht_points = ((1 + $ht_league_dif) * ($ht_goals_points + $ht_result_points + $ht_cs_points));
$at_points = ((1 + $at_league_dif) * ($at_goals_points + $at_result_points + $at_cs_points));

So that works fine to calculate the points scored for one game at a time. I just need to be able to calculate the total points for each team and then the total points for each user based on which teams they have selected.