MySQL sort

If you need to sort the read data, you can use MySQL's ORDER BY clause to set which field you want to sort by and how, and then return the search results.

The following is a SQL SELECT statement that uses the ORDER BY clause to sort the query data before returning the data:

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

You can use any field as a sorting condition to return sorted query results.

You can set multiple fields to sort.

You can use the ASC or DESC keyword to set the query results to be sorted in ascending or descending order. By default, it is sorted in ascending order.

You can add WHERE...LIKE clause to set conditions.

Use the ORDER BY clause in the command prompt

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

Try the following example, the results will be sorted in ascending order

MariaDB [RUNOOB]> SELECT * from runoob_tbl ORDER BY runoob_author ASC;
+-----------+--------------+------ ----------+------------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+---------- -+-------------+---------------+----------------- +
| 2 | Learn MySQL | Abdul S | 2016-11-26 |
| 4 | mysql | cakin24 | 2016-11-26 |
| 1 | Learn PHP | John Poul | 2016-11-26 |
+- ----------+--------------+--------------+-------- ----------+
3 rows in set (0.00 sec)

MariaDB [RUNOOB]> SELECT * from runoob_tbl ORDER BY runoob_author DESC;
+----------+ ---------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+------------ ---+-----------------+
| 1 | Learn PHP | John Poul | 2016-11-26 |
| 4 | mysql | cakin24 | 2016-11- 26 |
| 2 | Learn MySQL | Abdul S | 2016-11-26 |
+-----------+-------------+--- ------------+------------------+
3 rows in set (0.00 sec)

Read all data in runoob_tbl table And sorted in ascending order by the runoob_author field.

Using the ORDER BY clause in PHP scripts

You can use the PHP function mysql_query() and the SQL SELECT command with the ORDER BY clause to obtain data. This function is used to execute SQL commands and then output all queried data through the PHP function mysql_fetch_array().

Example

Try the following example. The queried data is returned in descending order of the runoob_author field.

$dbhost ='localhost:3036';

$dbuser ='root';

$dbpass ='rootpassword';

$conn = mysql_connect($dbhost, $dbuser, $dbpass );

if(! $conn )

{

die('Could not connect: '. mysql_error());

}

$sql ='SELECT runoob_id, runoob_title,

FROM runoob_tbl

ORDER BY runoob_author DESC';

mysql_select_db('RUNOOB');

$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['runoob_id']}
".

"Title: {$row['runoob_title']}
".

"Author: {$row['runoob_author']}
".

"Submission Date : {$row['submission_date']}
".

"-------------------------------- --
";

}

echo "Fetched data successfullyn";

mysql_close($conn);

?>

Run results