Link to home
Start Free TrialLog in
Avatar of Gabriel7
Gabriel7

asked on

I'm having an issue with a WHERE IN select query in mysqli in a prepared statement.

I'm receiving this error when I run the program...

Warning: mysqli_stmt::bind_param(): Number of elements in type definition string doesn't match number of bind variables

The array has 50 items.
My array item is $_REQUEST['itemslist'].

//I'm setting up the query types with this.
$queryTypes = array_fill(0,count($_REQUEST['itemslist']),'s');

//Building the question marks here.
$questionMarks = array_fill(0,count($_REQUEST['itemslist']),'?');

//Merging the query types with the itemslist.
$params = array_merge($queryTypes, $_REQUEST['itemslist']);

//My query
if($grabStuff = $mysqli->prepare("SELECT var1, var2, var3, var4 FROM my_table where id in (" . $param . ")"))
{
call_user_func_array(array($grabStuff, 'bind_param'), refValues($params));      
$grabStuff->execute();
}


//The function
function refValues($arr)
{
$refs = array();
foreach($arr as $key => $value)
      $refs[$key] = &$arr[$key];
return $refs;
}

When I print the array...it shows all 50 items.
When I print the params (with an s) I get 100 items.
I'm using php 7.

Thanks!
Avatar of David Favor
David Favor
Flag of United States of America image
When you bind a number of variables, as I recall, you must also pass that exact number of variables... otherwise, the bind breaks, because you're trying to use missing variables.

If I were faced with this... I'd either...

1) For small numbers of calls, skip use of prepare.

2) For large numbers of calls, I'd likely create some mechanism to count the number of parameters passed + create a prepare statement handle for that number of bound variables + cache the statement handle. Then on subsequent calls, use the cached prepare which matched my number of bound variables.

I'd only do #2, if I had to read many rows, repeatedly.
Avatar of Gabriel7
Gabriel7

ASKER

If my ? match my 's'...match my variables....does that not balance?

I guess, from my research...I've believed I've done it correctly....except maybe how I have the (" . $param . ")

Try to avoid not using a prepare statement...and #2 ...well....I use "where in () quite a bit...and it would be a burden...
Avatar of Gabriel7
Gabriel7

ASKER

I've resolved the problem.

$queryTypes = array_fill(0,count($_REQUEST['itemslist']),'s'); was my issue.

Instead of this....I concatenated the "s"'s as a single string...added it as the first element of a new array, then added the

$s_string[] = str_repeat('s',count($_REQUEST['itemslist']));

//merged with itemlist
$params = array_merge($s_string, $_REQUEST['itemslist']);

 //Building the question marks here.
 $questionMarks = array_fill(0,count($_REQUEST['itemslist']),'?');

 //My query
 if($grabStuff = $mysqli->prepare("SELECT var1, var2, var3, var4 FROM my_table where id in (" . $param . ")"))
 {
 call_user_func_array(array($grabStuff, 'bind_param'), refValues($params));      
 $grabStuff->execute();
 }


 //The function
 function refValues($arr)
 {
 $refs = array();
 foreach($arr as $key => $value)
       $refs[$key] = &$arr[$key];
 return $refs;
 }



Thanks!
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.