Mysqli 简明教程
MySQLi - Sorting Results
我们已经见过 SQL SELECT 命令以从 MySQL 表中获取数据。当您选择行时,MySQL 服务器可以按任何顺序自由返回它们,除非您通过说明如何对结果排序来指示它这样做。但是,您可以通过添加 ORDER BY 子句来对结果集进行排序,该子句指定您要排序的列或多列。
We have seen the SQL SELECT command to fetch data from a MySQL table. When you select rows, the MySQL server is free to return them in any order, unless you instruct it otherwise by saying how to sort the result. But, you sort a result set by adding an ORDER BY clause that names the column or columns which you want to sort.
Syntax
以下代码块是 SELECT 命令的一个通用 SQL 语法,以及 ORDER BY 子句以对 MySQL 表中的数据进行排序。
The following code block is a generic SQL syntax of the SELECT command along with the ORDER BY clause to sort the data from a MySQL table.
SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]
-
You can sort the returned result on any field, if that field is being listed out.
-
You can sort the result on more than one field.
-
You can use the keyword ASC or DESC to get result in ascending or descending order. By default, it’s the ascending order.
-
You can use the WHERE…LIKE clause in the usual way to put a condition.
Using ORDER BY clause at the Command Prompt
这将使用具有 ORDER BY 子句的 SQL SELECT 命令从 MySQL 表中获取数据 – tutorials_tbl 。
This will use the SQL SELECT command with the ORDER BY clause to fetch data from the MySQL table – tutorials_tbl.
Example
尝试以下示例,它以升序返回结果。
Try out the following example, which returns the result in an ascending order.
root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl ORDER BY tutorial_author ASC
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_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.42 sec)
mysql>
验证按升序列出的所有作者姓名。
Verify all the author names that are listed out in the ascending order.
Using ORDER BY clause inside a PHP Script
PHP 使用 mysqli query() 或 mysql_query() 函数从 MySQL 表中获取排序记录。这个函数接收两个参数,并在成功时返回 TRUE,并在失败时返回 FALSE。
PHP uses mysqli query() or mysql_query() function to get sorted records from a MySQL table. This function takes two parameters and returns TRUE on success or FALSE on failure.
Syntax
$mysqli→query($sql,$resultmode)
Sr.No. |
Parameter & Description |
1 |
$sql Required - SQL query to get sorted records from a table. |
2 |
$resultmode Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used. |
Example
尝试使用以下示例从表中获取排序记录 -
Try the following example to get sorted records from a table −
将以下示例复制粘贴为 mysql_example.php:
Copy and paste the following example as mysql_example.php −
<html>
<head>
<title>Sorting MySQL Table records</title>
</head>
<body>
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root@123';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli→connect_errno ) {
printf("Connect failed: %s<br />", $mysqli→connect_error);
exit();
}
printf('Connected successfully.<br />');
$sql = "SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl order by tutorial_title asc";
$result = $mysqli→query($sql);
if ($result→num_rows > 0) {
while($row = $result→fetch_assoc()) {
printf("Id: %s, Title: %s, Author: %s, Date: %d <br />",
$row["tutorial_id"],
$row["tutorial_title"],
$row["tutorial_author"],
$row["submission_date"]);
}
} else {
printf('No record found.<br />');
}
mysqli_free_result($result);
$mysqli→close();
?>
</body>
</html>
Output
访问部署在 Apache Web 服务器上的 mysql_example.php, 并验证输出。
Access the mysql_example.php deployed on apache web server and verify the output.
Connected successfully.
Id: 5, Title: Apache Tutorial, Author: Suresh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021