-
Hajipur, Bihar, 844101
MySQL Order By
When retrieving data from a database, displaying it in a meaningful order is essential. MySQL’s ORDER BY clause allows you to sort query results based on one or more columns. Using ORDER BY in PHP applications helps organize data in ascending or descending order, making it easier to read, analyze, and use in dashboards, reports, or listings.
This tutorial explains how ORDER BY works, how to use it with PHP MySQLi and PDO, and best practices for sorting data efficiently.
What is ORDER BY?
The ORDER BY clause is added at the end of a SELECT statement to sort results. By default, sorting is ascending, but you can explicitly set ascending (ASC) or descending (DESC) order.
Basic Syntax
SELECT column1, column2 FROM table_name ORDER BY column1 ASC;
Example Table: students
| id | name | age | |
|---|---|---|---|
| 1 | Neha | 16 | neha@example.com |
| 2 | Anika | 17 | anika@example.com |
| 3 | Riya | 16 | riya@example.com |
| 4 | Sanya | 18 | sanya@example.com |
SELECT * FROM students ORDER BY name ASC;
This query sorts all students alphabetically by their names.
Sorting in Descending Order
To sort from highest to lowest or Z to A, use DESC:
SELECT * FROM students ORDER BY age DESC;
This will display the oldest students first.
Sorting by Multiple Columns
You can sort by more than one column. MySQL will first sort by the first column, then resolve ties using the second column.
SELECT * FROM students ORDER BY age ASC, name DESC;
-
First, sort students by age ascending.
-
If multiple students have the same age, sort them by name in descending order.
Using ORDER BY with WHERE
ORDER BY can be combined with WHERE to sort only a subset of rows:
SELECT * FROM students WHERE age >= 16 ORDER BY name ASC;
Sorting with PHP MySQLi
Procedural Style
<?php
$conn = mysqli_connect("localhost", "root", "", "school_db");
if (!$conn) die("Connection failed: " . mysqli_connect_error());
$sql = "SELECT id, name, age, email FROM students ORDER BY age DESC, name ASC";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
echo "ID: {$row['id']} - Name: {$row['name']} - Age: {$row['age']} - Email: {$row['email']}<br>";
}
} else {
echo "No records found.";
}
mysqli_close($conn);
?>
Object-Oriented Style
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) die("Connection failed: " . $conn->connect_error);
$sql = "SELECT * FROM students WHERE age >= 16 ORDER BY name ASC";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo "ID: {$row['id']}, Name: {$row['name']}, Age: {$row['age']}, Email: {$row['email']}<br>";
}
} else {
echo "No records found.";
}
$conn->close();
?>
Using ORDER BY with PDO
<?php
try {
$conn = new PDO("mysql:host=localhost;dbname=school_db", "root", "");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->query("SELECT id, name, age, email FROM students ORDER BY age DESC, name ASC");
$students = $stmt->fetchAll(PDO::FETCH_ASSOC);
if ($students) {
foreach ($students as $student) {
echo "ID: {$student['id']}, Name: {$student['name']}, Age: {$student['age']}, Email: {$student['email']}<br>";
}
} else {
echo "No records found.";
}
} catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}
$conn = null;
?>
Explanation:
-
ORDER BY age DESC, name ASCsorts by age descending and then name ascending. -
fetchAll(PDO::FETCH_ASSOC)retrieves results as an associative array. -
You can easily change the sorting order dynamically based on user input.
Dynamic Sorting in PHP
You can allow users to sort table data dynamically:
$sortColumn = $_GET['sortColumn'] ?? 'name';
$sortOrder = $_GET['sortOrder'] ?? 'ASC';
$sql = "SELECT * FROM students ORDER BY $sortColumn $sortOrder";
Important: Always validate user input to prevent SQL injection when using dynamic column names.
Best Practices
-
Always specify ASC or DESC for clarity.
-
Combine
ORDER BYwith WHERE for filtering. -
Use multiple columns to handle tie-breakers.
-
Avoid sorting very large datasets without limits to improve performance.
-
Sanitize any dynamic column or order input from users.
Common Errors
| Error | Cause | Solution |
|---|---|---|
| Unknown column | Column name typo | Check table structure |
| Empty results | Conditions filter out all rows | Verify WHERE clause and sorting |
| Injection risk | Directly using user input in ORDER BY | Validate and sanitize input |
| Performance issues | Sorting large datasets | Use LIMIT, indexes, and optimized queries |
Real-World Example
In a school portal, you may want to display students in age descending order with names alphabetically sorted for those of the same age:
SELECT * FROM students ORDER BY age DESC, name ASC;
This ensures that older students appear first, while students of the same age are alphabetically listed.
Summary of the Tutorial
-
ORDER BYis used to sort results in ascending (ASC) or descending (DESC) order. -
Multiple columns can be used to resolve tie-breakers.
-
Combine with
WHEREto sort only filtered rows. -
Use PHP MySQLi or PDO to fetch and display ordered results.
-
Dynamic sorting is possible but requires validation to maintain security.
Proper use of ORDER BY improves usability, readability, and performance in applications like student lists, product catalogs, and reports, ensuring that your data is presented meaningfully.
Practice Questions
-
Write a SQL query to select all students ordered by age ascending.
-
Write a SQL query to select students ordered by name descending.
-
Write a PHP MySQLi procedural script to fetch students ordered by age descending and display their names and emails.
-
Write a PHP MySQLi object-oriented script to fetch students whose age is greater than 16 and order them by name ascending.
-
Write a PDO PHP script to retrieve students ordered first by age ascending and then by name descending.
-
Explain the difference between
ASCandDESCin theORDER BYclause. -
Write a SQL query to order students by multiple columns: age ascending, then email ascending.
-
Modify a PHP script to allow dynamic ordering based on a user-selected column and order type.
-
Write a SQL query to select the top 5 oldest students using
ORDER BYandLIMIT. -
Describe a real-world scenario, such as displaying products or students, where ordering results enhances user experience.
