Jump to content

Need help with an SQL Update


sbourdon

Recommended Posts

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!
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.