PHP MySQL ORDER BY Clause

This guide describes how to select data from a MySQL database table and sort the data by a specific column in ascending or descending order with ORDER BY clause using PHP in XAMPP stack.

Prerequisites

For the purpose of this guide, I have created a table called "sales" in a MySQL database called "my_company" with following records.

Heads Up: In XAMPP, the database and table names are case insensitive. It will consider upper/lower case as lowercase only.

If you haven't created a database and table, refer the following guide.

• Create MySQL Database And Table Using PHP In XAMPP

What is ORDER BY Clause?

The ORDER BY clause is used to sort the MySQL table data in an ascending or descending order. The ORDER BY Clause can be used along with the SELECT statement to sort the data of specific columns in an ordered way.

Query Syntax:

SELECT column1,column2,.,column n from table_name ORDER BY column_name asc/desc;

Where, the column_name is the name of the column in which the result is ordered based on the values of this column, asc is used to order the data in ascending order, and desc is used to order the data in descending order.

By default, the data is sorted in ascending order when no argument is given.

Steps to Write a PHP Code

1. Specify servername, username, password and database name in your PHP code.

2. Create a connection using mysqli_connect() function.

Code:

$connection = mysqli_connect($server_name, $user_name, $password, $database_name);

3. Specify the SQL Query to select particular or all columns records in an order from the table. In this step, we can specify the SQL query to select columns from the table and save them into a variable.

Syntax:

$query = "SELECT column1,…. from Sales ORDER BY column_name";

4. Store the selected results into a variable called "final" using the mysqli_query() function.

Syntax:

mysqli_query($connection, $query);

5. Get the rows one by one from the "final" variable using mysqli_num_rows() function.

Code:

if (mysqli_num_rows($final) > 0) {
 //get the output of each row
  while($i = mysqli_fetch_assoc($final)) {
    echo $i["column1”],…………..;
  }
} else {
  echo "No results";
}

6. Close the connection.

Finally, we have to close the connection by using the mysqli_close() function.

Syntax:

mysqli_close($connection);

Now let us write the actual PHP code based on the above steps.

Select Data From MySQL Database Table with ORDER BY Clause using PHP

Example Code 1:

The following PHP code will select the "id" and "name" columns from the "sales" table and display the result in ascending order by "name" column.

Create a plain text file named select.php under /htdocs folder with the following contents in it.

Heads Up: If you use Linux, the htdocs folder will be under /opt/lampp/ directory. If you're on Windows, the htdocs will be usually in C:xampp folder.

<?php
//specify the server name
$server_name = "localhost";

//specify the username
$user_name = "root";

//specify the password. Here, we use empty password
$password = "";

//specify the database name
$database_name = "my_company";

// Creating the connection by specifying the connection details
$connection = mysqli_connect($server_name, $user_name, $password, $database_name);

//sql query to select particular columns
//select id and name columns
$query = "SELECT id,name from sales ORDER BY name";

#get the result
$final = mysqli_query($connection, $query);

if (mysqli_num_rows($final) > 0) {
 //get the output of each row
  while($i = mysqli_fetch_assoc($final)) {
      //get id and name columns
    echo "id: " . $i["id"]. "  ----> name: " . $i["name"]. "<br>";
  }
} else {
  echo "No results";
}

//close the connection
mysqli_close($connection);
?>

Open your web browser and navigate to http://localhost/select.php. You will see an output like this in your browser window.

Example Code 2:

In this example, we will select the "id" and "name" columns from the "sales" table and display the result in descending order by "name" column.

The code is exactly same as above. The only difference is we order the selected data in descending order. Simply find the following line in the above example code (i.e. select.php),

$query = "SELECT id,name from sales ORDER BY name";

Replace it with:

$query = "SELECT id,name from sales ORDER BY name desc";

That's it. Open your browser and point it to http://localhost/select.php URL.

You will see the data from "name" is ordered in descending order.

Conclusion

In this tutorial, we learned how to sort data from a MySQL database table in ascending or descending order with ORDER BY clause using PHP in XAMPP stack.