How to Sort Columns in MySQL Using PHP

In this tutorial, we will learn how to sort columns in MySQL using PHP. Sorting columns in MySQL means arranging database records in ascending or descending order based on one or more columns.

Sorting is a very important operation when working with databases because it helps display data in a meaningful and organized way. For example, you may need to sort data in real-world applications based on:

Students by marks
Employees by salary
Products by price
Users by name
Users by registration date

When working with PHP, we use the MySQL ORDER BY clause to sort records in the table. So, let’s understand what ORDER BY clause is and how to use it with PHP and MySQL using practical examples.

What is ORDER BY Clause in MySQL?

The ORDER BY clause in MySQL sorts the result set (records) of a SELECT query based on the values in one or more columns. It allows you to arrange the records in either ascending or descending order.

However, by default, MySQL sorts data in ascending order if you do not specify the order. PHP executes MySQL queries, and MySQL performs the sorting operation. After sorting, PHP retrieves the sorted result set from the MySQL database.

Basic Syntax of ORDER BY Clause in MySQL

The basic syntax to sort records using the MySQL ORDER BY clause is:

SELECT column_name1, column_name2
FROM table_name
ORDER BY column_name ASC | DESC;

In the above syntax:

SELECT → Specifies the column(s) to retrieve data from the database table. You can also use * to select all columns.
FROM → Specifies the name of the table from which you want to retrieve records.
ORDER BY → Specifies the column used to sort the result set.
ASC (Ascending) → Sorts the records in ascending order (lowest to highest). This is the default sorting order in MySQL.
DESC (Descending) → Sorts the records in descending order (highest to lowest).

[blocksy-content-block id=”12371″]

Important Note:

If you do not specify ASC or DESC, MySQL automatically sorts the records in ascending order.

Requirements Before Sorting Records from Database Table

Before sorting records from a MySQL table using PHP, make sure you meet the following requirements:

You must create a MySQL database.
You must establish a successful connection between PHP and the MySQL database.
The table must exist in the selected database.
The table should contain records that you want to retrieve.
The database user must have permission to access and query the table.

We hope you have met all these basic requirements before attempting to sort columns from a MySQL table using PHP.

Ways to Sort Columns in MySQL Using PHP

PHP provides three main ways to sort the columns (result set) from a MySQL database table:

MySQLi Procedural Method
MySQLi Object-Oriented Method
PDO (PHP Data Objects) Method

All these methods execute the same MySQL query with the ORDER BY clause, but the syntax and approach in PHP are different. Let us understand each method one by one.

Sorting Columns Using MySQLi and PHP

Inside the htdocs folder, create a new file with name sorting_columns.php and paste this code into it.

Example 1: Procedural Method

<?php
// Include the database connection file.
require_once "connect.php";

// Write the SQL query to select all records by name in ascending order from students table.
$sql = "SELECT * FROM students ORDER BY name";
// Execute the SQL query.
$result = mysqli_query($conn, $sql);

// Check if the query returned any rows.
if (mysqli_num_rows($result) > 0) {
 // Loop through each row of the result set
    while($row = mysqli_fetch_assoc($result)) {
    // Display the student's name and email.
       echo "Name: " . $row["name"] . "<br>";
       echo "Email: " . $row["email"] . "<br><br>";
    }
} else {
  // Display message if no records are found.
     echo "No records found.";
}
// Close the database connection.
mysqli_close($conn);
?>

Now save this file using Ctrl + S.
[blocksy-content-block id=”12121″]
Now open the browser and go to http://localhost/sorting_columns.php. If the records exist into the table, you will see the following output in your browser.

Name: Amit
Email: amit@gmail.com

Name: Deepak
Email: deepak@gmail.com

Name: John
Email: john@gmail.com

Explanation of PHP Code

The line require_once “connect.php”; loads the database connection file. This file connects PHP to the MySQL database.
Without this connection, PHP cannot access or retrieve data from the database.
Next, we create a SQL statement and store it in a variable named $sql.
The SQL SELECT statement retrieves only name and email columns from the students table.
The ORDER BY clause sorts the records in ascending order.
The mysqli_query() function executes the SQL query. This function takes two parameters:
- $conn → database connection
- $sql → SQL query
The result set is stored in a variable named $result.
After that, the mysqli_num_rows() function checks whether the table contains any records.
If the number of rows is greater than 0, the condition becomes true. Then, the program reads and displays the data.
If no records exist inside the table, it goes to the else block to display a message “No records found”.
The while loop iterates over multiple records inside the table.
Inside the loop, the mysqli_fetch_assoc() function retrieves each row and stores it as an associative array. The loop continues until it reads all records.
Finally, the mysqli_close($conn); statement closes the database connection.

Example 2: Object-Oriented Method

<?php
// Include the database connection file.
require_once "connect.php";

// Write the SQL query to select all records by name in descending order using DESC from students table.
$sql = "SELECT * FROM students ORDER BY name DESC";
$result = mysqli_query($conn, $sql);

// Execute the SQL query using the query() method.
$result = $conn->query($sql);

// Check if the query returned any rows.
if ($result->num_rows > 0) {
// Loop through each row of the result set.
while ($row = $result->fetch_assoc()) {
// Display the student's name and email.
echo "Name: " . $row["name"] . "<br>";
echo "Email: " . $row["email"] . "<br><br>";
}
} else {
// Display message if no matching records are found.
echo "No records found.";
}
// Close the database connection
$conn->close();
?>

Output:

Name: John
Email: john@gmail.com

Name: Deepak
Email: deepak@gmail.com

Name: Amit
Email: amit@gmail.com

Sort Columns Using PDO Method

Inside the htdocs folder, create a new file with name sort_data_pdo.php and paste this code into it.

Example 3: Sort Data with PDO Method

<?php
// Import database connection.
require_once "config.php";

try {
// Create SQL query with ORDER BY clause.
   $sql = "SELECT name, age FROM students ORDER BY age ASC";
// Prepare and execute the query.
   $stmt = $conn->prepare($sql);
   $stmt->execute();
// Fetch all records.
   $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Check if records exist.
   if ($rows) {
    // Loop through each row.
       foreach ($rows as $row) {
           echo "Name: " . $row["name"] . "<br>";
           echo "Age: " . $row["age"] . "<br><br>";
       }
    } else {
        echo "No records found.";
    }
} catch (PDOException $e) {
  // Display error message.
     echo "Error: " . $e->getMessage();
}
// Close connection.
$conn = null;
?>