使用多个用户选择从数据库中过滤数据

Question

目前我正在开发一个搜索表单，所以我的 SQL 查询需要随着用户输入而改变。请参阅下面的代码示例。$sqlSearch = "SELECT * FROM seafarers WHERE  ";if ($dateS != "") {    $sqlSearch .= "add_date = '" . changeDateSlashToHypen($dateS) . "' and ";}if ($cdcS != "") {    $sqlSearch .= "cdc = '" . $cdcS . "'  and ";}if ($ppS != "") {    $sqlSearch .= "passport LIKE  '%$ppS%'  and ";} if ($surnameS != "") {    $sqlSearch .= "surname LIKE '" . $surnameS . "%'  and ";为了执行这个语句，用户必须选择所有选项；如果用户选择一两个选项，该语句将不起作用。

Answer 1

从一个占位符开始，1=1它总是为真，然后AND用作前缀而不是后缀。

$sqlSearch = "SELECT * FROM seafarers WHERE 1=1 ";

if ($dateS != "") {

    $sqlSearch .= " AND add_date = '" . changeDateSlashToHypen($dateS) . "'";

}

...

但是正如另一个答案中指出的那样，您需要使用准备好的语句。因此，假设您正在使用 mysqli，每个人似乎都出于某种原因这样做：

$sqlSearch = "SELECT * FROM seafarers WHERE 1=1 ";

$types = "";

$parameters = [];

if ($dateS != "") {

    $sqlSearch .= " AND add_date = ?";

    $types .= "s";

    $parameters[] = changeDateSlashToHypen($dateS);

}

if ($cdcS != "") {

    $sqlSearch .= " AND cdc = ?";

    $types .= "s";

    $parameters[] = $cdcS;

}

if ($ppS != "") {

    $sqlSearch .= " AND passport LIKE  ?";

    $types .= "s";

    $parameters[] = "%$ppS%";

} 

if ($surnameS != "") {

    $sqlSearch .= " AND surname LIKE ?";

    $types .= "s";

    $parameters[] = "$surnameS%";

}

$stmt = $db->prepare($sqlSearch);

if (count($parameters) {

    $stmt->bind_param($types, ...$parameters);

}

$stmt->execute();

$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {

    ...

}

Answer 2

不要像这样将您的查询拼凑在一起。使用准备好的语句。例子：

SELECT * 

FROM seafarers 

WHERE (:dt is null or add_date = :dt)

and (:cdc is null or cdc = :cdc)

您必须在执行之前填充查询的参数。