CODEWITHSUNDEEP
phpmysqlpdo
Sam
Sam

Reputation: 1666

PHP PDO - Bind number of variables dynamically

I'm trying to add a search feature on my site there are 4 different inputs the user can use although they might not use all 4. I'm appending to my sql query depending on which inputs they fill in;

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

This is my query I'm using - 

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

    return $stmt;
}

I'm getting the error - 

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

I think its expecting all 4 inputs to be used and therefore wants 4 bound variables.

Upvotes: 0

Views: 108

Answers (4)

user900030
user900030

Reputation: 68

Could it be because you always feed it an array of size four as to bind, while your query string sometimes won't have as many due to the if statements, hence the error message?

Upvotes: 0

Danagon
Danagon

Reputation: 1

Maybe you can try to say "if this variable is empty, set it to Null" Then the variables that stay empty will be Null instead of undefined.

Upvotes: 0

Can YILDIZ
Can YILDIZ

Reputation: 413

First of all you have a missing parenthesis in your query. You are closing it but not opening.

You should create your variable array also in if clauses as :

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

Upvotes: 4

Alex M.
Alex M.

Reputation: 641

Try to use ? instead the :alias format.

Upvotes: 0

Related Questions