PhP, MySql - Optimising Code

Question

I am not a great php coder (I come from C++). I am using php only for database entry.

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)

There are about 1000-1500 entries in that table. Every morning and afternoon 5 people take a test which effects their overall average and recent average. (This is updated elsewhere, but not shown here.) After that is calculated for those 5 people, then the rankings of all 1000-1500 will be effected, so I have written the code below. Is it optimal?

The thing I am most concerned with is I am doing a MySql UPDATE about a 1000 times. Is that great? Should I be doing it another way? (Also feel free to optimise any other code in the function. As I say, I am from a C++ background, so do not really 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 the ranking? Maybe you store Ranks unnecessary? They might be easily calculated:

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

To show TOP 10:

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

etc.

EDIT:

To show complete ranking with position number you can either do it in PHP (you have it already - inside loop where you fetch rows just display $i++ variable). Or you can try with pure SQL (personally I like it better):

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

Answer 2

Just to expand on Mark's answer, you do not need to recalculate the rank every time you add a test result. It is certainly functional but it is not optimal. The best way is to calculate the rank when you display it. If you want to allow for students having the same result and the same rank you could 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 fields read and the ORDER BY field if you want to order by recent average instead.