我可以将数组绑定到 PDO 查询中的 IN() 条件吗？

发布于 2024-07-21 05:20:15 字数 767 浏览 10 评论 0原文

我很好奇是否可以使用 PDO 将值数组绑定到占位符。这里的用例尝试传递一个值数组以与 IN() 条件一起使用。

我希望能够执行以下操作：

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

并让 PDO 绑定并引用数组中的所有值。

目前我正在做的事情是：

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

哪个确实可以完成这项工作，但只是想知道我是否缺少一个内置的解决方案？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

末が日狂欢 2024-07-28 05:20:15

您必须手动构建占位符列表，为每个数组成员添加一个占位符。

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

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

鉴于 $inQuery 不接受任何输入并且完全由常量值（?, 部分）构造，因此在查询中添加此类变量是安全的。

如果查询中还有其他占位符，您可以使用 array_merge() 函数将所有变量连接到一个数组中，以数组的形式添加其他变量，按照它们出现的顺序您的查询：

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

如果您使用命名占位符，代码会稍微复杂一些，因为您必须创建命名占位符的序列，例如 :id0,:id1,:id2。所以代码是：

// other parameters that are going into query
$params = ["foo" => "foo", "bar" => "bar"];

$ids = [1,2,3];
$in = "";
$i = 0; // we are using an external counter 
        // because the actual array keys could be dangerous
foreach ($ids as $item)
{
    $key = ":id".$i++;
    $in .= ($in ? "," : "") . $key; // :id0,:id1,:id2
    $in_params[$key] = $item; // collecting values into a key-value array
}

$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)); // just merge two arrays
$data = $stmt->fetchAll();

幸运的是，对于命名占位符，我们不必遵循严格的顺序，因此我们可以按任何顺序合并数组。

You'll have to construct the list of placeholders manually, adding a placeholder for each array member.

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

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

Given $inQuery doesn't take any input and fully constructed from constant values (?, parts), it's safe to add such a variable in the query.

In case there are other placeholders in the query, you could use array_merge() function to join all the variables into a single array, adding your other variables in the form of arrays, in the order they appear in your 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();

In case you are using named placeholders, the code would be a little more complex, as you have to create a sequence of the named placeholders, e.g. :id0,:id1,:id2. So the code would be:

// other parameters that are going into query
$params = ["foo" => "foo", "bar" => "bar"];

$ids = [1,2,3];
$in = "";
$i = 0; // we are using an external counter 
        // because the actual array keys could be dangerous
foreach ($ids as $item)
{
    $key = ":id".$i++;
    $in .= ($in ? "," : "") . $key; // :id0,:id1,:id2
    $in_params[$key] = $item; // collecting values into a key-value array
}

$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)); // just merge two arrays
$data = $stmt->fetchAll();

Luckily, for the named placeholders we don't have to follow the strict order, so we can merge our arrays in any order.

我可以将数组绑定到 PDO 查询中的 IN() 条件吗？

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（23）

关于作者

相关话题

热门标签

推荐作者

留蓝

18790681156

zach7772

Wini

ayeshaaroy

初雪

友情链接

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。