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

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)您必须在执行之前填充查询的参数。