← Back to PHP Course | Chapter 11: Database | Lesson 18 of 21

PHP MySQL Order By

Rows returned from a database come back in no particular guaranteed order unless you explicitly ask for one -- ORDER BY sorts the results of a SELECT query by one or more columns, ascending or descending, which is essential for anything from an alphabetized user list to a "newest first" activity feed.

In this page:

  1. Sorting Results Ascending
  2. Sorting Results Descending
  3. Sorting by Multiple Columns
  4. Combining ORDER BY with WHERE
  5. Sorting by an Expression or Alias
Syntax
php 
SELECT * FROM table_name ORDER BY column;             -- ascending
SELECT * FROM table_name ORDER BY column DESC;        -- descending
SELECT * FROM table_name ORDER BY column1, column2;

Sorting Results Ascending

ORDER BY column sorts a query's results by that column in ascending order by default -- smallest numbers first, earliest dates first, or alphabetical order for text -- exactly as you would expect a plain, unqualified sort to behave.

Note: ASC is the default and rarely needs to be written explicitly, though including it can make an ORDER BY clause's intent more obvious to future readers.
Warning: Sorting text columns is case-sensitive or case-insensitive depending on the column's collation setting -- results can look surprising if you assume the opposite of your table's actual configuration.

Example: Sorting Results Ascending

php 
<?php
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT)");
$db->exec("INSERT INTO users VALUES ('Carol'), ('Alice'), ('Bob')");
// Declare `$result`, set to `$db->query("SELECT * FROM users ORDER BY name")`
$result = $db->query("SELECT * FROM users ORDER BY name");
// Keep looping while `$row = $result->fetchArray(SQLITE3_ASSOC)` holds
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    // Print `$row['name'] . "\n"` to the output
    echo $row['name'] . "\n";
}
?>

Sorting Results Descending

ORDER BY column DESC reverses the sort direction -- largest values, latest dates, or reverse-alphabetical text first -- commonly used for showing the newest content first, like a blog's most recent posts or an activity feed's latest events.

Note: Add DESC explicitly whenever "newest first" or "highest first" is the intended order, since ascending is otherwise the silent default.
Warning: Forgetting DESC on a "most recent first" feed produces the exact opposite: the oldest items appear at the top instead of the newest.

Example: Sorting Results Descending

php 
<?php
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE posts (title TEXT, created TEXT)");
$db->exec("INSERT INTO posts VALUES ('Old', '2023-01-01'), ('New', '2024-01-01')");
// Declare `$result`, set to `$db->query("SELECT * FROM posts ORDER BY created DESC")`
$result = $db->query("SELECT * FROM posts ORDER BY created DESC");
// Keep looping while `$row = $result->fetchArray(SQLITE3_ASSOC)` holds
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    // Print `$row['title'] . "\n"` to the output
    echo $row['title'] . "\n";
}
?>

Sorting by Multiple Columns

ORDER BY col1, col2 sorts primarily by col1, and only uses col2 to decide the order among rows that have the exact same col1 value -- like sorting a class roster by last name, then by first name to break ties between students sharing the same last name.

Note: List sort columns in priority order, left to right -- the first column dominates, and each following column only matters as a tiebreaker for the ones before it.
Warning: Each column in a multi-column ORDER BY can have its own independent direction -- ORDER BY lastName ASC, age DESC is entirely valid and sorts each column differently.

Example: Sorting by Multiple Columns

php 
<?php
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE students (last_name TEXT, first_name TEXT)");
$db->exec("INSERT INTO students VALUES ('Smith','Bob'), ('Smith','Alice')");
// Declare `$result`, set to `$db->query("SELECT * FROM students ORDER BY last_name, first_name")`
$result = $db->query("SELECT * FROM students ORDER BY last_name, first_name");
// Keep looping while `$row = $result->fetchArray(SQLITE3_ASSOC)` holds
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    // Print `$row['first_name'] . " " . $row['last_name'] . "\n"` to the output
    echo $row['first_name'] . " " . $row['last_name'] . "\n";
}
?>

Combining ORDER BY with WHERE

ORDER BY works together with a WHERE clause, filtering the rows first and then sorting whatever remains -- WHERE always comes before ORDER BY in the SQL statement, and the sort only applies to the rows that already passed the filter.

Note: Write WHERE before ORDER BY in your SQL, matching how the database actually processes the statement: filter first, then sort what remains.
Warning: ORDER BY must come after WHERE in the SQL statement's syntax -- writing them in the wrong order produces a syntax error.

Example: Combining ORDER BY with WHERE

php 
<?php
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT, status TEXT)");
$db->exec("INSERT INTO users VALUES ('Bob','active'), ('Alice','active'), ('Carol','inactive')");
// Declare `$result`, set to `$db->query("SELECT * FROM users WHERE status = 'active' ORDER BY name")`
$result = $db->query("SELECT * FROM users WHERE status = 'active' ORDER BY name");
// Keep looping while `$row = $result->fetchArray(SQLITE3_ASSOC)` holds
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    // Print `$row['name'] . "\n"` to the output
    echo $row['name'] . "\n";
}
?>

Sorting by an Expression or Alias

ORDER BY can sort by more than just a plain column name -- it also accepts a computed expression (like a calculated total) or the alias given to a column in the SELECT clause, letting you sort by values that do not exist as their own stored column.

Note: When sorting by a calculated value, give it a clear alias in the SELECT clause, then reference that same alias in ORDER BY for cleaner, more readable SQL.
Warning: Referencing a column alias in ORDER BY works in MySQL, but is not universally portable to every database system -- worth double-checking if your code needs to support multiple database engines.

Example: Sorting by an Expression or Alias

php 
<?php
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE items (name TEXT, price INTEGER, qty INTEGER)");
$db->exec("INSERT INTO items VALUES ('A', 10, 2), ('B', 5, 10)");
// Declare `$result`, set to `$db->query("SELECT name, (price * qty) as total FROM items ORDER BY total DESC")`
$result = $db->query("SELECT name, (price * qty) as total FROM items ORDER BY total DESC");
// Keep looping while `$row = $result->fetchArray(SQLITE3_ASSOC)` holds
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    // Print `$row['name'] . ": " . $row['total'] . "\n"` to the output
    echo $row['name'] . ": " . $row['total'] . "\n";
}
?>
Related Topics

PHP MySQL Where · PHP MySQL Delete Data · PHP MySQL Select Data · PHP MySQL Update Data

{# common_mistakes/chapter_summary/browser_support: on Hindi pages the view already swaps in the hi_ translation fields (or blanks these out if untranslated), so this renders correctly for both languages without a lang_code check here. #}
Common Mistakes
  1. Assuming a table's natural row order (like insertion order) is reliable without an explicit ORDER BY -- MySQL does not guarantee any particular order without one.
  2. Sorting a large result set purely with PHP's sort functions after fetching it, when ORDER BY in the SQL query itself is far more efficient and can use table indexes.
  3. Forgetting ASC/DESC direction and getting the opposite sort order than intended -- ASC (ascending, the default) sorts smallest/earliest first, DESC sorts largest/latest first.
Chapter Summary
  • ORDER BY column sorts query results by that column, ascending by default.
  • ORDER BY column DESC sorts in descending order instead, largest or most recent values first.
  • ORDER BY col1, col2 sorts by col1 first, using col2 only to break ties where col1 values are equal.
Browser Support

ORDER BY is standard SQL and works identically across all MySQL and MariaDB versions PHP supports.

🔒

Chapter Quiz — Complete all 21 topics to unlock

0/21 topics done

Complete these topics first:

← Previous Lesson PHP MySQL Where Next Lesson → PHP MySQL Delete Data

Login to run this code

C/C++/Java/PHP execution requires a free account. Your code is saved — you'll land right back in the editor after logging in.