PHP MySQL ORDER BY Clause

In this tutorial you will learn how to sort and display the data from a MySQL table in ascending or descending order using PHP.

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 query through passing it to the PHP mysqli_query() function to get the ordered data. Consider the following persons table inside the demo database:

+----+------------+-----------+----------------------+
| id | first_name | last_name | email                |
+----+------------+-----------+----------------------+
|  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

Procedural Object Oriented PDO
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>id</th>";
  •                 echo "<th>first_name</th>";
  •                 echo "<th>last_name</th>";
  •                 echo "<th>email</th>";
  •             echo "</tr>";
  •         while($row = mysqli_fetch_array($result)){
  •             echo "<tr>";
  •                 echo "<td>" . $row['id'] . "</td>";
  •                 echo "<td>" . $row['first_name'] . "</td>";
  •                 echo "<td>" . $row['last_name'] . "</td>";
  •                 echo "<td>" . $row['email'] . "</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);
  • ?>
  • <?php
  • /* Attempt MySQL server connection. Assuming you are running MySQL
  • server with default setting (user 'root' with no password) */
  • $mysqli = new mysqli("localhost", "root", "", "demo");
  •  
  • // Check connection
  • if($mysqli === 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($sql)){
  •     if($result->num_rows > 0){
  •         echo "<table>";
  •             echo "<tr>";
  •                 echo "<th>id</th>";
  •                 echo "<th>first_name</th>";
  •                 echo "<th>last_name</th>";
  •                 echo "<th>email</th>";
  •             echo "</tr>";
  •         while($row = $result->fetch_array()){
  •             echo "<tr>";
  •                 echo "<td>" . $row['id'] . "</td>";
  •                 echo "<td>" . $row['first_name'] . "</td>";
  •                 echo "<td>" . $row['last_name'] . "</td>";
  •                 echo "<td>" . $row['email'] . "</td>";
  •             echo "</tr>";
  •         }
  •         echo "</table>";
  •         // Free result set
  •         $result->free();
  •     } else{
  •         echo "No records matching your query were found.";
  •     }
  • } else{
  •     echo "ERROR: Could not able to execute $sql. " . $mysqli->error;
  • }
  •  
  • // Close connection
  • $mysqli->close();
  • ?>
  • <?php
  • /* Attempt MySQL server connection. Assuming you are running MySQL
  • server with default setting (user 'root' with no password) */
  • try{
  •     $pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
  •     // Set the PDO error mode to exception
  •     $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  • } catch(PDOException $error){
  •     die("ERROR: Could not connect. " . $error->getMessage());
  • }
  •  
  • // Attempt select query execution
  • try{
  •     $sql = "SELECT * FROM persons ORDER BY first_name";
  •     $result = $pdo->query($sql);
  •     if($result->rowCount() > 0){
  •         echo "<table>";
  •             echo "<tr>";
  •                 echo "<th>id</th>";
  •                 echo "<th>first_name</th>";
  •                 echo "<th>last_name</th>";
  •                 echo "<th>email</th>";
  •             echo "</tr>";
  •         while($row = $result->fetch()){
  •             echo "<tr>";
  •                 echo "<td>" . $row['id'] . "</td>";
  •                 echo "<td>" . $row['first_name'] . "</td>";
  •                 echo "<td>" . $row['last_name'] . "</td>";
  •                 echo "<td>" . $row['email'] . "</td>";
  •             echo "</tr>";
  •         }
  •         echo "</table>";
  •         // Free result set
  •         unset($result);
  •     } else{
  •         echo "No records matching your query were found.";
  •     }
  • } catch(PDOException $error){
  •     die("ERROR: Could not able to execute $sql. " . $error->getMessage());
  • }
  •  
  • // Close connection
  • unset($pdo);
  • ?>

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

+----+------------+-----------+----------------------+
| id | first_name | last_name | email                |
+----+------------+-----------+----------------------+
|  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.