dontknow dontknow - 3 months ago 23
PHP Question

filter data pdo

What I am trying to do is similar to this. Search Filtering with 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; ?>


When I select a gender and religion on my dropdown, the result is fine.

But when I select only one, let's say gender, I received an error:


number of bound variables does not match number of tokens


I'm a bit confused where to place
$sql->bindParam(...);
. I guess this is the cause of my error? Or if there's more 'error' or if there's anything that's not right, please correct me. Thank you in advance.

Answer

Just add value right along with placeholder and then send them right to execute

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);