PHP PDO - Bind number of variables dynamically

Question

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.

It's a verbose way to generate a query like this, just set the values of the array which used for binding in the if($_GET ..) statements too, e.g. $query .= "AND location ..."; $bind[':location'] = $_GET['location']; etc. — dbf
– dbf, Commented Dec 18, 2014 at 11:40

Can YILDIZ · Accepted Answer · 2014-12-18 11:39:21Z

4

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

answered Dec 18, 2014 at 11:39

Can YILDIZ

4133 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sam Over a year ago

Thanks I see what you've done and how you've done it big help thanks!

Alex M. · Accepted Answer · 2014-12-18 11:37:44Z

0

Try to use ? instead the :alias format.

answered Dec 18, 2014 at 11:37

Alex M.

6412 gold badges6 silver badges20 bronze badges

Comments

Danagon · Accepted Answer · 2014-12-18 11:40:42Z

0

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.

answered Dec 18, 2014 at 11:40

Danagon

1

Comments

user900030 · Accepted Answer · 2014-12-18 11:44:11Z

0

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?

answered Dec 18, 2014 at 11:44

user900030

684 bronze badges

Collectives™ on Stack Overflow

PHP PDO - Bind number of variables dynamically

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related