Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Jul 2011
    Posts
    46
    Thanks
    12
    Thanked 0 Times in 0 Posts

    SUM of values stored in a function?

    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.

  • #2
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,530
    Thanks
    45
    Thanked 259 Times in 256 Posts
    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

  • #3
    New Coder
    Join Date
    Jul 2011
    Posts
    46
    Thanks
    12
    Thanked 0 Times in 0 Posts
    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?

  • #4
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,530
    Thanks
    45
    Thanked 259 Times in 256 Posts
    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.

  • #5
    New Coder
    Join Date
    Jul 2011
    Posts
    46
    Thanks
    12
    Thanked 0 Times in 0 Posts
    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

    teams
    team_id
    team_name

    fixtures
    fixture_id
    home_team_id
    away_team_id
    date

    results
    fixture_id
    home_team_goals
    away_team_goals

    team_selections
    user_team_id
    team_id

    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.

    PHP Code:
    $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 = (($ht_league_dif) * ($ht_goals_points $ht_result_points $ht_cs_points));
            
    $at_points = (($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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •