Home PHP MySQL PHP MySQL ORDER BY Clause

PHP MySQL ORDER BY Clause

Select Data From MySQL Database Table With ORDER BY Clause Using PHP

By Sravan Kumar
156 views

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.

idnamecount
5Shampoo10
2Milk20
3Books14
4Chocos45
6Eggs12
Records From sales table
View Records In A MySQL Database Table
View Records In A MySQL Database Table

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.

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.

Select Data From MySQL Database Table Sorted By Name Column In Ascending Order Using ORDER By Clause
Select Data From MySQL Database Table Sorted By Name Column In Ascending Order Using ORDER By Clause

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.

Select Data From MySQL Database Table Sorted By Name Column In Descending Order Using ORDER By Clause
Select Data From MySQL Database Table Sorted By Name Column In Descending Order Using ORDER By Clause

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.

You May Also Like

Leave a Comment

* By using this form you agree with the storage and handling of your data by this website.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

This website uses cookies to improve your experience. By using this site, we will assume that you're OK with it. Accept Read More