Why Does PDO Return Unexpected Results When Using an IN Clause with an Array of Placeholders?

Using PDO with IN Clause Array [duplicate]

Question:

When using PDO to execute a statement with an IN clause that uses an array for its values, why does this code return an unexpected result:

$in_array = array(1, 2, 3);
$in_values = implode(',', $in_array);
$my_result = $wbdb->prepare("SELECT * FROM my_table WHERE my_value IN (:in_values)");
$my_result->execute(array(':in_values' => $in_values));

Answer:

PDO has difficulty processing IN clauses that use placeholders for an array of values. To resolve this issue, you need to create a string of placeholders dynamically and insert it into the query, while binding the array values separately.

For positional placeholders:

$in  = str_repeat('?,', count($in_array) - 1) . '?';
$sql = "SELECT * FROM my_table WHERE my_value IN ($in)";
$stm = $db->prepare($sql);
$stm->execute($in_array);

For named placeholders:

// collect parameters for query
$params = ["foo" => "foo", "bar" => "bar"];

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

// construct query and execute
$sql = "SELECT * FROM table WHERE foo=:foo AND id IN ($in) AND bar=:bar";
$stm = $db->prepare($sql);
$stm->execute(array_merge($params, $in_params));

The above is the detailed content of Why Does PDO Return Unexpected Results When Using an IN Clause with an Array of Placeholders?. For more information, please follow other related articles on the PHP Chinese website!