MySQL ORDER BY

Sorting data is one of those things we do all the time but rarely stop to think about. Whether you build an e-commerce website or a basic blog, you are probably sorting stuff behind the scenes—products by price, posts by date, or users by name. That is where the PHP MySQL ORDER BY clause shines. It lets you sort database records in just the order you need.

In this article, you will learn what ORDER BY is, how to use it with PHP and MySQL, and tweak it for more advanced tasks.

What Is ORDER BY in MySQL?

The ORDER BY clause in SQL is used to sort the rows in your result set. By default, it sorts in ascending order, but you can flip that to descending if you need to. It works perfectly with both numbers and text.

Here is the basic syntax:

SELECT column1, column2 
FROM table_name
ORDER BY column1 ASC;

In this syntax:

You usually run SQL queries using a MySQL database. You can integrate ORDER BY into these queries to sort the results before displaying them on your webpage.

Let's start by creating a sample database and table to work with:  

CREATE DATABASE your_database_name;
USE your_database_name;

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO products (name, price) VALUES
('Laptop', 899.99),
('Smartphone', 599.99),
('Tablet', 299.99),
('Headphones', 49.99);

In the following section, we will fetch the data by using PHP based on the sorting task.

Fetching and Sorting Data in PHP

The below code helps you to fetch this data and sort it by price in descending order:

$db_servername = "localhost";
$db_username = "root";
$db_password = "";
$db_dbname = "your_database_name";

// Create connection
$dbconne = new mysqli($db_servername, $db_username, $db_password, $db_dbname);

// Check connection
if ($dbconne->connect_error) {
    die("Unable to Connect: " . $dbconne->connect_error);
}

// SQL query with ORDER BY
$sql = "SELECT * FROM products ORDER BY price DESC";
$dbresult = $dbconne->query($sql);

if ($dbresult->num_rows > 0) {
    while ($recrow = $dbresult->fetch_assoc()) {
        echo "Product Name: " . $recrow['name'] . " - Price: $" . $recrow['price'] . "<br>";
    }
} else {
    echo "No results found.";
}

$dbconne->close();

As you see, the query retrieves all records from the products table, sorted by the price column in descending order.  

Sorting with Multiple Columns

If you need to sort data by using more than one column. For instance, you might want to sort by price and then by name (alphabetically) for products with the same price.

Here is how you can do it:

SELECT * FROM products
ORDER BY price DESC, name ASC;

The priority is determined by the order of the columns in the ORDER BY clause. In this case, price takes precedence, and name is used as a secondary sort.

If you decide to give your users the ability to how they want to sort the data. This can be achieved by dynamically building the ORDER BY clause based on user input.

$sort_column = isset($_GET['sort']) ? $_GET['sort'] : 'name'; // Default sort by name
$order = isset($_GET['order']) && $_GET['order'] === 'desc' ? 'DESC' : 'ASC';

$sql = "SELECT * FROM products ORDER BY $sort_column $order";
$dbresult = $conn->query($sql);

if ($dbresult->num_rows > 0) {
    while ($recrow = $dbresult->fetch_assoc()) {
        echo "Name: " . $recrow['name'] . " - Price: $" . $recrow['price'] . "<br>";
    }
} else {
    echo "No results found.";
}

The query selects all data from the products table and orders it by the column specified in $sort_column (e.g., name or price) and the direction specified in $order (ascending or descending).

If you have not heard about the $_GET superglobal variable, you can read this tutorial.

Anyway, let' move on to the following section to see how to do that by dates.

Sorting Dates

Sorting by dates is common in applications like blogs or e-commerce sites. With the created_at column we added earlier, you can easily sort products by their creation date.

Here is an example of the SQL query:

SELECT * FROM products
ORDER BY created_at DESC;

And the PHP implementation:

$sql = "SELECT * FROM products ORDER BY created_at DESC";
$dbresult= $dbconne->query($sql);

if ($dbresult->num_rows > 0) {
    while ($recrow= $dbresult->fetch_assoc()) {
        echo "Name: " . $recrow['name'] . " - Created At: " . $recrow['created_at'] . "<br>";
    }
} else {
    echo "No result found.";
}

This query selects all rows from the products table and orders them by the created_at column in descending order. The newest entries (most recent created_at values) will appear first.

Let's summarize it.

Wrapping Up

The MySQL ORDER BY in PHP is an incredibly useful tool for sorting data in MySQL. By combining it with PHP, you can create dynamic and user-friendly applications that serve sorted data exactly how your users need it. From sorting prices to ordering by dates, the possibilities are endless when you understand how to use it properly.

Now it is your turn. Try out the examples above, tweak the code to fit your project, and watch your database queries. Do not forget to test with different datasets to see how sorting transforms your application's behavior.

Thank you for reading. Happy Coding!