PDO: Use array in search criteria WHERE smt. IN ()

Matej Kuna aka Maťok 17 November 2017

When you are working with PDO you may ask the question: How to bind an array into your query? There are at least 3 possibilities. In all cases¹ you must handle an empty array and avoid query:

SELECT * FROM person WHERE person_id IN ()

because this is not valid. I omit this from my examples. Let's presume we are working with set of integers.

Option 1: Straightforward way

$sql = 'SELECT * FROM persons WHERE person_id IN ('.$this->implodeInt($ids).')';

implodeInt method should be something like this to avoid SQL injection

public function implodeInt($ints)
{
     $result = array();
     foreach ($ints as $int) {
         $result[] = (int) $int;
     }

     return $result;
}

Option 2: Pass array to execute

$inParam = str_repeat('?, ', count($ints) - 1).'?';
$sql = 'SELECT article_id, title FROM article WHERE article_id IN ('.$inParam.')';
$statement = $this->connection->prepare($sql);

$statement->execute($ints);

return $statement->fetchAll();

Option 3: Doctrine support

$sql = 'SELECT article_id, title FROM article WHERE article_id IN (?)';
$statement = $this->connection->executeQuery($sql, [$ints],  [Connection::PARAM_INT_ARRAY]);

return $statement->fetchAll();

1: Last example with Doctrine is probably working however query is not valid and cannot be run on mysql server... kind of Doctrine magic