[SOLVED] Help with query...

[ameriblog]

I need help with a query used to compute sports ratings. Up until this week I had this DB structure:

 

TABLE games

gameID

game_date

team1 (fk)

team1_score

team2 (fk)

team2_score

 

TABLE teams

teamID

team_name

team_ww

 

I was able to compute the ratings by running a loop through every game and calculating the ratings on a game by game basis. Over the weekend a fellow PHP programmer urged me to correct my DB structure to database normalization. So my new table structure is:

 

TABLE games

gameID

game_date

 

TABLE games_info

infoID

gameID (fk)

teamID (fk)

team_score

 

TABLE teams

teamID

team_name

team_ww

 

I tried to correct my code to correctly calculate the ratings, as the previous version had done. Not only are they not computing correctly, it is also taking longer than 10 minutes and my connection times out and it stops.

 

I have the following code that is what I am using now:

 

$weighted_rs = $conn->Execute ( "SELECT ncaa_gm.gameID, ncaa_gm.game_date, ncaa_gm.game_year, game_fixed, infoID, ncaa_gm_info.gameID, ncaa_gm_info.teamID, ncaa_gm_info.team_score FROM ncaa_gm_info, ncaa_gm WHERE ncaa_gm_info.gameID = ncaa_gm.gameID AND game_year = " . $_GET['year'] . " AND game_fixed = 1 ORDER BY ncaa_gm_info.infoID ASC" ) or die ( $conn->ErrorMsg() );
        
while ( ! $weighted_rs->EOF ) {
    
    // TEAM 1 INFO
    $team_rs = $conn->Execute ( "SELECT teamID, team_gp, team_mpi, team_ww FROM ncaa_tm WHERE teamID = " . $weighted_rs->Fields("teamID") . "" ) or die ( $conn->ErrorMsg() );
    $team = $team_rs->Fields("teamID");
    $team_gp = $team_rs->Fields("team_gp");
    $team_ww = $team_rs->Fields("team_ww");
    
    $opp_rs = $conn->Execute ( "SELECT ncaa_tm.teamID, team_gp, team_mpi, team_ww, ncaa_gm_info.teamID, gameID, team_score FROM ncaa_gm_info, ncaa_tm WHERE gameID = " . $weighted_rs->Fields("gameID") . " AND ncaa_gm_info.teamID != " . $weighted_rs->Fields("teamID") . " AND ncaa_gm_info.teamID = ncaa_tm.teamID" ) or die ( $conn->ErrorMsg() );
    $opp_gp = $opp_rs->Fields("team_gp");
    $opp_mpi = $opp_rs->Fields("team_mpi");
    
    // IF TEAM WON
    if ( $weighted_rs->Fields("team_score") > $opp_rs->Fields("team_score") )  {
        
        $weighted_win = $team_ww + $opp_mpi + 1;
        
        $sql = "UPDATE ncaa_tm SET team_ww = $weighted_win WHERE teamID = $team";
        $add_weighted_rs = $conn->Execute($sql) or die ( $conn->ErrorMsg() );
    }
    
    // IF TEAM LOST
    if ( $weighted_rs->Fields("team_score") < $opp_rs->Fields("team_score") )  {

        $weighted_win = $team_ww + $opp_mpi - 1;
        
        $sql = "UPDATE ncaa_tm SET team_ww = $weighted_win WHERE teamID = $team";
        $add_weighted_rs = $conn->Execute($sql) or die ( $conn->ErrorMsg() );
    }
    
$weighted_rs->MoveNext(); } 

 

My previous structure was the code would loop through all games, then would calculate the record for team1 and update the db, then calculate the record for team2 and update the db. I'd like to set this up to operate similarly...

[Barand]

I'd ask the "fellow PHP programmer" to sort out the problem s/he's created for you. You originally had a structure which made it simple to find who played who when and who won. Now you don't, without a lot of extra effort to simulate what you had before.

[ameriblog]

I am just going back to my old structure. I was told that it was only luck that I managed to get things to work properly with the way I had originally had it setup and that if I changed it things would be easier, but that's not my experience so far!

[Barand]

What you had was a many to many relationship

 

One team plays many opponents, one opponent plays many teams.

 

The game table was the link table between team and opponent. It's just that team and opponent both happen to be teams.

 

So IMO it was already normalised. Your friend's advice was one normalisation too far.

[ameriblog]

What you had was a many to many relationship

 

One team plays many opponents, one opponent plays many teams.

 

The game table was the link table between team and opponent. It's just that team and opponent both happen to be teams.

 

So IMO it was already normalised. Your friend's advice was one normalisation too far.

 

Great, thanks for your help!