PHP基于值排序多维数组但获得意外结果

Question

so I've been scratching my head about this one for a while and almost there but still getting unexpected results.

So I have a database containing the following data

username |  level1    |  level2    |  level3
user1    |  39.76072  |  79.41869  |  151.2955
user2    |  39.80072  |  80.73846  |  270.6498
user3    |  39.84072  |  80.81845  |  83.41801
user4    |  39.85321  |  80.90525  |  88.31719
user5    |  Zero      |  Zero      |  Zero

I've been trying to put these into a multi-dimensional array in order from the lowest to the highest but have really been struggling. These are basically level times and I'm trying to create a leaderboard.

I assume the problem is that the level columns are not INT but rather CHAR

I am currently trying the following

function order_by_time($a, $b) {
    return $b["Level".$_POST["level"]] < $a["Level".$_POST["level"]] ? 1 : -1;
}


$query = mysqli_query($con, "SELECT userName, Level".$_POST["level"]." FROM table");
$results = array();
$unranked = array();
while($line = mysqli_fetch_array($query, MYSQLI_ASSOC)){
    if($line["Level".$_POST["level"]] == "Zero"){
        $unranked[] = $line;
    } else{
        $results[] = $line;
    }
}
usort($results, "order_by_time");
$results = array_merge($results, $unranked);

This works for level1 and for level2 but not for level3. Level1 displays as it does in the aboce database example and so does level2 but the problem is so does level3 and that isn't the correct order.

it looks like its something to do with the number of characters before the decimal.

Any advice on how i can resolve this without touching the database? that isn't really an option as its already being used.

I'd really appreciate some help with this :-)

Answer 1

First off, don't inject something from $_POST directly into an SQL query. That's how sites get hacked. In this particular case, I'd recommend setting a variable with that parameter cast to integer, and check its value as well:

$level = (int)$_POST["level"];
if ($level > 0 && $level < 4) {
    $query = $con->query("SELECT userName, Level$level FROM table");
} else {
    die();
}

As to your question, the simple solution here is to sort in the database. ORDER BY Level$level and you're done.

$level = (int)$_POST["level"];
if ($level > 0 && $level < 4) {
    $query = $con->query("SELECT userName, Level$level FROM table ORDER BY Level$level");
} else {
    die();
}
$results = $query->fetch_all(MYSQLI_ASSOC);

If that's not possible for some reason, what you've got there should be working just fine according to my testing. But it's rather hacky, especially pulling information from $_POST to a custom function. Instead just use PHP's built-in abilities to work with multidimensional arrays.

$level = (int)$_POST["level"];
if ($level > 0 && $level < 4) {
    $query = $con->query("SELECT userName, Level$level FROM table");
} else {
    die();
}
$results = $query->fetch_all(MYSQLI_ASSOC);
$usernames = array_column($results, "username");
$times = array_column($results, "Level$level");
array_multisort($times, SORT_ASC, $results);