带有ORDER和WHERE的SQL/PHP查询 - SQL/PHP query with ORDER and WHERE

SQL/PHP query with ORDER and WHERE

本文关键字：PHP 查询 SQL ORDER WHERE 带有 | 更新日期: 2023-09-27

我遇到了SQL查询问题。我正试图在两次约会之间以有序的方式获取数据。以下是我遇到问题的代码的简化：

$query = "SELECT Name DateTime FROM table1";
$query .= " ORDER BY DateTime ASC";
$query .= " WHERE DateTime between  date1 AND date2";
$result = mysql_query($query);
//fetch data here
while($row = mysql_fetch_array($result))
{
$output[] = $row;
}
    print(json_encode($output));

当我注释掉这一行时，上面的代码就起作用了：$query。="ORDER BY DateTime ASC"；

我可以更正什么以便返回两个日期之间的排序数据？

编辑：当我将ORDER移到WHERE之后时，$output现在不应该排序吗？我将其返回到安卓应用程序，但情况似乎并非如此。

ORDER BY子句必须位于末尾：

$query = "SELECT Name DateTime FROM table1";
$query .= " WHERE DateTime between  date1 AND date2";
$query .= " ORDER BY DateTime ASC";

条款的一般顺序是：

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

您的查询几乎是正确的，但ORDER BY子句必须在WHERE之后。

将其更改为：

$query = "SELECT Name DateTime FROM table1";
$query .= " WHERE DateTime between  date1 AND date2";
$query .= " ORDER BY DateTime ASC";
$result = mysql_query($query);
//fetch data here
while($row = mysql_fetch_array($result))
{
$output[] = $row;
}
    print(json_encode($output));

问题在于查询中元素的顺序：ORDER部分应该在WHERE部分之后。

例如，请参阅MySQL SELECT语法的文档：

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name' export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

将ORDER BY放在查询的末尾

$query = "SELECT Name, DateTime FROM table1";
$query .= " WHERE DateTime between  date1 AND date2";
$query .= " ORDER BY DateTime ASC";

您可以使用where条件进行选择，然后对结果进行排序。所以在查询的末尾放置订单