如何将 IN 子句与 mysqli 准备好的语句一起使用

发布于 2024-07-17 18:06:42 字数 591 浏览 13 评论 0原文

我正在使用准备好的语句将一些旧代码移至新的 msqli 接口，但我在使用包含 IN 子句的 SQL 语句时遇到了问题。我通常会这样做：

$ids = '123,535,345,567,878'
$sql = "SELECT * FROM table WHERE id IN ($ids)";
$res = mysql_query($sql);

将其转换为 mysqli 并准备好语句我尝试了多种解决方案：

$ids = '123,535,345,567,878'
$ids = implode($ids,',');
$result = $msqli->prepare("SELECT foo,blar FROM table WHERE id IN (?));
$result->bind_param("i", $ids);
$result->execute();

上面的方法失败并计算数组中的元素数量并更改 SQL 字符串中问号的数量并为每个元素调用 bind_parm在数组中也失败。仅使用逗号分隔的字符串也会失败。

我在谷歌上找不到这方面的好文档，那么你是如何解决这个问题的？

原文

I’m moving some old code over to the new msqli interface using prepared statements, I’m having trouble with SQL statements containing the IN clause. I would just normally do this:

$ids = '123,535,345,567,878'
$sql = "SELECT * FROM table WHERE id IN ($ids)";
$res = mysql_query($sql);

Converting this to mysqli and prepared statements I have tried a number of solutions:

$ids = '123,535,345,567,878'
$ids = implode($ids,',');
$result = $msqli->prepare("SELECT foo,blar FROM table WHERE id IN (?));
$result->bind_param("i", $ids);
$result->execute();

The above fails and calculating the number of elements in the array and altering number of question marks in the SQL string and calling bind_parm for each element in the array also fails. Just using the comma separated string also fails.

I can find no good documentation in Google on this, so how have you solved the problem?

分享到QQ

分享到微博

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

发布评论

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

愛放△進行李 2024-07-24 18:06:42

不可能将可变长度列表绑定到单个绑定变量。

同样，如果您要绑定字符串 $ids 您实际上最终会得到：（

SELECT foo,blar FROM table WHERE id IN ('123,535,345,567,878')

请注意 ID 列表周围的引号）。

使用正确数量的问号和绑定参数创建您自己的查询应该实际上有效 - 您可能需要再次尝试并报告实际错误。

或者，不幸的是，这可能是需要手工编写自己的 SQL 并且不使用绑定参数的情况之一。

It's not possible to bind a list of variable length to a single bound variable.

Similarly, if you were to bind the string $ids you'll actually end up with:

SELECT foo,blar FROM table WHERE id IN ('123,535,345,567,878')

(Note the quotes around the list of IDs).

Creating your own query with the right number of question marks and bound parameters should have actually worked - you may need to try that again and report on the actual error.

Alternatively, this may be one of those occasions where it's unfortunately necessary to hand-craft your own SQL and not use bound parameters.

回复收藏 0 原文

汹涌人海 2024-07-24 18:06:42

查看之前在这里提出的类似问题的答案（第二个代码示例）：

我有一个整数数组，如何在 mysql 查询（在 php 中）中使用每个整数？

归结为：

创建带有适量问号的 SQL 字符串，
使用 call_user_func_array() 将数组绑定到查询字符串

回复收藏 0 原文

嘦怹 2024-07-24 18:06:42

我认为准备好的陈述的要点是在这种情况下你可以这样做：

$stmt = $this->mysqli->prepare("UPDATE radcheck SET attribute = ?, value = ?  WHERE username = ? AND attribute LIKE 'CS-Total-Octets%'");
foreach ($usernames as $username)
{
    $stmt->bind_param('sss', $bandwidth_types[$bandwidth_type], $bandwidth_bytes, $username);
    $stmt->execute();
}
$stmt->close();

I thought the point of prepared statements was so in this situation you could just do:

$stmt = $this->mysqli->prepare("UPDATE radcheck SET attribute = ?, value = ?  WHERE username = ? AND attribute LIKE 'CS-Total-Octets%'");
foreach ($usernames as $username)
{
    $stmt->bind_param('sss', $bandwidth_types[$bandwidth_type], $bandwidth_bytes, $username);
    $stmt->execute();
}
$stmt->close();

回复收藏 0 原文

~没有更多了~