[RESOLVED] mysqli->prepare() with IN (?)

knowj

I have a simple query pulling from the database based on a simple CSV

This is a rough example of what I'm doing:

$ids = '1,  2,  3';
$stmt = $mysqli->prepare("SELECT `id`, `name` FROM `table` WHERE `id` IN (?)");
$stmt->bind_param('s', $ids);
$stmt->execute();
$stmt->bind_result($id, $name);
while ($stmt->fetch())
{
echo $id.': '.$name."\n"; 
}

For some reason only id:1 is being returned. Will the bind_param('s', ) be affecting the formatting and causing this problem or am i doing something stupid?

Thanks in advance

laserlight

Unfortunately you cannot bind multiple values to a single placeholder.

knowj

Isn't it technically a single value? or does the statement expect multiple values within the IN () e.g. IN (?, ?, ?, ?) rather than a single comma separated string within IN (?)?

Would i be better off just using $mysqli->query()?

laserlight

knowj wrote:
Isn't it technically a single value?

Well, yes, it is a single value: the string '1, 2, 3'. But you are trying to treat it as a list of values.

knowj wrote:
or does the statement expect multiple values within the IN () e.g. IN (?, ?, ?, ?) rather than a single comma separated string within IN (?)?

Yes.

Would i be better off just using $mysqli->query()?

No, since you can still benefit from the use of prepared statements as an escaping mechanism.

knowj

Thanks for the input.

Would the most effective method be to dynamically build the prepared statement based on the number of variables?

something along the lines of:

$ids = '1,  2,  3';
$array = explode(', ', $ids);
$count = count($array);
for ($i=0,$i<$count;$i++)
{
$params .= '? ,'
}
$params = substr($params, 0, -2);

$stmt = $mysqli->prepare("SELECT `id`, `name` FROM `table` WHERE `id` IN (".$params.")");

call_user_func_array(array($stmt, 'bind_param'), $array); 

$stmt->execute();
$stmt->bind_result($id, $name);
while ($stmt->fetch())
{
echo $id.': '.$name."\n";
}

laserlight

Yes, such an approach can work. You can simplify with the use of [man]str_repeat/man.

cahva

You can replace this:

$count = count($array);
for ($i=0,$i<$count;$i++)
{
$params .= '? ,'
}
$params = substr($params, 0, -2);

..with this:

$arr2 = array_fill(0,count($array),'?');
$params = implode(',',$arr2);

I have done some tests in the past and implode is faster than concatinating string ( $something .= $somethingelse). Althought the issue here probably isnt speed but anyway I wanted to show you other way 🙂 And I always try to avoid using for loops 😃

knowj

cahva;10923816 wrote:
You can replace this:
$count = count($array);
for ($i=0,$i<$count;$i++)
{
$params .= '? ,'
}
$params = substr($params, 0, -2);
..with this:
$arr2 = array_fill(0,count($array),'?');
$params = implode(',',$arr2);
I have done some tests in the past and implode is faster than concatinating string ( $something .= $somethingelse). Althought the issue here probably isnt speed but anyway I wanted to show you other way 🙂 And I always try to avoid using for loops 😃

Thanks for the input. I just used a loop for quickness when bashing together an example.