无法使用 ORDER BY 和 php IF 选择表单 DB

我使用以下 to 语句从 SELECT 查询中加载数据,在执行 IFSQL之前效果很好。php

我想ORDER BY在执行 if 语句时在单个语句中使用 2 我得到

致命错误:未捕获异常 'PDOException' 并带有消息 'SQLSTATE[42000]:语法错误或访问冲突:1064 您的 SQL 语法有错误;检查与您的 MySQL 服务器版本相对应的手册,以在 C:\Users\Amin\Documents\NetBeansProjects\fetch.php:34 中的第 1 行的“AND sca IN (?)”附近使用正确的语法 堆栈跟踪:

0 C:\Users\Amin\Documents\NetBeansProjects\fetch.php(34): PDO->prepare('SELECT * FROM a...') #1 {main} 抛出

C:\Users\Amin\Documents\NetBeansProjects\fetch.php 在第 34 行

我该如何解决这个问题

if (isset($_POST["action"])) {

$query = "SELECT * FROM allpostdata WHERE sts = '1' AND mca='Vehicle' ORDER BY pdt DESC";


if (!empty($_POST['cate'])) {

    $query .= " AND sca IN (" . str_repeat("?,", count($_POST['cate']) - 1) . "?)";

} else {

    $_POST['cate'] = []; // in case it is not set 

}


if (!empty($_POST['brand'])) {

    $query .= " AND product_brand IN (" . str_repeat("?,", count($_POST['brand']) - 1) . "?)";

} else {

    $_POST['brand'] = []; // in case it is not set 

}


if (!empty($_POST['model'])) {

    $query .= " AND mdl IN (" . str_repeat("?,", count($_POST['model']) - 1) . "?)";

} else {

    $_POST['model'] = []; // in case it is not set 

}


if (!empty($_POST['sort'])) {

    if ($_POST["sort"][0] == "ASC" || $_POST["sort"][0] == "DESC") { //simplistic whitelist

        $query .= " ORDER BY prs " . $_POST['sort'][0];

    }

}


$stmt = $conn->prepare($query);

$params = array_merge($_POST['cate'], $_POST['brand'], $_POST['model']);

$stmt->execute($params);

$result = $stmt->fetchAll();

$total_row = $stmt->rowCount();

$output = '';


翻阅古今
浏览 107回答 1
1回答

杨魅力

正如@aynber 已经提到的,order by 应该是查询中的最后一个子句。因此,正确的形式如下:if (isset($_POST["action"])) {$query = "SELECT * FROM allpostdata WHERE sts = '1' AND mca='Vehicle'";if (!empty($_POST['cate'])) {$query .= " AND sca IN (" . str_repeat("?,", count($_POST['cate']) - 1) . "?)";} else {$_POST['cate'] = []; // in case it is not set }if (!empty($_POST['brand'])) {$query .= " AND product_brand IN (" . str_repeat("?,", count($_POST['brand']) - 1) . "?)";} else {$_POST['brand'] = []; // in case it is not set }if (!empty($_POST['model'])) {$query .= " AND mdl IN (" . str_repeat("?,", count($_POST['model']) - 1) . "?)";} else {$_POST['model'] = []; // in case it is not set }$query .= " ORDER BY pdt DESC";if (!empty($_POST['sort'])) {if ($_POST["sort"][0] == "ASC" || $_POST["sort"][0] == "DESC") { //simplistic whitelist    $query .= ", prs " . $_POST['sort'][0];}}
打开App,查看更多内容
随时随地看视频慕课网APP