此SQL IF语句有什么问题？

debugcn 发表于 Dev

JLA

因此，我正在构建一个搜索脚本，并注意传递两个变量，但是首先，我想确保SQL QUery是正确的，因此我现在对变量进行硬编码。所以我的变量是

$comma_separated = "'Alberta','Ontario'";

这将传递给查询，如下所示：

$sql = "SELECT * FROM persons WHERE 1=1";



if ($firstname)
$sql .= " AND firstname='" . mysqli_real_escape_string($mysqli,$firstname) . "'";

if ($surname)
$sql .= " AND surname='" . mysqli_real_escape_string($mysqli,$surname) . "'";

if ($province)
$sql .= " AND province='" . mysqli_real_escape_string($mysqli,$comma_separated) . "' WHERE province IN ($comma_separated)";

$sql .= " ORDER BY surname";

然后当查询运行时，我收到此消息：

cannot run the query because: 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 'WHERE province IN ('Alberta','Ontario') ORDER BY surname LIMIT 0, 5' at line 1

但是对我而言，查询看起来正确，我在这里想念的是什么？

提前致谢。

迈克·布兰特（Mike Brant）

你不能WHERE在那里两次。您似乎还试图以两种不同的方式来筛选省值。基于$province将始终是值数组的假设（即使仅给出单个值），也可以尝试以下操作：

$sql = "SELECT * FROM persons WHERE 1=1";

if (!empty($firstname)) {
    $sql .= " AND firstname='" . mysqli_real_escape_string($mysqli,$firstname) . "'";
}

if (!empty($surname)) {
    $sql .= " AND surname='" . mysqli_real_escape_string($mysqli,$surname) . "'";
}

if (!empty($province)) {
    array_walk($province, function($value, $key_not_used) use ($mysqli) {
        return mysqli_real_escape_string($mysqli, $value);
    });
    $sql .= " AND province IN ('" . implode(',', $province) . "')";
}

$sql .= " ORDER BY surname";

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。