PHP MySQL ORDER BY Clause

 

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    | [email protected] |
|  2 | John       | Rambo     | [email protected]   |
|  3 | Clark      | Kent      | [email protected]   |
|  4 | John       | Carter    | [email protected]  |
|  5 | Harry      | Potter    | [email protected] |
+----+------------+-----------+----------------------+

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.

Code

<?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); ?>

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

+----+------------+-----------+----------------------+
| id | first_name | last_name | email                |
+----+------------+-----------+----------------------+
|  3 | Clark      | Kent      | [email protected]   |
|  5 | Harry      | Potter    | [email protected] |
|  2 | John       | Rambo     | [email protected]   |
|  4 | John       | Carter    | [email protected]  |
|  1 | Peter      | Parker    | [email protected] |
+----+------------+-----------+----------------------+