php - 按 desc 或 limit 30 排序时使用额外参数搜索失败

Question

我最终决定在我的一个 mysql 请求中包含一个搜索参数。当末尾不存在“order by desc”或“limit 30”时有效，如下所示:

$sql = 'SELECT blahblah, bla FROM databasename WHERE lat >= ? AND lat <= ? AND lon >= ? AND lon <= ?';
$params = array( $minlat, $maxlat, $minlon, $maxlon );
if ( isset( $_GET['sear'] ) ) {
    $sql .= "AND (searcher LIKE ?)";

    $params[] = '%'.$_GET['sear'].'%';  

}

但是，当包含“order by desc”或“limit 30”时，它不起作用，如下所示:

$sql = 'SELECT blahblah, bla FROM databasename WHERE lat >= ? AND lat <= ? AND lon >= ? AND lon <= ? ORDER BY autoinc DESC LIMIT 30';
$params = array( $minlat, $maxlat, $minlon, $maxlon );
if ( isset( $_GET['sear'] ) ) {
    $sql .= "AND searcher LIKE ? ";

    $params[] = '%'.$_GET['sear'].'%';  


}

如何让它与“limit 30”或“order by desc”一起使用？

Answer 1

您的 ORDER BY 和 LIMIT 子句必须在您的所有 WHERE 条件

$sql = 'SELECT blahblah, bla FROM databasename WHERE lat >= ? AND lat <= ? AND lon >= ? AND lon <= ? ';
$params = array( $minlat, $maxlat, $minlon, $maxlon );
if ( isset( $_GET['sear'] ) ) {
    $sql .= "AND searcher LIKE ? ";

    $params[] = '%'.$_GET['sear'].'%';  
}
$sql .= ' ORDER BY autoinc DESC LIMIT 30';