MySQL sorting

We know that to read the data using SQL SELECT statement from the MySQL table.

If we need to sort of read data, we can use MySQL's ORDER BY clause in which you want to set the manner in which field to sort, and then returned in the search results.

Database structure and data used in this section Download: w3big.sql .

grammar

The following is a SQL SELECT statement uses ORDER BY clause to sort query data and then return the data:

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

• You can use any field as the sort of conditions to return query results sorted.
• You can set multiple fields to sort.
• You can use the ASC or DESC keyword to set the search results in ascending or descending order. By default, it is in ascending order.
• You can add WHERE ... LIKE clause to set conditions.

---

ORDER BY clause in the command prompt

The following will use the ORDER BY clause in the SQL SELECT statement to read data in MySQL data tables w3big_tbl:

Examples

Try the following examples, the results will be arranged in ascending order

root@host# mysql -u root -p password;
Enter password:*******
mysql> use w3big;
Database changed
mysql> SELECT * from w3big_tbl ORDER BY w3big_author ASC;
+-----------+---------------+---------------+-----------------+
| w3big_id | w3big_title  | w3big_author | submission_date |
+-----------+---------------+---------------+-----------------+
|         2 | Learn MySQL   | Abdul S       | 2007-05-24      |
|         1 | Learn PHP     | John Poul     | 2007-05-24      |
|         3 | JAVA Tutorial | Sanjay        | 2007-05-06      |
+-----------+---------------+---------------+-----------------+
3 rows in set (0.00 sec)

mysql> SELECT * from w3big_tbl ORDER BY w3big_author DESC;
+-----------+---------------+---------------+-----------------+
| w3big_id | w3big_title  | w3big_author | submission_date |
+-----------+---------------+---------------+-----------------+
|         3 | JAVA Tutorial | Sanjay        | 2007-05-06      |
|         1 | Learn PHP     | John Poul     | 2007-05-24      |
|         2 | Learn MySQL   | Abdul S       | 2007-05-24      |
+-----------+---------------+---------------+-----------------+
3 rows in set (0.00 sec)

mysql> 

W3big_tbl read all the data in the table in ascending order according to w3big_author field.

---

ORDER BY clause in PHP script

Commands you can use PHP function mysql_query () and put on the same SQL SELECT ORDER BY clause to retrieve the data. This function is used to execute SQL commands, then () to output data for all queries through PHP function mysql_fetch_array.

Examples

Try the following examples, data query returns after pressing the descending order w3big_author field.

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT w3big_id, w3big_title, 
               w3big_author, submission_date
        FROM w3big_tbl
        ORDER BY  w3big_author DESC';

mysql_select_db('w3big');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    echo "Tutorial ID :{$row['w3big_id']}  <br> ".
         "Title: {$row['w3big_title']} <br> ".
         "Author: {$row['w3big_author']} <br> ".
         "Submission Date : {$row['submission_date']} <br> ".
         "--------------------------------<br>";
} 
echo "Fetched data successfully\n";
mysql_close($conn);
?>