Iterations until stabilization

jbachris

I have a PHP code I wrote to rate high school football teams based on performance, what I'd like to do is have the PHP run several iterations until the ratings stabilize, but I'm not sure how I accomplish this:

<? 

require ( "conf.php" ); 

$games_rs = $conn->Execute ( "SELECT * FROM game ORDER BY date DESC" ) or die ( $conn->ErrorMsg() ); 

while ( ! $games_rs->EOF ) { 


$games_rs->MoveNext(); } 

?>

I have this code that runs every game in the DB through the loop. I don't have the inside the loop code as I'm on a library computer, but essentially the loop gets both team's information and then computes a rating for each team based on the following formula:

E = Opponent E + Won-Loss Pct. - .500

It does work and computes the ratings accurately and then stores them back in the database.

What I would like to do is tweak it so that it runs many iterations, as many as neccessary, until every team's rating stabilizes. Each iteration should use the previous iteration's results. I have an ancient Excel spreadsheet with VB (it's 6 years old) that does something similar, but I've made some tweaks to the actually formula, plus it is so much easier to customize the output of the ratings with PHP and MySQL.

Any help on how to accomplish this iteration would be awesome.

jbachris

Actually I think I figured this one out, just need to include a table for "old_rating" and then run all the ratings, compare "new_rating" to "old_rating" and if the margin is < 0.00005 I'd consider it stable and that'd end the calculating, if the margin > 0.00005 i'd just run the loop again.

So run the loop, with each row INSERT old_entry and new_entry into the team table. At the end of the loop look to make sure the difference between new_entry and old_entry for every row is 0.00005 or less, if it is move along to the next step, if not run the query again.

jbachris

Actually, I'm having some issues with how to structure the code to run iterations if the absolute value is greater than 0.00005.

I have this code that computes the ratings and then inserts it into the database:

$converge_rs = $conn->Execute ( "SELECT * FROM team ORDER BY teamID DESC" ) or die ( $conn->ErrorMsg() );

while ( ! $converge_rs->EOF ) {
	$team1_rs = $conn->Execute ( "SELECT * FROM team WHERE teamID = " . $converge_rs->Fields("teamID") . " LIMIT 1" ) or die ( $conn->ErrorMsg() );
	$team1 = $team1_rs->Fields("teamID");
	$team1_gp = $team1_rs->Fields("team_gp");	
	$team1_ww = $team1_rs->Fields("team_ww");	
	$team_old = $team1_rs->Fields("team_mpi");

$new_win = $team1_ww + 1;
$mpi = $new_win / $team1_gp;
$mpi_change = $mpi - $team_old;	

$sql = "UPDATE team SET team_mpi = $mpi, team_mpiold = $team_old, team_mpichange = $mpi_change WHERE teamID = $team1";
$add_rating_rs = $conn->Execute($sql) or die ( $conn->ErrorMsg() );

$converge_rs->MoveNext(); }

This works excellent, right after that I have a query that checks to see how many, if any, of the entries have a $team_mpichange has an absolute value of > 0.0005.

$check_rs = $conn->Execute ( "SELECT COUNT(teamID) as check, teamID, team_mpi, team_mpiold, team_ww, team_gp, team_mpichange FROM team WHERE abs(team_mpichange) > 0.00005 GROUP BY teamID ORDER BY teamID DESC" ) or die ( $conn->ErrorMsg() );

if ( $check_rs->Fields("check") > 0 )  {
// RUN RATINGS AGAIN
} else {
// MOVE ON TO NEXT STEP
}

The problem I'm running into is I'm not sure how to set it so that in the // RUN RATINGS AGAIN to check when it is done and if one of the entries still has an absolute value of greater than 0.00005 it runs it again, but if not it moves on...