PHP PDO - 动态绑定(bind)变量数

Question

我正在尝试在我的网站上添加搜索功能，用户可以使用 4 种不同的输入，尽管他们可能不会使用全部 4 种。我根据他们填写的输入附加到我的 sql 查询；

$query = "SELECT * FROM cars WHERE status = 2 ";
if($_GET['ref']){
  $query .= " AND ref = :ref";
}
if($_GET['doors']){
  $query .= " AND doors = :doors";
}
if($_GET['wheels']){
  $query .= " AND wheels = :wheels";
}
if($_GET['location']){
  $query .= " AND location = :location";
}
$query .= ")";

$adverts = Singlequery ($query, array(              
        'ref' => $_GET['ref'],
        'doors' => $_GET['doors'],
        'wheels' => $_GET['wheels'],
        'location' => $_GET['location']
), $conn);

这是我正在使用的查询 -

function query($query, $bindings, $conn)
{
    $stmt = $conn->prepare($query);
    $stmt->execute($bindings);

    return $stmt;
}

我收到错误 -

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

我认为它期望使用所有 4 个输入，因此需要 4 个绑定(bind)变量。

Answer 1

首先，您的查询中缺少括号。您正在关闭它而不是打开它。

您还应该在 if 子句中创建变量数组:

$query = "SELECT * FROM cars WHERE status = 2 ";
$data=array();
if($_GET['ref']){
  $query .= " AND ref = :ref";
  $data['ref']=$_GET['ref'];
}
if($_GET['doors']){
  $query .= " AND doors = :doors";
  $data['doors']=$_GET['doors'];
}
if($_GET['wheels']){
  $query .= " AND wheels = :wheels";
  $data['wheels']=$_GET['wheels'];
}
if($_GET['location']){
  $query .= " AND location = :location";
  $data['location']=$_GET['location'];
}

$adverts = Singlequery($query, $data, $conn);