Can PDO Handle Array Binding for IN() Clause Queries?

Can Binding an Array to an IN() Condition be Done with PDO?

Utilizing arrays in PDO queries for IN() conditions can be achieved by manually constructing the placeholders for each array member. Here's a breakdown of the process:

$ids       = [1, 2, 3, 7, 8, 9];
$inQuery    = str_repeat('?,', count($ids) - 1) . '?'; // generates ?,?,?,?,?,?

$stmt = $db->prepare("SELECT * FROM table WHERE id IN($inQuery)");
$stmt->execute($ids);
$data = $stmt->fetchAll();

Construct the $inQuery variable using str_repeat to create a list of placeholders. This ensures each array member has a corresponding placeholder.

Another approach is to merge arrays containing placeholders and their associated values. This method is suitable when other placeholders exist within the query.

$arr = [1,2,3];
$in  = str_repeat('?,', count($arr) - 1) . '?';
$sql = "SELECT * FROM table WHERE foo=? AND column IN ($in) AND bar=? AND baz=?";
$stmt = $db->prepare($sql);
$params = array_merge([$foo], $arr, [$bar, $baz]);
$stmt->execute($params);
$data = $stmt->fetchAll();

Named placeholders require a slightly different approach, where you create a corresponding placeholder sequence, such as :id0,:id1,:id2.

$ids = [1,2,3];
$in = "";
$i = 0;
foreach ($ids as $item) {
    $key = ":id" . $i++;
    $in .= ($in ? "," : "") . $key;
    $in_params[$key] = $item;
}

$sql = "SELECT * FROM table WHERE foo=:foo AND id IN ($in) AND bar=:bar";
$stmt = $db->prepare($sql);
$stmt->execute(array_merge($params, $in_params));
$data = $stmt->fetchAll();

These methods provide a secure and efficient way to use arrays in IN() conditions with PDO.

The above is the detailed content of Can PDO Handle Array Binding for IN() Clause Queries?. For more information, please follow other related articles on the PHP Chinese website!