这个SQL语句有什么问题吗?

so I am building a search script and meed to pass on two variables, but first I want to make sure that the SQL QUery is correct so I am hard-coding the variable for now. So my variable is

因此，我正在构建一个搜索脚本和meed来传递两个变量，但是首先我要确保SQL查询是正确的，所以我现在正在硬编码变量。所以我的变量

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

This is getting passed through to the query, which looks like this:

这是传递给查询的，它看起来是这样的:

$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";

and then when the query runs, I get this message:

然后当查询运行时，我得到如下信息:

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

But to me the query looks right, what am I missing here?

但是对我来说，这个查询是正确的，我在这里缺少什么?

Thanks in advance.

提前谢谢。

3 个解决方案

#1

You can't have WHERE in there twice. You also seem to be trying to filter on province values in two different ways. Based on the assumption that $province will always be an array of values (even if only a single value is given), you can try this:

你不能在那里两次。您似乎也试图用两种不同的方式来过滤省的值。基于$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";

#2

Your SQL contains two WHERE's.

您的SQL包含两个WHERE's。

SELECT * FROM persons WHERE 1=1
                      AND firstname='fn'
                      AND surname='sn'
                      AND province='p'
                      WHERE province IN ($comma_separated)
                      ORDER BY surname

Change the last bit to:

将最后一点更改为:

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

Which becomes:

这就变成:

AND province='p'
AND province IN ('Alberta','Ontario')

#3

-1

Change the last part to:

将最后一部分更改为:

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

#1