`WHERE id IN (?)` does not work with bindings

[Jemt]

Hi.

Bindings are not working in the following scenario where I only get 1 row returned:

$ids = array(2, 4, 8, 13, 59, 392, 399);
$rows = R::getAll("SELECT * FROM test WHERE id IN (?)", array(implode(", ", $ids));

This returns all the rows as expected:

$ids = array(2, 4, 8, 13, 59, 392, 399);
$rows = R::getAll("SELECT * FROM test WHERE id IN (" . implode(", ", $ids) . ")");

I'm using PHP 7.4 and MySQL 8

[Lynesth]

Hi.

This is because that's not how you're supposed to be doing this. That's not how PDO bindings works, as each parameter must have their own ? placeholder.

Redbean provides a simple method to deal with that (source: https://redbeanphp.com/index.php?p=/finding), so try this:

$ids = array(2, 4, 8, 13, 59, 392, 399);
$rows = R::getAll("SELECT * FROM test WHERE id IN (" . R::genSlots($ids) . ")", $ids);

Edit: R::genSlots will internally generate the following string "?,?,?,?,?,?,?" (as many ? as there are elements in your array) which is needed to assign all the values you need to pass to that query.

[Jemt]

Thanks @Lynesth. I do find it odd that my approach doesn't work though. ? should just be replaced by the string 2, 4, 8, 13, 59, 392, 399 - just like any other string. I'm not sure what makes PDO treat this different from other strings.

[Lynesth]

Taken from https://www.php.net/manual/en/pdo.prepare.php

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute().

Note: Parameter markers can represent a complete data literal only. Neither part of literal, nor keyword, nor identifier, nor whatever arbitrary query part can be bound using parameters. For example, you cannot bind multiple values to a single parameter in the IN() clause of an SQL statement.

[Jemt]

Thank you so much, @Lynesth. I appreciate it 😊