To sort the results of a query using the ORDER BY clause in MySQLi, you simply add ORDER BY to your SQL query, followed by the column you want to sort by, and optionally, the sort direction (either ASC for ascending or DESC for descending).

Here are a few examples on how to use ORDER BY in MySQLi queries in PHP.

1. Simple Order By in PHP (Procedural Approach)

This example retrieves data from the users table and sorts it by the name column in ascending order.

<?php// Database connection parameters$host = "localhost";$username = "root";$password = "";$dbname = "testDB"; // Database where the table is located// Create connection$conn = mysqli_connect($host, $username, $password, $dbname);// Check connectionif (!$conn) {    die("Connection failed: " . mysqli_connect_error());}// SQL query to fetch data ordered by 'name' in ascending order$sql = "SELECT id, name, email FROM users ORDER BY name ASC"; // Change ASC to DESC for descending// Execute the query$result = mysqli_query($conn, $sql);// Check if there are rows to fetchif (mysqli_num_rows($result) > 0) {    // Fetch and display the records    while ($row = mysqli_fetch_assoc($result)) {        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";    }} else {    echo "No records found.";}// Close the connectionmysqli_close($conn);?>

2. Order By with Descending Order in PHP (Procedural Approach)

If you want to sort the data in descending order, you can use DESC.

<?php// Database connection parameters$host = "localhost";$username = "root";$password = "";$dbname = "testDB"; // Database where the table is located// Create connection$conn = mysqli_connect($host, $username, $password, $dbname);// Check connectionif (!$conn) {    die("Connection failed: " . mysqli_connect_error());}// SQL query to fetch data ordered by 'name' in descending order$sql = "SELECT id, name, email FROM users ORDER BY name DESC"; // DESC for descending order// Execute the query$result = mysqli_query($conn, $sql);// Check if there are rows to fetchif (mysqli_num_rows($result) > 0) {    // Fetch and display the records    while ($row = mysqli_fetch_assoc($result)) {        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";    }} else {    echo "No records found.";}// Close the connectionmysqli_close($conn);?>

3. Multiple Sorting Columns in PHP (Procedural Approach)

You can also sort by multiple columns. For example, if you want to sort first by name in ascending order and then by email in descending order:

<?php// Database connection parameters$host = "localhost";$username = "root";$password = "";$dbname = "testDB"; // Database where the table is located// Create connection$conn = mysqli_connect($host, $username, $password, $dbname);// Check connectionif (!$conn) {    die("Connection failed: " . mysqli_connect_error());}// SQL query to fetch data ordered by 'name' (ascending) and 'email' (descending)$sql = "SELECT id, name, email FROM users ORDER BY name ASC, email DESC"; // Multiple sorting// Execute the query$result = mysqli_query($conn, $sql);// Check if there are rows to fetchif (mysqli_num_rows($result) > 0) {    // Fetch and display the records    while ($row = mysqli_fetch_assoc($result)) {        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";    }} else {    echo "No records found.";}// Close the connectionmysqli_close($conn);?>

4. Order By in PHP with Prepared Statements (Object-Oriented Approach)

Using prepared statements with MySQLi in an object-oriented approach ensures better security (preventing SQL injection).

<?php// Database connection parameters$host = "localhost";$username = "root";$password = "";$dbname = "testDB"; // Database where the table is located// Create connection$conn = new mysqli($host, $username, $password, $dbname);// Check connectionif ($conn->connect_error) {    die("Connection failed: " . $conn->connect_error);}// SQL query to fetch data ordered by 'name' in ascending order$sql = "SELECT id, name, email FROM users ORDER BY name ASC"; // Change ASC to DESC for descending// Prepare and execute the statement$stmt = $conn->prepare($sql);$stmt->execute();// Get the result$result = $stmt->get_result();// Check if there are rows to fetchif ($result->num_rows > 0) {    // Fetch and display the records    while ($row = $result->fetch_assoc()) {        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";    }} else {    echo "No records found.";}// Close the statement and connection$stmt->close();$conn->close();?>

5. Order By with Pagination in PHP

When you want to limit the number of records per page, you can combine ORDER BY with LIMIT for pagination. Here's an example of paginated results with sorting:

<?php// Database connection parameters$host = "localhost";$username = "root";$password = "";$dbname = "testDB"; // Database where the table is located// Create connection$conn = mysqli_connect($host, $username, $password, $dbname);// Check connectionif (!$conn) {    die("Connection failed: " . mysqli_connect_error());}// Get the current page number (default is 1 if not set)$page = isset($_GET['page']) ? $_GET['page'] : 1;$records_per_page = 5; // Number of records per page$offset = ($page - 1) * $records_per_page; // Calculate the offset// SQL query to fetch data ordered by 'name' in ascending order with LIMIT and OFFSET$sql = "SELECT id, name, email FROM users ORDER BY name ASC LIMIT $records_per_page OFFSET $offset";// Execute the query$result = mysqli_query($conn, $sql);// Check if there are rows to fetchif (mysqli_num_rows($result) > 0) {    // Fetch and display the records    while ($row = mysqli_fetch_assoc($result)) {        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";    }} else {    echo "No records found.";}// Display pagination linksecho "<br><a href='?page=" . ($page - 1) . "'>Previous</a> | <a href='?page=" . ($page + 1) . "'>Next</a>";// Close the connectionmysqli_close($conn);?>

Explanation:

1. Basic Sorting: Use ORDER BY followed by the column name and ASC for ascending or DESC for descending order.

2. Multiple Sorting: You can sort by multiple columns by separating column names with commas.

3. Pagination: When fetching a limited number of records, you can combine LIMIT and OFFSET with ORDER BY for sorting the paginated data.

This should help you effectively use the ORDER BY clause with MySQLi in PHP. Let me know if you need further assistance!