PHP MySQL Using the ORDER BY Clause

📊 PHP MySQL Using the ORDER BY Clause

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

1️⃣ 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

2️⃣ 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.

3️⃣ 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.

4️⃣ 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

5️⃣ 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