PHP MySQL Order By Clause

The ORDER BY clause is used to sort the rows of a result set.

Ordering the Result Set

The ORDER BY clause can be used in conjugation with the SELECT statement to see the data from a table ordered by a specific field. The ORDER BY clause lets you define the field name to sort against and the sort direction either ascending or descending.

The basic syntax of this clause can be given with:

SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC

Let's make a SQL query using the ORDER BY clause in SELECT statement, after that we will execute this SQL query through passing it to the mysqli_query() function to get the ordered data. Consider the following "persons" table inside the "demo" database:

+-----------+------------+-----------+----------------------+
| person_id | first_name | last_name | email_address        |
+-----------+------------+-----------+----------------------+
|         1 | Peter      | Parker    | peterparker@mail.com |
|         2 | John       | Rambo     | johnrambo@mail.com   |
|         3 | Clark      | Kent      | clarkkent@mail.com   |
|         4 | John       | Carter    | johncarter@mail.com  |
|         5 | Harry      | Potter    | harrypotter@mail.com |
+-----------+------------+-----------+----------------------+

The PHP code in the following example selects all rows from the persons table and sorts the result by the "first_name" column in the alphabetically ascending order.

Example

Download
  • <?php
  • /* Attempt MySQL server connection. Assuming you are running MySQL
  • server with default setting (user 'root' with no password) */
  • $link = mysqli_connect("localhost", "root", "", "demo");
  •  
  • // Check connection
  • if($link === false){
  •     die("ERROR: Could not connect. " . mysqli_connect_error());
  • }
  •  
  • // Attempt select query execution with order by clause
  • $sql = "SELECT * FROM persons ORDER BY first_name";
  • if($result = mysqli_query($link, $sql)){
  •     if(mysqli_num_rows($result) > 0){
  •         echo "<table>";
  •             echo "<tr>";
  •                 echo "<th>person_id</th>";
  •                 echo "<th>first_name</th>";
  •                 echo "<th>last_name</th>";
  •                 echo "<th>email_address</th>";
  •             echo "</tr>";
  •         while($row = mysqli_fetch_array($result)){
  •             echo "<tr>";
  •                 echo "<td>" . $row['person_id'] . "</td>";
  •                 echo "<td>" . $row['first_name'] . "</td>";
  •                 echo "<td>" . $row['last_name'] . "</td>";
  •                 echo "<td>" . $row['email_address'] . "</td>";
  •             echo "</tr>";
  •         }
  •         echo "</table>";
  •         // Close result set
  •         mysqli_free_result($result);
  •     } else{
  •         echo "No records matching your query were found.";
  •     }
  • } else{
  •     echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
  • }
  •  
  • // Close connection
  • mysqli_close($link);
  • ?>

After ordering the result, the result set will look something like this:

+-----------+------------+-----------+----------------------+
| person_id | first_name | last_name | email_address        |
+-----------+------------+-----------+----------------------+
|         3 | Clark      | Kent      | clarkkent@mail.com   |
|         5 | Harry      | Potter    | harrypotter@mail.com |
|         2 | John       | Rambo     | johnrambo@mail.com   |
|         4 | John       | Carter    | johncarter@mail.com  |
|         1 | Peter      | Parker    | peterparker@mail.com |
+-----------+------------+-----------+----------------------+
 

Tip: By default the ORDER BY clause sort the results in ascending order. If you want to sort the records in a descending order, you can use the DESC keyword.

 
Close

Your Feedback:

 

We would love to hear from you! Please say something.