bluemicrobyte
03-21-2007, 3:13 PM
Greetings, Warboards. I come to you in hopes of getting help to find the bugs in my code. The code I bring before you today updates a users number of rank points, based on the scores they submit in my flash arcade.
Edit: Until someone replies saying that they've started working on it, I'll update my code here as I make changes. After someone speaks up, I'll post changes as a new post.
There are two scripts: The first is a cron that runs overnight that manually calculates every single score, and is 100% accurate as far as I know (or at least I'm calling it that). The second is a "real time" php function that takes in a few paramaters and updates affected rank point values as users submit scores to the arcade. It is called on average every 5 minutes. This script is mostly accurate, but for some reason whenever I run the first script it always reports that there were a few mis-calculations with my "real time" function.
I would guess that there is a problem with some greater than or equal to statement somewhere that's causing an extra user to be added or excluded from a point update that's offsetting the values.
(So in other words, I need to find the bugs in the second script to make it get the same values as the first script)
Here is the first cron script:
<?
// This script will update the arcade ladder rankings.
include_once '/home/blue/public_html/forums/includes/functions_custom.php';
connect_db();
// functions_custom.php connets us to the database
$point_level = 100; // This is the number of slots per game to award points for. Anything below this slot on a high score list gets no points.
// update functions_custom.php when changing this!
$querycount = 0;
// Convert current ladder points to old ladder points....
$query = "UPDATE phpbb_users SET ina_ladder_points_old = ina_ladder_points";
$result = mysql_query($query) or die('error ' . mysql_error()); $querycount++;
debugmsg ('Transfer current points to old points column: '.$result);
// reset everyones ladder points since we're about to re-calculate them.
// commented out -- not needed
/*
$query = "UPDATE phpbb_users SET ina_ladder_points = 0";
$result = mysql_query($query) or die('error ' . mysql_error()); $querycount++;
debugmsg ('Set everyones points to zero: '.$result);
*/
// Now lets create an associative array with everyone in it....
$users_array = array();
$username_list = array();
$query = "SELECT username, user_id FROM phpbb_users";
$result = mysql_query($query) or die('error ' . mysql_error()); $querycount++;
while ($row = mysql_fetch_row($result)) {
$users_array[$row[0]] = 0;
$username_list[$row[1]] = $row[0];
}
debugmsg ('Created users array with all the users: $users_array');
// Set up a loop to go through every single game in the DB
$query_games = "SELECT game_name, reverse_list FROM phpbb_ina_games";
$games_data = mysql_query($query_games) or die('error ' . mysql_error()); $querycount++;
debugmsg('Selected all games in the DB: '.$games_data);
while ($game_info = mysql_fetch_assoc($games_data)) { // For each game in the database......
debugmsg('<hr>');
// prepare to Get the scores for this game...
if ($game_info['reverse_list'])
$list_type = 'ASC';
else
$list_type = 'DESC';
debugmsg ('Game name: <b>'.$game_info['game_name'].'</b> and order type: '.$list_type);
// Now lets actually select the scores
$query_scores = "SELECT * FROM phpbb_ina_scores WHERE game_name = '".$game_info['game_name']."' ORDER BY score $list_type, date DESC LIMIT $point_level";
$scores_data = mysql_query($query_scores) or die('error ' . mysql_error()); $querycount++;
debugmsg('Found scores for this game: '.mysql_num_rows($scores_data)." ($point_level max)<br>");
$points_bank = mysql_num_rows($scores_data); // the num of points to award to 1st place, decrement for each slot after.
// For each score for this game
while ($score_info = mysql_fetch_assoc($scores_data)) {
$i++;
// Add $points_bank to the users point info in phpbb_users, then decrement the bank
$users_array[$score_info['player']] += $points_bank;
debugmsg($i .') '.$score_info['player'].' -- Added '.$points_bank . ' (Users new total: '.$users_array[$score_info['player']].')');
$points_bank--;
}
}
debugmsg('<hr>');
// Now lets actually store these new values in the database
foreach ($username_list as $username) {
if ($users_array[$username] > 0) {
$query_addpoints = "UPDATE phpbb_users SET ina_ladder_points = '".$users_array[$username]."' WHERE username = '$username'";
$result = mysql_query($query_addpoints) or die('error ' . mysql_error()); $querycount++;
debugmsg("Updated database for $username, set points to ".$users_array[$username]);
}
}
// end of script
echo('Script completed with '.$querycount.' database queries');
// Now let's calculate the errors from the "real time" calculations with our careful processed ones.
$query = "SELECT username, ina_ladder_points, ina_ladder_points_old FROM phpbb_users WHERE ina_ladder_points > 0 ORDER BY ina_ladder_points DESC";
$result = mysql_query($query) or die ('Could not query user database for ina ladder info');
while ($row = mysql_fetch_assoc($result)) {
$error_amount += abs($row['ina_ladder_points'] - $row['ina_ladder_points_old']);
$error_msg .= $row['username'] ." had <b>".($row['ina_ladder_points'] - $row['ina_ladder_points_old'])."</b> added to adjust to where they should be<br>";
}
echo ('<hr>'.$error_msg.'<hr>');
echo ('Error amount is as follows: '.$error_amount.' offset. If greater than 0, bugs exist.');
?>And here is my "real time" function that gets called when a score is saved. If it looks messy, simply remove the "$output.=" statements and it will appear more tidy.
// To update the user rank scores in "real time"
function update_ranks($game_name, $username, $old_score, $new_score, $reverse) {
connect_db();
// Set the point level, defined wherever ranks are affected.
$point_level = 100;
$output .= 'Updating rank information, user submitted a score. <hr>';
$output .= 'Input paramaters are as follows: GAME:'.$game_name.', USER:'.$username.', OLD_S:'.$old_score.', NEW_S:'.$new_score.', REV:'.$reverse;
// Check to see if the score has even changed....
if (($old_score < $new_score && $reverse == 0) || ($old_score > $new_score && $reverse == 1)) {
// First let's set up the sort order info since we need to write everything for both cases
if ($reverse == 0) {
$sort_order = "DESC";
} else {
$sort_order = "ASC";
}
// Find the score data for this game, (users_within_threshold, and min value)
$query = "SELECT * FROM phpbb_ina_scores WHERE game_name = '$game_name' ORDER BY score $sort_order, date DESC LIMIT $point_level";
$result = mysql_query($query); $querycount++;
mysql_data_seek($result, mysql_num_rows($result)-1);
$row = mysql_fetch_assoc($result);
//
// Now lets assign variables
$lowest_score = $row['score'];
$number_of_scores = mysql_num_rows($result);
$user_had_score = ($old_score > 0) ? true : false ;
$threshold_top = $new_score;
if ($reverse == 0) {
$threshold_bottom = ($old_score > $lowest_score) ? $old_score : $lowest_score ;
$new_score_within_top = ($new_score >= $lowest_score) ? true : false ;
} else {
$threshold_bottom = ($old_score < $lowest_score) ? $old_score : $lowest_score ;
$new_score_within_top = ($new_score <= $lowest_score) ? true : false ;
}
$output.= "<hr>Variables have been set up as follows:<br>Lowest score (pos 100): $lowest_score <br> Number of scores: $number_of_scores <br> User had score: $user_had_score <br> Threshold top: $threshold_top <br> Threshold bottom: $threshold_bottom <br> New score within top 100? $new_score_within_top <hr>";
//================================================== ==
// Now we update the ranking points
//
if ($reverse == 0) $query = "SELECT * FROM phpbb_ina_scores WHERE game_name = '$game_name' AND score >= $threshold_bottom AND score < $threshold_top";
if ($reverse == 1) $query = "SELECT * FROM phpbb_ina_scores WHERE game_name = '$game_name' AND score <= $threshold_bottom AND score > $threshold_top";
$result = mysql_query($query); $querycount++;
$points_earned = mysql_num_rows($result);
$output.= "The number of scores the user passed within the top 100 threshold: $points_earned <hr>";
// Condition 1: There are at least 100 scores OR there aren't and this is and old score
if ($new_score_within_top && ($number_of_scores >= 100 || $user_had_score)) {
$output.="(true) <b>Condition 1:</b> There are at least 100 scores OR there aren't and this is and old score <br>";
// Step A: Everyone within the threshold loses a point
//
$output.="<b>Step A:</b> Everyone within the threshold loses a point...<br>";
while ($row = mysql_fetch_assoc($result)) {
$query2 = "UPDATE phpbb_users SET ina_ladder_points = ina_ladder_points-1 WHERE username = '".$row['player']."'";
mysql_query($query2); $querycount++;
$output.="The user ".$row['player']." has been deducted 1 point.<br>";
}
// Step B: Current user gets a point for the size of the threshold we just affected
//
$output.="<b>Step B:</b> Current user gets a point for the size of the threshold we just affected<br>";
$query2 = "UPDATE phpbb_users SET ina_ladder_points = ina_ladder_points+$points_earned WHERE username = '$username'";
mysql_query($query2); $querycount++;
$output.="User $username was given $points_earned points.<hr>";
}
// Condition 2: There are not yet 100 scores and the user didn't have the score before
if ($number_of_scores < 100 && !$user_had_score) {
$output.="(true) <b>Condition 2:</b> There are not yet 100 scores and the user didn't have the score before. <br>";
//Check the threshold, if it's less than 1 then we set it to 1 since this is a new score.
$points_earned = ($points_earned > 0) ? $points_earned+1 : 1 ;
// Step B: Current user gets a point for the size of the threshold we just affected
//
$output.="<b>Step B:</b> Current user gets a point for the size of the threshold we just affected<br>";
$query2 = "UPDATE phpbb_users SET ina_ladder_points = ina_ladder_points+$points_earned WHERE username = '$username'";
mysql_query($query2); $querycount++;
$output.="User $username was given $points_earned points.<br>";
// Step C: Add 1 point to everyone above $threshold_top
$output.="<b>Step C:</b> Add 1 point to everyone above threshold_top<br>";
if ($reverse == 0) $query2 = "SELECT * FROM phpbb_ina_scores WHERE game_name = '$game_name' AND score > $threshold_top";
if ($reverse == 1) $query2 = "SELECT * FROM phpbb_ina_scores WHERE game_name = '$game_name' AND score < $threshold_top";
$result2 = mysql_query($query2); $querycount++;
while ($row = mysql_fetch_assoc($result2)) {
$query3 = "UPDATE phpbb_users SET ina_ladder_points = ina_ladder_points+1 WHERE username = '".$row['player']."'";
mysql_query($query3); $querycount++;
$output.="The user ".$row['player']." has been given 1 point.<br>";
}
$output.="<hr>";
}
} else {
// Else the score didn't even change
$output.="<hr>Script detected that the score hadn't actually changed, exiting....<hr>";
return 0;
}
$output.="<i>Script completed with $querycount database queries. User was given $points_earned points. </i>";
// And finally we finish up...
debug_mail($output, $username);
return $points_earned;
}If you would like to see the database or the run output of any of these scripts let me know. As you see, the function script emails me a debug log every time it runs, so I have plenty of them saved up. I can also generate the run output of the cron script if it would help.
If you need any more information to help solve this issue, just let me know.
Edit: Until someone replies saying that they've started working on it, I'll update my code here as I make changes. After someone speaks up, I'll post changes as a new post.
There are two scripts: The first is a cron that runs overnight that manually calculates every single score, and is 100% accurate as far as I know (or at least I'm calling it that). The second is a "real time" php function that takes in a few paramaters and updates affected rank point values as users submit scores to the arcade. It is called on average every 5 minutes. This script is mostly accurate, but for some reason whenever I run the first script it always reports that there were a few mis-calculations with my "real time" function.
I would guess that there is a problem with some greater than or equal to statement somewhere that's causing an extra user to be added or excluded from a point update that's offsetting the values.
(So in other words, I need to find the bugs in the second script to make it get the same values as the first script)
Here is the first cron script:
<?
// This script will update the arcade ladder rankings.
include_once '/home/blue/public_html/forums/includes/functions_custom.php';
connect_db();
// functions_custom.php connets us to the database
$point_level = 100; // This is the number of slots per game to award points for. Anything below this slot on a high score list gets no points.
// update functions_custom.php when changing this!
$querycount = 0;
// Convert current ladder points to old ladder points....
$query = "UPDATE phpbb_users SET ina_ladder_points_old = ina_ladder_points";
$result = mysql_query($query) or die('error ' . mysql_error()); $querycount++;
debugmsg ('Transfer current points to old points column: '.$result);
// reset everyones ladder points since we're about to re-calculate them.
// commented out -- not needed
/*
$query = "UPDATE phpbb_users SET ina_ladder_points = 0";
$result = mysql_query($query) or die('error ' . mysql_error()); $querycount++;
debugmsg ('Set everyones points to zero: '.$result);
*/
// Now lets create an associative array with everyone in it....
$users_array = array();
$username_list = array();
$query = "SELECT username, user_id FROM phpbb_users";
$result = mysql_query($query) or die('error ' . mysql_error()); $querycount++;
while ($row = mysql_fetch_row($result)) {
$users_array[$row[0]] = 0;
$username_list[$row[1]] = $row[0];
}
debugmsg ('Created users array with all the users: $users_array');
// Set up a loop to go through every single game in the DB
$query_games = "SELECT game_name, reverse_list FROM phpbb_ina_games";
$games_data = mysql_query($query_games) or die('error ' . mysql_error()); $querycount++;
debugmsg('Selected all games in the DB: '.$games_data);
while ($game_info = mysql_fetch_assoc($games_data)) { // For each game in the database......
debugmsg('<hr>');
// prepare to Get the scores for this game...
if ($game_info['reverse_list'])
$list_type = 'ASC';
else
$list_type = 'DESC';
debugmsg ('Game name: <b>'.$game_info['game_name'].'</b> and order type: '.$list_type);
// Now lets actually select the scores
$query_scores = "SELECT * FROM phpbb_ina_scores WHERE game_name = '".$game_info['game_name']."' ORDER BY score $list_type, date DESC LIMIT $point_level";
$scores_data = mysql_query($query_scores) or die('error ' . mysql_error()); $querycount++;
debugmsg('Found scores for this game: '.mysql_num_rows($scores_data)." ($point_level max)<br>");
$points_bank = mysql_num_rows($scores_data); // the num of points to award to 1st place, decrement for each slot after.
// For each score for this game
while ($score_info = mysql_fetch_assoc($scores_data)) {
$i++;
// Add $points_bank to the users point info in phpbb_users, then decrement the bank
$users_array[$score_info['player']] += $points_bank;
debugmsg($i .') '.$score_info['player'].' -- Added '.$points_bank . ' (Users new total: '.$users_array[$score_info['player']].')');
$points_bank--;
}
}
debugmsg('<hr>');
// Now lets actually store these new values in the database
foreach ($username_list as $username) {
if ($users_array[$username] > 0) {
$query_addpoints = "UPDATE phpbb_users SET ina_ladder_points = '".$users_array[$username]."' WHERE username = '$username'";
$result = mysql_query($query_addpoints) or die('error ' . mysql_error()); $querycount++;
debugmsg("Updated database for $username, set points to ".$users_array[$username]);
}
}
// end of script
echo('Script completed with '.$querycount.' database queries');
// Now let's calculate the errors from the "real time" calculations with our careful processed ones.
$query = "SELECT username, ina_ladder_points, ina_ladder_points_old FROM phpbb_users WHERE ina_ladder_points > 0 ORDER BY ina_ladder_points DESC";
$result = mysql_query($query) or die ('Could not query user database for ina ladder info');
while ($row = mysql_fetch_assoc($result)) {
$error_amount += abs($row['ina_ladder_points'] - $row['ina_ladder_points_old']);
$error_msg .= $row['username'] ." had <b>".($row['ina_ladder_points'] - $row['ina_ladder_points_old'])."</b> added to adjust to where they should be<br>";
}
echo ('<hr>'.$error_msg.'<hr>');
echo ('Error amount is as follows: '.$error_amount.' offset. If greater than 0, bugs exist.');
?>And here is my "real time" function that gets called when a score is saved. If it looks messy, simply remove the "$output.=" statements and it will appear more tidy.
// To update the user rank scores in "real time"
function update_ranks($game_name, $username, $old_score, $new_score, $reverse) {
connect_db();
// Set the point level, defined wherever ranks are affected.
$point_level = 100;
$output .= 'Updating rank information, user submitted a score. <hr>';
$output .= 'Input paramaters are as follows: GAME:'.$game_name.', USER:'.$username.', OLD_S:'.$old_score.', NEW_S:'.$new_score.', REV:'.$reverse;
// Check to see if the score has even changed....
if (($old_score < $new_score && $reverse == 0) || ($old_score > $new_score && $reverse == 1)) {
// First let's set up the sort order info since we need to write everything for both cases
if ($reverse == 0) {
$sort_order = "DESC";
} else {
$sort_order = "ASC";
}
// Find the score data for this game, (users_within_threshold, and min value)
$query = "SELECT * FROM phpbb_ina_scores WHERE game_name = '$game_name' ORDER BY score $sort_order, date DESC LIMIT $point_level";
$result = mysql_query($query); $querycount++;
mysql_data_seek($result, mysql_num_rows($result)-1);
$row = mysql_fetch_assoc($result);
//
// Now lets assign variables
$lowest_score = $row['score'];
$number_of_scores = mysql_num_rows($result);
$user_had_score = ($old_score > 0) ? true : false ;
$threshold_top = $new_score;
if ($reverse == 0) {
$threshold_bottom = ($old_score > $lowest_score) ? $old_score : $lowest_score ;
$new_score_within_top = ($new_score >= $lowest_score) ? true : false ;
} else {
$threshold_bottom = ($old_score < $lowest_score) ? $old_score : $lowest_score ;
$new_score_within_top = ($new_score <= $lowest_score) ? true : false ;
}
$output.= "<hr>Variables have been set up as follows:<br>Lowest score (pos 100): $lowest_score <br> Number of scores: $number_of_scores <br> User had score: $user_had_score <br> Threshold top: $threshold_top <br> Threshold bottom: $threshold_bottom <br> New score within top 100? $new_score_within_top <hr>";
//================================================== ==
// Now we update the ranking points
//
if ($reverse == 0) $query = "SELECT * FROM phpbb_ina_scores WHERE game_name = '$game_name' AND score >= $threshold_bottom AND score < $threshold_top";
if ($reverse == 1) $query = "SELECT * FROM phpbb_ina_scores WHERE game_name = '$game_name' AND score <= $threshold_bottom AND score > $threshold_top";
$result = mysql_query($query); $querycount++;
$points_earned = mysql_num_rows($result);
$output.= "The number of scores the user passed within the top 100 threshold: $points_earned <hr>";
// Condition 1: There are at least 100 scores OR there aren't and this is and old score
if ($new_score_within_top && ($number_of_scores >= 100 || $user_had_score)) {
$output.="(true) <b>Condition 1:</b> There are at least 100 scores OR there aren't and this is and old score <br>";
// Step A: Everyone within the threshold loses a point
//
$output.="<b>Step A:</b> Everyone within the threshold loses a point...<br>";
while ($row = mysql_fetch_assoc($result)) {
$query2 = "UPDATE phpbb_users SET ina_ladder_points = ina_ladder_points-1 WHERE username = '".$row['player']."'";
mysql_query($query2); $querycount++;
$output.="The user ".$row['player']." has been deducted 1 point.<br>";
}
// Step B: Current user gets a point for the size of the threshold we just affected
//
$output.="<b>Step B:</b> Current user gets a point for the size of the threshold we just affected<br>";
$query2 = "UPDATE phpbb_users SET ina_ladder_points = ina_ladder_points+$points_earned WHERE username = '$username'";
mysql_query($query2); $querycount++;
$output.="User $username was given $points_earned points.<hr>";
}
// Condition 2: There are not yet 100 scores and the user didn't have the score before
if ($number_of_scores < 100 && !$user_had_score) {
$output.="(true) <b>Condition 2:</b> There are not yet 100 scores and the user didn't have the score before. <br>";
//Check the threshold, if it's less than 1 then we set it to 1 since this is a new score.
$points_earned = ($points_earned > 0) ? $points_earned+1 : 1 ;
// Step B: Current user gets a point for the size of the threshold we just affected
//
$output.="<b>Step B:</b> Current user gets a point for the size of the threshold we just affected<br>";
$query2 = "UPDATE phpbb_users SET ina_ladder_points = ina_ladder_points+$points_earned WHERE username = '$username'";
mysql_query($query2); $querycount++;
$output.="User $username was given $points_earned points.<br>";
// Step C: Add 1 point to everyone above $threshold_top
$output.="<b>Step C:</b> Add 1 point to everyone above threshold_top<br>";
if ($reverse == 0) $query2 = "SELECT * FROM phpbb_ina_scores WHERE game_name = '$game_name' AND score > $threshold_top";
if ($reverse == 1) $query2 = "SELECT * FROM phpbb_ina_scores WHERE game_name = '$game_name' AND score < $threshold_top";
$result2 = mysql_query($query2); $querycount++;
while ($row = mysql_fetch_assoc($result2)) {
$query3 = "UPDATE phpbb_users SET ina_ladder_points = ina_ladder_points+1 WHERE username = '".$row['player']."'";
mysql_query($query3); $querycount++;
$output.="The user ".$row['player']." has been given 1 point.<br>";
}
$output.="<hr>";
}
} else {
// Else the score didn't even change
$output.="<hr>Script detected that the score hadn't actually changed, exiting....<hr>";
return 0;
}
$output.="<i>Script completed with $querycount database queries. User was given $points_earned points. </i>";
// And finally we finish up...
debug_mail($output, $username);
return $points_earned;
}If you would like to see the database or the run output of any of these scripts let me know. As you see, the function script emails me a debug log every time it runs, so I have plenty of them saved up. I can also generate the run output of the cron script if it would help.
If you need any more information to help solve this issue, just let me know.