在 PDO 准备好的语句中将值传递给 MySQL IN 操作？

发布于 2024-10-12 19:56:59 字数 305 浏览 2 评论 0原文

我有一个表单字段，它返回一个逗号分隔的字符串，我想将其传递给 PHP PDO MySQL 查询 IN 操作，但 IN 操作要求值以逗号分隔（而不是我的 分隔值的字符串）。

我该怎么做？

$values = $_POST['values']; # '10,5,4,3' (string)
$query = "SELECT * FROM table WHERE id IN (:values)";
$data = array( ':values' => $values );

原文

I have a form field that is returning a comma-delimited string that I want to pass in to a PHP PDO MySQL query IN operation, but the IN operation requires that the values be comma-delimited (as opposed to my string of delimited values).

How do I do this?

$values = $_POST['values']; # '10,5,4,3' (string)
$query = "SELECT * FROM table WHERE id IN (:values)";
$data = array( ':values' => $values );

分享到QQ

分享到微博

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

发布评论

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

烟燃烟灭 2024-10-19 19:56:59

您不能在单个占位符中传递多个值。您必须为要传递到 IN () 的每个值输入不同的占位符。由于您不知道会有多少个，因此请使用 ? 而不是命名参数。

$values = explode(',', $values) ;

$placeholders = rtrim(str_repeat('?, ', count($values)), ', ') ;
$query = "SELECT * FROM table WHERE id IN ($placeholders)";

$stm = $db->prepare($query) ;
$stm->execute($values) ;

You can't pass in multiple values in a single placeholder. You will have to enter a different placeholder for each value to be passed into IN (). Since you don't know how many there will be, use ? instead of named parameters.

$values = explode(',', $values) ;

$placeholders = rtrim(str_repeat('?, ', count($values)), ', ') ;
$query = "SELECT * FROM table WHERE id IN ($placeholders)";

$stm = $db->prepare($query) ;
$stm->execute($values) ;

回复收藏 0 原文

向地狱狂奔 2024-10-19 19:56:59

仅 PDO 无法将数组绑定到 :parameter。为此，您需要一个辅助函数。

同样在您的示例中，文字字符串 '10,5,4,3' 将作为一个值接收。参数绑定会将其转换为 .. id IN ('10,5,4,3') 而不是列表比较。

您的情况的解决方法是使用动态 SQL 和转义。

$values = preg_replace('/[^\d,]/', "", $_POST['values']);
$query = "SELECT * FROM table WHERE id IN ($values)";

我个人使用包装器/帮助器 function 它有一个特殊的数组语法（但你实际上没有一个开始，所以这将是一个双重解决方法）：

db("SELECT * FROM table WHERE id IN (??)", explode(",",$values));

PDO alone cannot bind arrays to a :parameter. You need a helper function for that.

Also in your example, the literal string '10,5,4,3' would be received as one value. Parameter binding will turn it into .. id IN ('10,5,4,3') and not into a list comparison.

The workaround in your case would be to fall back on using dynamic SQL and escaping.

$values = preg_replace('/[^\d,]/', "", $_POST['values']);
$query = "SELECT * FROM table WHERE id IN ($values)";

I'm personally using a wrapper/helper function which has a special syntax for arrays (but you don't actually have one to begin with, so it would be a double workaround):

db("SELECT * FROM table WHERE id IN (??)", explode(",",$values));

回复收藏 0 原文

酒中人 2024-10-19 19:56:59

诀窍是认识到 $values 是一堆单独的值，并在设置查询时考虑到这一点。如果您使用 ? 占位符而不是命名占位符，这会更容易做到。例如，您可以执行如下操作：

$values = explode(',', $_POST['values']); //array(10,5,4,3)
$placeholder_string = implode(',', array_fill(0, count($values), '?')); // string '?,?,?,?'
$query = "SELECT * FROM table WHERE id IN ($placeholder_string)";

$statement = $db->prepare($query);
$statement->execute($values);

The trick is to recognize that $values is a bunch of individual values, and set up your query with this in mind. This is easier to do if you use ? placeholders instead of named placeholders. For example, you could do something like the following:

$values = explode(',', $_POST['values']); //array(10,5,4,3)
$placeholder_string = implode(',', array_fill(0, count($values), '?')); // string '?,?,?,?'
$query = "SELECT * FROM table WHERE id IN ($placeholder_string)";

$statement = $db->prepare($query);
$statement->execute($values);

回复收藏 0 原文

~没有更多了~