Understanding the MySQL ORDER BY Clause in PHP

A SELECT query without ORDER BY returns rows in an order MySQL does not guarantee — it can change between runs, server versions, or after table maintenance. The ORDER BY clause makes that order explicit, sorting the result set by one or more columns. This page shows how to use it from PHP: the syntax, ascending vs descending, sorting by several columns, where NULL values land, and how to sort by a user-supplied column safely so you don't open a SQL injection hole.

If you are new to running queries from PHP, start with connecting to MySQL and selecting data first.

Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

• ASC sorts ascending (A→Z, 0→9, oldest→newest) and is the default — ORDER BY age and ORDER BY age ASC are identical.
• DESC sorts descending (Z→A, 9→0, newest→oldest).
• The direction applies per column, so you can mix them: ORDER BY country ASC, age DESC.

ORDER BY runs after WHERE filtering and GROUP BY, so it sorts only the rows that survived those steps. It is the last clause before LIMIT.

A basic example

This selects names and ages and sorts the oldest customers first:

<?php

$conn = new mysqli("localhost", "username", "password", "database");
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT name, age FROM customers ORDER BY age DESC";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "Name: " . $row["name"] . " - Age: " . $row["age"] . "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();
?>

We connect with the mysqli class, select name and age from customers, and use ORDER BY age DESC so the highest age comes first. Swap DESC for ASC (or drop it) to put the youngest first.

Sorting by multiple columns

When several rows share the same value in the first sort column, the next column breaks the tie. The order of columns in the clause is the order of precedence:

SELECT name, country, age
FROM customers
ORDER BY country ASC, age DESC;

This groups customers by country alphabetically, and within each country lists the oldest first. With the data below:

ORDER BY country ASC, age DESC returns Anna, Ben, Carla — Canada before Mexico, and Anna before Ben because 41 > 29.

How NULL values sort

In MySQL, NULL is treated as lower than any non-NULL value:

• With ASC, rows where the sort column is NULL appear first.
• With DESC, they appear last.

To force NULLs to the end regardless of direction, sort on whether the column is null first:

SELECT name, last_login
FROM users
ORDER BY last_login IS NULL, last_login DESC;

last_login IS NULL evaluates to 0 for real dates and 1 for nulls, so the non-null rows (0) come before the null ones (1).

Sorting by a column chosen at runtime (safely)

A common need is letting the user pick the sort column — for example ?sort=name. You cannot bind a column name or the ASC/DESC keyword with a prepared-statement placeholder; placeholders only work for values. Concatenating the raw request into the SQL would be a SQL injection vulnerability. Instead, validate the input against an allow-list:

<?php
// Map user input to known-good column names.
$allowedColumns = [
    "name" => "name",
    "age"  => "age",
    "date" => "created_at",
];

$sortKey   = $_GET["sort"] ?? "name";
$column    = $allowedColumns[$sortKey] ?? "name";          // fallback if unknown
$direction = (($_GET["dir"] ?? "asc") === "desc") ? "DESC" : "ASC";

// $column and $direction can now only be values we put in the code.
$sql = "SELECT name, age FROM customers ORDER BY $column $direction";
$result = $conn->query($sql);
?>

The user only ever supplies an array key; the actual column name and direction come from constants in your code, so untrusted input never reaches the query string. This is the one place where building SQL by concatenation is acceptable — because every possible value is one you authored.

Note: ORDER BY itself never takes user values, so it doesn't use bound parameters. Any WHERE values in the same query should still be bound with a prepared statement.

Common gotchas

• Sorting by a string column with numbers (e.g. an age stored as VARCHAR) sorts lexically: "10" comes before "9". Store numbers in a numeric column, or cast with ORDER BY CAST(age AS UNSIGNED).
• ORDER BY with LIMIT is how you get "top N" results: ORDER BY age DESC LIMIT 5 returns the five oldest. Without ORDER BY, the rows LIMIT keeps are unpredictable. See limiting data.
• Sorting a large result set can be slow if no index covers the sort column; an index on the ORDER BY column lets MySQL skip the sort step.

Conclusion

ORDER BY turns an undefined row order into a defined one. Remember the essentials: ASC is the default, columns are applied left to right for tie-breaking, NULL sorts low, and a user-chosen sort column must be validated against an allow-list rather than concatenated blindly. Combined with WHERE for filtering and LIMIT for paging, it gives you full control over which rows you return and in what order.

Practice