如何使用PHP构建SQL查询以对数据库中的多行进行排序和选择

Question

I'm building a game using a platform called Unity. The game integrates with some php scripts and a database I have. Players can register in the game and all info gets stored in the database, including their high score.

I'm trying to write php code that will query the database and complete the following action

*Sort the table by high score

*Return the "username" and "high score" values for the top 25 entries

I don't know how to construct a query to do this. Below is a screen cap of my table and a sample php script of a query used to log in

database of users and high scores

//php below, script checking that there is only one instance of the username

$namecheckquery = "SELECT username, totalpoints, highscore FROM players WHERE username='" . $username . "'"; 

$namecheck = mysqli_query($con, $namecheckquery) or die("2: Name check query failed"); //error code #2 - name check query failed
if (mysqli_num_rows($namecheck) != 1)
{
    echo "5: Either no user with name or more than one"; //error code #5: number of names matching does not = 1 
    exit();
}

$existinginfo = mysqli_fetch_assoc($namecheck);

Answer 1

You need ORDER BY {column_name} {ASC|DESC} (ascending by default), and then LIMIT BY {offset}, {length}:

SELECT username, hightscore
FROM players
ORDER BY highscore DESC
LIMIT 0, 25

You can also add multiple columns to order by, so in case of equality the next column will be used, like:

SELECT username, hightscore
FROM players
ORDER BY highscore DESC, totalpoints DESC
LIMIT 0, 25