Matok's PHP Blog

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

article image

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 cases1 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();

Footnotes:

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


If you like this article then mark it as helpful to let others know it's worth to read. Otherwise leave me a feedback/comment and we can talk about it.

I'm foreigner. Where I live my friends call me Maťok.


Comments - Coming Soon