1

i have a mysql select query statement like this using php, 

$rrt = implode(',',array_fill(0,count($ees),'?'));
$rrr = implode(',',array_fill(0,count($array),'?'));



$select = $con ->prepare("SELECT name from table WHERE name NOT IN ($array) AND ageNOT IN ($rrt) GROUP BY name ");

i have tried:

$select = $con ->prepare("SELECT name from table WHERE name NOT IN ($rrr) AND age NOT IN ($rrt) GROUP BY name ");
$select->($array);

error output

Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in

$select = $con ->prepare("SELECT name from table WHERE name NOT IN ($array) AND age NOT IN ($rrt) GROUP BY name ");
$select -> bindParam(1,$array);
$select -> bindParam(2,$ees);
$select -> execute();

error output

Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in

$select = $con ->prepare("SELECT name from table WHERE name NOT IN ('".implode('","',$array)."') AND ageNOT IN ($rrt) GROUP BY name ");
$select -> execute($ees);

this one gave some results but not what i want; i want to use a query like this :

$select = $con ->prepare("SELECT name from table WHERE name NOT IN ($array) AND age NOT IN ($rrt) GROUP BY name ");
$select -> execute($ees);

but i dont want to be getting number of parameter bound error, please help! thanks in advance.

Masivuye Cokile
  • 4,754
  • 3
  • 19
  • 34
matthew
  • 21
  • 5
  • Looks like you're trying to bind 2 arrays to a single parameterised query. Unfortunately you can't do that, if you're using a parameterised query you can only bind a single array of parameters to it. You might want to merge the two arrays into a single one. – GordonM Jun 26 '18 at 10:06
  • Voted to reopen on the grounds that the linked question didn't really resolve this specific issue – GordonM Jun 26 '18 at 10:11

2 Answers2

1

This should work:

$rrr = implode(',',array_fill(0,count($array),'?'));
$rrt = implode(',',array_fill(0,count($ees),'?'));

$select = $con ->prepare("SELECT name from table WHERE name NOT IN ($rrr) AND age NOT IN ($rrt) GROUP BY name ");
$index = 1;
foreach($array as $key => $value) {
    $select -> bindParam($index++,$array[$key]);
}
foreach($ees as $key => $value) {
    $select -> bindParam($index++,$ees[$key]);
}
$select -> execute();

Note bindParam requires a variable reference.

Adder
  • 5,708
  • 1
  • 28
  • 56
0

Assuming you're using PDO there are only two ways to pass values to a parameterised query:

  • with bindParam()
  • By passing an array of parameters into execute()

In the former case you'd either bind by the position of the question mark (for ? style parameters), or by the name of the parameter (for named parameters)

In either case you can only bind one variable to one parameter. You can't bind an entire array with bindParam (although you can bind individual entries in the array).

For passing an array into execute, either it must be an indexed array with the same number of entries as there are question marks (for ? style) or it must be an array with keys that match the named placeholders (for named parameter style). Again, you can only bind one entry per placeholder.

What you appear to be trying to do is bind two arrays to the same query. You can't do that. You do have options though.

  • foreach over each array and bindparam() in a loop
  • Build a single array with all the parameters you want to pass in (being careful that the position in the array corresponds to the position of the question mark you want to bind in the query!) and pass that into execute()
GordonM
  • 31,179
  • 15
  • 87
  • 129