MySQL Sorting

We know that we can use the SQL SELECT statement to read data from a MySQL table.

If we need to sort the retrieved data, we can use the MySQL ORDER BY clause to specify which field and in what order you want to sort, and then return the search results.

Syntax

The following is the SQL SELECT statement using the ORDER BY clause to sort the query data before returning it:

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][default ASC]], [field2...] [ASC [DESC][default ASC]]

• You can use any field as the sorting condition to return the sorted query results.
• You can set multiple fields to sort by.
• You can use the ASC or DESC keywords to set whether the query results are sorted in ascending or descending order. By default, it is sorted in ascending order.
• You can add the WHERE...LIKE clause to set conditions.

Using the ORDER BY Clause in the Command Prompt

The following will use the ORDER BY clause in the SQL SELECT statement to read data from the MySQL table tutorialpro_tbl:

Example

Try the following example, which will sort the results in ascending and descending order.

SQL Sorting

mysql> use tutorialpro;
Database changed
mysql> SELECT * from tutorialpro_tbl ORDER BY submission_date ASC;
+-----------+---------------+---------------+-----------------+
| tutorialpro_id | tutorialpro_title  | tutorialpro_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 3         | Learn Java   | tutorialpro.org    | 2015-05-01      |
| 4         | Learn Python | tutorialpro.org    | 2016-03-06      |
| 1         | Learn PHP    | tutorialpro.org  | 2017-04-12      |
| 2         | Learn MySQL  | tutorialpro.org  | 2017-04-12      |
+-----------+---------------+---------------+-----------------+
4 rows in set (0.01 sec)

mysql> SELECT * from tutorialpro_tbl ORDER BY submission_date DESC;
+-----------+---------------+---------------+-----------------+
| tutorialpro_id | tutorialpro_title  | tutorialpro_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 1         | Learn PHP    | tutorialpro.org  | 2017-04-12      |
| 2         | Learn MySQL  | tutorialpro.org  | 2017-04-12      |
| 4         | Learn Python | tutorialpro.org    | 2016-03-06      |
| 3         | Learn Java   | tutorialpro.org    | 2015-05-01      |
+-----------+---------------+---------------+-----------------+
4 rows in set (0.01 sec)

Read all data from the tutorialpro_tbl table and sort it in ascending order by the submission_date field.

Using the ORDER BY Clause in a PHP Script

You can use the PHP function mysqli_query() along with the same SQL SELECT command with the ORDER BY clause to fetch data.

This function is used to execute the SQL command, and then output all the queried data using the PHP function mysqli_fetch_array().

Example

Try the following example, which will return the queried data sorted in descending order by the submission_date field.

MySQL ORDER BY Test:

<?php
$dbhost = 'localhost';  // mysql server hostname
$dbuser = 'root';       // mysql username
$dbpass = '123456';     // mysql password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('Connection failed: ' . mysqli_error($conn));
}
// Set encoding to prevent Chinese乱码
mysqli_query($conn , "set names utf8");

$sql = 'SELECT tutorialpro_id, tutorialpro_title, 
        tutorialpro_author, submission_date
        FROM tutorialpro_tbl
        ORDER BY  submission_date ASC';

mysqli_select_db( $conn, 'tutorialpro' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('Could not read data: ' . mysqli_error($conn));
}
echo '<h2>tutorialpro.org MySQL ORDER BY Test<h2>';
echo '<table border="1"><tr><td>Tutorial ID</td><td>Title</td><td>Author</td><td>Submission Date</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQLI_ASSOC))
{
    echo "<tr><td> {$row['tutorialpro_id']}</td> ".
         "<td>{$row['tutorialpro_title']} </td> ".
         "<td>{$row['tutorialpro_author']} </td> ".
         "<td>{$row['submission_date']} </td> ".
         "</tr>";
}
echo '</table>';
mysqli_close($conn);
?>

The output result is shown in the following image: