过滤数据pdo


filter data pdo

我想做的是类似于此。PHP/MySQL搜索过滤

  <?php 
        require 'con.php';
        $minage     = $_POST['data'][0];
        $maxage     = $_POST['data'][1];
        $gender     = $_POST['data'][2];
        $religion   = $_POST['data'][3];
        $query = "SELECT CONCAT(firstname, ' ', middlename, ' ', lastname, ' ', extension_name) as fullname, TIMESTAMPDIFF(YEAR, birthday ,NOW()) as age FROM mytable";
$filter = array();

       if($gender != -1){
           $gender = substr($gender, 1, -1);
           $filter[] = "gender = :gender";
       }
       if($religion != -1){
            $filter[] = "religion = :religion";
       }
       if(count($filter) > 0){
           $query .= " WHERE " . implode(' AND ', $filter);
           $sql = $connection->prepare($query);
     ->    $sql->bindParam(':gender', $gender, PDO::PARAM_STR);
     ->    $sql->bindParam(':religion', $religion, PDO::PARAM_STR);
           $sql->execute();
           $res = $sql->fetchAll();
      }else{
           $sql = $connection->prepare($query);
           $sql->execute();
           $res = $sql->fetchAll();
      }
  ?>
   <?php foreach($res as $row): ?>
         <div><?php echo $row['fullname'];?></div>
   <?php endforeach; ?>

当我在下拉菜单中选择性别和宗教时,结果很好。

但是当我只选择一个,比如性别,我收到了一个错误:

绑定变量的数量与令牌的数量不匹配

我有点困惑在哪里放置$sql->bindParam(...);。我想这就是我出错的原因吧?或者如果有更多的"错误",或者如果有什么不对的地方,请纠正我。提前谢谢你。

只需将值与占位符一起添加,然后将它们发送到执行

if($gender != -1){
   $filter[] = "gender = ?";
   $values[] = $gender;
}
if($religion != -1){
    $filter[] = "religion = ?";
    $values[] = $religion;
}
$query .= " WHERE 1 AND " . implode(' AND ', $filter);
$stmt = $connection->prepare($query);
$stmt->execute($values);

绑定也应该是有条件的:

if($gender != -1){
           $filter[] = "gender = :gender";
       }
       if($religion != -1){
            $filter[] = "religion = :religion";
       }
       if(count($filter) > 0){
           $gender = substr($gender, 1, -1);
           $query .= " WHERE " . implode(' AND ', $filter);
           $sql = $connection->prepare($query);
           if($gender != -1){
         $sql->bindParam(':gender', $gender, PDO::PARAM_STR);
           }
           if($religion != -1){
         $sql->bindParam(':religion', $religion, PDO::PARAM_STR);
           }
           $sql->execute();
           $res = $sql->fetchAll();
      }else{

这是一个非常糟糕的组织代码,试着让它更容易读