Article directory
MySQL multi-field sorting? MySQL ORDER BY descending/ascending order query statement/function
MySQL sort
We know to use SQL SELECT statement to read data from MySQL table.
If we need to sort the read data, we can use MySQL's ORDER BY clause to specify which field and sorting method we want to use, and then return the search results.
grammar
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 sort condition to return sorted query results.
- You can set multiple fields to sort.
- You can use the ASC or DESC keywords to set the query results to be sorted in ascending or descending order.By default, it is in ascending order.
- You can add WHERE...LIKE clauses 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 the data in the MySQL data table chenweiliang_tbl:
Examples
Try the examples below and the results will be sorted in ascending and descending order.
SQL sort
mysql> use chenweiliang; Database changed mysql> SELECT * from chenweiliang_tbl ORDER BY submission_date ASC; +-----------+---------------+---------------+-----------------+ | chenweiliang_id | chenweiliang_title | chenweiliang_author | submission_date | +-----------+---------------+---------------+-----------------+ | 3 | 学习 Java | chenweiliang.com | 2015-05-01 | | 4 | 学习 Python | chenweiliang.com | 2016-03-06 | | 1 | 学习 PHP | 陈沩亮博客 | 2017-04-12 | | 2 | 学习 MySQL | 陈沩亮博客 | 2017-04-12 | +-----------+---------------+---------------+-----------------+ 4 rows in set (0.01 sec) mysql> SELECT * from chenweiliang_tbl ORDER BY submission_date DESC; +-----------+---------------+---------------+-----------------+ | chenweiliang_id | chenweiliang_title | chenweiliang_author | submission_date | +-----------+---------------+---------------+-----------------+ | 1 | 学习 PHP | 陈沩亮博客 | 2017-04-12 | | 2 | 学习 MySQL | 陈沩亮博客 | 2017-04-12 | | 4 | 学习 Python | chenweiliang.com | 2016-03-06 | | 3 | 学习 Java | chenweiliang.com | 2015-05-01 | +-----------+---------------+---------------+-----------------+ 4 rows in set (0.01 sec)
Read all the data in the chenweiliang_tbl table and sort 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() and the same SQL SELECT command with an ORDER BY clause to get the data.
This function is used to execute SQL commands and then output all the queried data through the PHP function mysqli_fetch_array().
Examples
Try the following example, the queried data is returned in descending order of the submission_date field.
MySQL ORDER BY test:
<?
php
$dbhost = 'localhost:3306'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('连接失败: ' . mysqli_error($conn));
}
// 设置编码，防止中文乱码
mysqli_query($conn , "set names utf8");
$sql = 'SELECT chenweiliang_id, chenweiliang_title,
chenweiliang_author, submission_date
FROM chenweiliang_tbl
ORDER BY submission_date ASC';
mysqli_select_db( $conn, 'chenweiliang' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
die('无法读取数据: ' . mysqli_error($conn));
}
echo '<h2>陈沩亮博客 MySQL ORDER BY 测试<h2>';
echo '<table border="1"><tr><td>教程 ID</td><td>标题</td><td>作者</td><td>提交日期</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQL_ASSOC))
{
echo "<tr><td> {$row['chenweiliang_id']}</td> ".
"<td>{$row['chenweiliang_title']} </td> ".
"<td>{$row['chenweiliang_author']} </td> ".
"<td>{$row['submission_date']} </td> ".
"</tr>";
}
echo '</table>';
mysqli_close($conn);
?>Hopefully, the article "MySQL Multi-Field Condition Sorting? MySQL ORDER BY Descending/Ascending Order Query Statement/Function" shared on Chen Weiliang's blog ( https://www.chenweiliang.com/ ) will be helpful to you.
Feel free to share this article's link: https://www.chenweiliang.com/cwl-476.html