Why Does PDO Return Unexpected Results When Using an IN Clause with an Array of Placeholders?-PHP Tutorial-php.cn

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

Barbara Streisand

Release： 2024-12-21 08:31:13

Original

948 people have browsed it

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

Copy after login

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

Copy after login

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

Copy after login

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!