How can I create a prepared statement for IN () operator?

Prepared statement can represent complete data literal only. Not a part of literal, nor a complex expression, nor identifier. But either string or number only. So, a very common pitfall is a query like this:

$ids = array(1,2,3);
$stm = $pdo->prepare(“SELECT * FROM t WHERE id IN (?)”);
$stm->execute(array($ids));

it will actually bind a word ‘Array’ and raise a warning.

One have to create a query with placeholders representing every array member, and then bind this array values for execution:

$ids = array(1,2,3);
$stm = $pdo->prepare(“SELECT * FROM t WHERE id IN (?,?,?)”);
$stm->execute($ids);

To make this query more flexible, it’s better to create a string with ?s dynamically:

$ids = array(1,2,3);
$in = str_repeat(‘?,’, count($arr) – 1) . ‘?’;
$sql = “SELECT * FROM table WHERE column IN ($in)”;
$stm = $db->prepare($sql);
$stm->execute($ids);
$data = $stm->fetchAll();

Of course, if we have other variables to be bound, we need to add them to values array:

$ids = array(1,2,3);
$in = str_repeat(‘?,’, count($arr) – 1) . ‘?’;
$sql = “SELECT * FROM table WHERE column IN ($in) AND category=?”;
$stm = $db->prepare($sql);
$ids[] = $category; //adding another member to array
$stm->execute($ids);
$data = $stm->fetchAll();

the code become quite bloated but that’s all PDO can offer to handle such complex cases. As a further improvement one can invent their own placeholders to support such complex data types.

 

Better:

join(‘,’, array_fill(0, count($arr), ‘?’))

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s