Can I bind an array to an IN() condition in a PDO query?

user_113659 0
Viewed 237090

I'm curious to know if it's possible to bind an array of values to a placeholder using PDO. The use case here is attempting to pass an array of values for use with an IN() condition.

I'd like to be able to do something like this:

<?php
$ids=array(1,2,3,7,8,9);
$db = new PDO(...);
$stmt = $db->prepare(
    'SELECT *
     FROM table
     WHERE id IN(:an_array)'
);
$stmt->bindParam('an_array',$ids);
$stmt->execute();
?>

And have PDO bind and quote all the values in the array.

At the moment I'm doing:

<?php
$ids = array(1,2,3,7,8,9);
$db = new PDO(...);
foreach($ids as &$val)
    $val=$db->quote($val); //iterate through array and quote
$in = implode(',',$ids); //create comma separated list
$stmt = $db->prepare(
    'SELECT *
     FROM table
     WHERE id IN('.$in.')'
);
$stmt->execute();
?>

Which certainly does the job, but just wondering if there's a built in solution I'm missing?

where-in prepared-statement pdo arrays php
22 Answers

You'll have to construct the query-string.

<?php
$ids     = array(1, 2, 3, 7, 8, 9);
$inQuery = implode(',', array_fill(0, count($ids), '?'));

$db = new PDO(...);
$stmt = $db->prepare(
    'SELECT *
     FROM table
     WHERE id IN(' . $inQuery . ')'
);

// bindvalue is 1-indexed, so $k+1
foreach ($ids as $k => $id)
    $stmt->bindValue(($k+1), $id);

$stmt->execute();
?>

Both chris (comments) and somebodyisintrouble suggested that the foreach-loop ...

(...)
// bindvalue is 1-indexed, so $k+1
foreach ($ids as $k => $id)
    $stmt->bindValue(($k+1), $id);

$stmt->execute();

... might be redundant, so the foreach loop and the $stmt->execute could be replaced by just ...

<?php 
  (...)
  $stmt->execute($ids);
user_880700

Looking at PDO :Predefined Constants there is no PDO::PARAM_ARRAY which you would need as is listed on PDOStatement->bindParam

bool PDOStatement::bindParam ( mixed $parameter , mixed &$variable [, int $data_type [, int $length [, mixed $driver_options ]]] )

So I don't think it is achievable.

Admin1

A little editing about the code of Schnalle

<?php
$ids     = array(1, 2, 3, 7, 8, 9);
$inQuery = implode(',', array_fill(0, count($ids)-1, '?'));

$db   = new PDO(...);
$stmt = $db->prepare(
    'SELECT *
     FROM table
     WHERE id IN(' . $inQuery . ')'
);

foreach ($ids as $k => $id)
    $stmt->bindValue(($k+1), $id);

$stmt->execute();
?>

//implode(',', array_fill(0, count($ids)-1), '?')); 
//'?' this should be inside the array_fill
//$stmt->bindValue(($k+1), $in); 
// instead of $in, it should be $id
user_2761980

What database are you using? In PostgreSQL I like using ANY(array). So to reuse your example:

<?php
$ids=array(1,2,3,7,8,9);
$db = new PDO(...);
$stmt = $db->prepare(
    'SELECT *
     FROM table
     WHERE id = ANY (:an_array)'
);
$stmt->bindParam('an_array',$ids);
$stmt->execute();
?>

Unfortunately this is pretty non-portable.

On other databases you'll need to make up your own magic as others have been mentioning. You'll want to put that logic into a class/function to make it reusable throughout your program of course. Take a look at the comments on mysql_query page on PHP.NET for some more thoughts on the subject and examples of this scenario.

user_917400

With MySQL and PDO we can use a JSON array and JSON_CONTAINS() (https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-contains) to search in.

$ids = [123, 234, 345, 456]; // Array of users I search
$ids = json_encode($ids); // JSON conversion

$sql = <<<SQL
    SELECT ALL user_id, user_login
    FROM users
    -- Cast is mandatory beaucause JSON_CONTAINS() waits JSON doc candidate
    WHERE JSON_CONTAINS(:ids, CAST(user_id AS JSON))
    SQL;

$search = $pdo->prepare($sql);
$search->execute([':ids' => $ids]);
$users = $search->fetchAll();

Whe can also use JSON_TABLE() (https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html#function_json-table) for more complex cases and JSON data exploration :

$users = [
    ['id' => 123, 'bday' => ..., 'address' => ...],
    ['id' => 234, 'bday' => ..., 'address' => ...],
    ['id' => 345, 'bday' => ..., 'address' => ...],
]; // I'd like to know their login

$users = json_encode($users);

$sql = <<<SQL
    SELECT ALL user_id, user_login
    FROM users
    WHERE user_id IN (
        SELECT ALL user_id
        FROM JSON_TABLE(:users, '$[*]' COLUMNS (
            -- Data exploration...
            -- (if needed I can explore really deeply with NESTED kword)
            user_id INT PATH '$.id',
            -- I could skip these :
            user_bday DATE PATH '$.bday',
            user_address TINYTEXT PATH '$.address'
        )) AS _
    )
    SQL;

$search = $pdo->prepare($sql);
$search->execute([':users' => $users]);
...
user_102426500
Related