PHP MySQL Using the ORDER BY Clause

PHP MySQL Using the ORDER BY Clause

In PHP MySQL Using the ORDER BY Clause allows you to sort query results in ascending (ASC) or descending (DESC) order. You can use it with SELECT statements in PHP.

Using `mysqli` (Procedural)

<?php
$conn = mysqli_connect("localhost", "root", "", "test_db");

if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

// Select data and order by age ascending
$sql = "SELECT id, name, email, age FROM users ORDER BY age 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"]."<br>";
}
} else {
echo "0 results";
}

mysqli_close($conn);
?>

<?php

$conn = mysqli_connect("localhost", "root", "", "test_db");

if (!$conn) {

die("Connection failed: " . mysqli_connect_error());

}

// Select data and order by age ascending

$sql = "SELECT id, name, email, age FROM users ORDER BY age 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"]."<br>";

}

} else {

echo "0 results";

}

mysqli_close($conn);

ASC → ascending (default), DESC → descending

Using `mysqli` Object-Oriented

<?php
$conn = new mysqli("localhost", "root", "", "test_db");

if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT * FROM users ORDER BY name DESC";
$result = $conn->query($sql);

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

$conn->close();
?>

<?php

$conn = new mysqli("localhost", "root", "", "test_db");

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

$sql = "SELECT * FROM users ORDER BY name DESC";

$result = $conn->query($sql);

if ($result->num_rows > 0) {

while($row = $result->fetch_assoc()) {

echo "ID: ".$row["id"]." - Name: ".$row["name"]." - Age: ".$row["age"]."<br>";

}

} else {

echo "0 results";

}

$conn->close();

Sorts users by name in descending order.

Using PDO

<?php
try {
$conn = new PDO("mysql:host=localhost;dbname=test_db", "root", "");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $conn->prepare("SELECT * FROM users ORDER BY age DESC");
$stmt->execute();

$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($users as $user) {
echo "ID: ".$user['id']." - Name: ".$user['name']." - Age: ".$user['age']."<br>";
}

} catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}

$conn = null;
?>

<?php

try {

$conn = new PDO("mysql:host=localhost;dbname=test_db", "root", "");

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $conn->prepare("SELECT * FROM users ORDER BY age DESC");

$stmt->execute();

$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($users as $user) {

echo "ID: ".$user['id']." - Name: ".$user['name']." - Age: ".$user['age']."<br>";

}

} catch(PDOException $e) {

echo "Error: " . $e->getMessage();

}

$conn = null;

PDO supports the same ORDER BY syntax as MySQL.

Using Multiple Columns

You can order by more than one column:

First sorts by age ascending
Then sorts by name descending if ages are equal

Key Points

ORDER BY sorts the query results.
Use ASC for ascending (default) and DESC for descending order.
Multiple columns can be used for secondary sorting.
Works with mysqli procedural, object-oriented, and PDO.
Combine with WHERE to filter results before sorting.

PHP MySQL Using the ORDER BY Clause