PHP : MySQL Use The ORDER BY Clause

To retrieve data from a MySQL database table in a specific order, you can use the ORDER BY clause in your SQL SELECT` queries. The ORDER BY clause allows you to sort the result set by one or more columns, either in ascending (default) or descending order. Below are examples using both mysqli and PDO to select data from a users table and order the results based on a specific column.

Using mysqli :

Here's an example of using mysqli to select rows from the users table and order the results by the name column in ascending order:

// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

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

// SQL query with ORDER BY clause
$sql = "SELECT * FROM users ORDER BY name ASC";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // Output data of each row
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
    }
} else {
    echo "0 results";
}

// Close connection
$conn->close();
 

If you want to order by the name column in descending order, you would use ORDER BY name DESC instead.

Using PDO :

Here's the same example using PDO:

// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

try {
    // Create connection
    $conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // SQL query with ORDER BY clause
    $sql = "SELECT * FROM users ORDER BY name ASC";
    $stmt = $conn->prepare($sql);
    $stmt->execute();

    // Set the resulting array to associative
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if (count($result) > 0) {
        foreach ($result as $row) {
            echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
        }
    } else {
        echo "0 results";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
} finally {
    // Close connection
    $conn = null;
}
 

Explanation:

• Both examples use an SQL SELECT * FROM users ORDER BY name ASC query to retrieve rows from the users table and order the results by the name column in ascending order.
• The ORDER BY clause can be used to sort the result set in ascending (ASC) or descending (DESC) order.
• In the examples, the result set is ordered by the name column. You can replace name with any other column name you want to use for ordering.

Important Notes:

• Adjust the column name and sorting order (ASC or DESC) in the ORDER BY clause as needed.
• The ORDER BY clause can include multiple columns for more complex sorting.
• Always verify that the connection is successful before executing queries.
• Close the connection after use to free up resources.

Running these scripts will select and output rows from the users table ordered by the name column in ascending order. Adjust the ORDER BY clause as needed for your specific requirements.