Need help with an SQL Update

[sbourdon]

Hello,

I'm using a sports game in my Forum and I've created an add-on which allows the user the reset the team records (number of wins, ties and losses for each team). This is very usefull when using it for the season's finals, specifically when it's a x of y series (for example, 4 of 7 for the NHL Stanley Cup finals).  In those cases, we're more interested in the wins/losses records for the current series than for the whole season (for example, Edmonton Oilers (0-1) vs Carolina Hurricanes (1-0))...

[url=http://img158.imageshack.us/my.php?image=clipboard012oq.jpg]http://img158.imageshack.us/my.php?image=clipboard012oq.jpg[/url]

This add-on keeps the actual wins/ties/losses rows intact, since those are required for the season's statistics.  What I've done is create 3 new ones, called wins_series, ties_series and losses_series.  I'm all done now, except for one remaining bug; when adding the scores, the script does a +1 in the according  wins_series, ties_series or losses_series based on the values that appear in the wins, ties or losses rows!  If you look at the attached screenshot for example, you'll notice that the Hurricanes have already won one game in their actual series against the Oilers.  Problem is that when I'll enter the scores for the second game, instead of going up to 2 (because they did win!), this value will go up to 6 (because the script will use the value in the 'wins' row)...

[url=http://img205.imageshack.us/my.php?image=clipboard045ui.jpg]http://img205.imageshack.us/my.php?image=clipboard045ui.jpg[/url]

After lots of tests, I've managed to identify the source of the problem as coming from the function build_leaderboard, part of which appears below; could someone please take a look at it and tell me what's missing in order to fix this bug?

[code]
    for($i=0;$i<count($game_list);$i++)
    {
        $homescore = $game_array[$game_list[$i]]['homescore'];
        $awayscore = $game_array[$game_list[$i]]['awayscore'];
        $homeid = $game_array[$game_list[$i]]['homeid'];
        $awayid = $game_array[$game_list[$i]]['awayid'];
        $winner = '';

        if ($homescore>$awayscore)
        {
            $team_stats[$homeid]['wins'] = (isset($team_stats[$homeid]['wins'])) ? $team_stats[$homeid]['wins'] + 1 : 1;
            $team_stats[$awayid]['losses'] = (isset($team_stats[$awayid]['losses'])) ? $team_stats[$awayid]['losses'] + 1 : 1;
            $leaderboard_data .= "Winner of game_id->$game_list[$i] is Home\n";
            $winner = 'home';
            $team_stats[$homeid]['wins_series'] = (isset($team_stats[$homeid]['wins_series'])) ? $team_stats[$homeid]['wins_series'] + 1 : 1;
            $team_stats[$awayid]['losses_series'] = (isset($team_stats[$awayid]['losses_series'])) ? $team_stats[$awayid]['losses_series'] + 1 : 1;           
        }
        elseif ($homescore<$awayscore)
        {
            $team_stats[$awayid]['wins'] = (isset($team_stats[$awayid]['wins'])) ? $team_stats[$awayid]['wins'] + 1 : 1;
            $team_stats[$homeid]['losses'] = (isset($team_stats[$homeid]['losses'])) ? $team_stats[$homeid]['losses'] + 1 : 1;
            $leaderboard_data .= "Winner of game_id->$game_list[$i] is Away\n";
            $winner = 'away';
            $team_stats[$awayid]['wins_series'] = (isset($team_stats[$awayid]['wins_series'])) ? $team_stats[$awayid]['wins_series'] + 1 : 1;
            $team_stats[$homeid]['losses_series'] = (isset($team_stats[$homeid]['losses_series'])) ? $team_stats[$homeid]['losses_series'] + 1 : 1;           
        }
        elseif ($homescore==$awayscore)
        {
            $team_stats[$homeid]['ties'] = (isset($team_stats[$homeid]['ties'])) ? $team_stats[$homeid]['ties'] + 1 : 1;
            $team_stats[$awayid]['ties'] = (isset($team_stats[$awayid]['ties'])) ? $team_stats[$awayid]['ties'] + 1 : 1;
            $leaderboard_data .= "Winner of game_id->$game_list[$i] is Tie\n";
            $winner = 'none';
            $team_stats[$homeid]['ties_series'] = (isset($team_stats[$homeid]['ties_series'])) ? $team_stats[$homeid]['ties_series'] + 1 : 1;
            $team_stats[$awayid]['ties_series'] = (isset($team_stats[$awayid]['ties_series'])) ? $team_stats[$awayid]['ties_series'] + 1 : 1;           
        }
        if (($fsports_vars['points_system'] == 1) && ($process_bets) && ($winner != '') && (@in_array($game_list[$i], $bet_list) || ($game_list[$i] == $bet_list)) )
        {
            process_bets($game_list[$i], $winner);
            $leaderboard_data .= "Bets were processed for game_id->$game_list[$i] with winner being $winner\n";
        }
    }

    if (!empty($team_stats))
    {
        while (list($key, $val) = each($team_stats))
        {
            $wins = (!empty($team_stats[$key]['wins'])) ? $team_stats[$key]['wins'] : 0;
            $losses = (!empty($team_stats[$key]['losses'])) ? $team_stats[$key]['losses'] : 0;
            $ties = (!empty($team_stats[$key]['ties'])) ? $team_stats[$key]['ties'] : 0;
            $wins_series = (!empty($team_stats[$key]['wins_series'])) ? $team_stats[$key]['wins_series'] : 0;
            $losses_series = (!empty($team_stats[$key]['losses_series'])) ? $team_stats[$key]['losses_series'] : 0;
            $ties_series = (!empty($team_stats[$key]['ties_series'])) ? $team_stats[$key]['ties_series'] : 0;           

            $sql = "UPDATE " . TEAM_TABLE . " SET wins=$wins, losses=$losses, ties=$ties, wins_series=$wins_series, losses_series=$losses_series, ties_series=$ties_series WHERE id='$key'";
            if(!$db->sql_query($sql))
            {
                message_die(GENERAL_ERROR,'Error updating team table','',__LINE__,__FILE__,$sql);
            }
        }
    }
[/code]

If more info or code is necessary, just ask!

Thank you very much!