gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-11-29 09:28:27 26 4
gpt4 key购买 nike

我使用以下语句从 SQL 加载数据,SELECT 查询运行良好,直到执行 php IF 为止。

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

Fatal error : Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND sca IN (?)' at line 1' in C:\Users\Amin\Documents\NetBeansProjects\fetch.php:34 Stack trace:

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

C:\Users\Amin\Documents\NetBeansProjects\fetch.php on line 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 = '';

最佳答案

正如 @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];
}
}

关于php - 无法使用 ORDER BY 和 php IF 选择表单 DB,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/59202293/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com