PhP, MySql - Code Optimization

Question

I am not a great php encoder (I'm from C ++). I use php only for database input.

I have a database with the following:

UserId (an unique int) AsyncPointsAverage (float) AsyncPointsAverageRank (a position based on the value immediately above) AsyncPointsRecentAverage (float an average for the last 5 tests only) AsyncPointsRecentAverageRank (a position based on the value immediately above) 

This table contains about 1000-1500 records. Every morning and in the afternoon, 5 people take a test that affects their average average and last average. (This is updated elsewhere, but not shown here.) After that, it is calculated for these 5 people, then the rating of all 1000-1500 will be performed, so I wrote the code below. Optimal?

What excites me the most is what I do MySql UPDATE about 1000 times. It's great? Should I do it differently? (Also feel free to optimize any other code in the function. As I said, I'm from C ++ background, so I don’t know the nuances of php.)

 // Sorts by array entry 1 function ReRankCompareAverage($a, $b) { if($a[1] == $b[1]) return 0; else return ($a[1] > $b[1] ? 1 : -1); } // Sorts by array entry 2 function ReRankCompareAverageRecent($a, $b) { if($a[2] == $b[2]) return 0; else return ($a[2] > $b[2] ? 1 : -1); } function ReRank($db) { $i = 0, $j = 0; $usersARR = null; $stmt = $db->prepare("SELECT UserId, AsyncPointsAverage, AsyncPointsRecentAverage FROM studenttable"); $stmt->execute(); if($stmt && isset($stmt) && $stmt->rowCount() > 0) { $i = 0; while(($row = $stmt->fetch(PDO::FETCH_ASSOC))) { $usersARR[$i][0] = intval($row['UserId']); $usersARR[$i][1] = floatval($row['AsyncPointsAverage']); $usersARR[$i][2] = floatval($row['AsyncPointsRecentAverage']); $i++; } } $stmt->closeCursor(); // mysql_free_result equivalent // The first pass of $j == 3 does the ranking by Average, filling position $usersARR[][3] with that rank // The second pass of $j == 4 does the ranking by AverageRecent, filling position $usersARR[][4] with that rank for($j = 3, $j <= 4; $j++) { $iCompare = $j == 3 ? 1 : 2; usort($usersARR, $j == 3 ? "ReRankCompareAverage" : "ReRankCompareAverageLast"); $count = count($usersARR); if($count > 0) { // Start it off, with the person with the highest average is rank 1 $usersARR[$count - 1][$j] = 1; // Position $j is filled with the rank // Now loop starting from the second one down for($i = $count - 2, $rank = 1; $i >= 0; $i--) { // Only change the rank if the next one down is strictly lower than the one above, otherwise will share the same rank if($usersARR[$i][$iCompare] < $usersARR[$i+1][$iCompare]) $rank = $count - $i; // Otherwise keep the same rank, because they are equal $usersARR[$count - 1][$j] = $rank; } } } // Now $usersARR is filled with the correct rankings, and they are asscoiated with $UserId // Now we must put all of these rankings into the database $count = count($usersARR); for($i = 0; $i < $count; $i++) { $stmt = $db->prepare("UPDATE studenttable SET AsyncPointsAverageRank=:AsyncPointsAverageRank, AsyncPointsRecentAverageRank=:AsyncPointsRecentAverageRank " . "WHERE UserId=:UserId"); $stmt->execute(array(':AsyncPointsAverageRank' => $usersARR[$i][3], ':AsyncPointsRecentAverageRank' => $usersARR[$i][4], ':UserId' => $usersARR[$i][0])); } } 

Answer 1

How do you need to use a rating? Maybe you are not using Ranks? They can be easily calculated:

 SELECT COUNT(*) FROM studenttable WHERE AsyncPointsAverage > $currentUserVariableAsyncPoints 

To show TOP 10:

 SELECT * FROM studenttable ORDER BY AsyncPointsAverage DESC LIMIT 0,10 

and etc.

EDIT:

To show a full rating with a position number, you can either do it in PHP (you already have it - inside the loop where you extract the lines, just display the $i++ variable). Or you can try with pure SQL (I personally feel better):

 SET @rank=0; SELECT @rank := @rank +1 AS rank, UserId, AsyncPointsAverage FROM studenttable ORDER BY AsyncPointsAverage DESC 

Answer 2

To expand Mark's answer, you do not need to recount the rank each time you add a test result. It is certainly functional, but it is not optimal. The best way is to calculate the rank when displaying it. If you want students to have the same result and the same rank, you can always calculate the rank in PHP.

SQL:

 SELECT UserId, AsyncPointsAverage, AsyncPointsAverageRank FROM studenttable ORDER BY AsyncPointsAverage DESC 

PHP:

 $stmt = $db->prepare("SEE ABOVE..."); $stmt->execute(); if( $stmt && isset( $stmt ) && $stmt->rowCount() ) { $rank = 1; $last_grade = -1; while( ( $row = $stmt->fetch( PDO::FETCH_ASSOC ) ) ) { $usersARR[$i][0] = intval($row['UserId']); $usersARR[$i][1] = floatval($row['AsyncPointsAverage']); $usersARR[$i][2] = floatval($row['AsyncPointsRecentAverage']); if( $usersARR[$i][1] < $last_grade ) { $rank++; } $usersARR[$i][3] = $rank; $last_grade = $usersARR[$i][1]; } } 

You just need to change the reading fields and the ORDER BY field if you want to order the last average value.