无法将内爆数组绑定到 mysql 准备好的语句中

2021-12-25 00:00:00 php mysql mysqli prepared-statement

我对以下语法错误感到头疼.我正在尝试将内爆数组绑定到准备好的语句中，但出现以下语法错误:

I am beating my head over the below syntax error. I am trying to bind an imploded array into a prepared statement, but I am getting the following syntax error:

您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在?"附近使用的正确语法.在第 1 行

这是我的代码.谁能看出我哪里出错了?

Here is my code. Can anyone see where I am going wrong?

<?php 
include('config.php');

$selected = $_POST['selected'];

if ($stmt = $mysqli->prepare("DELETE FROM email_addresses WHERE email_addresses IN ?")) {

    $stmt->bind_param("s", "('" . implode("', '", $selected) . "')" );

    $stmt->execute();

    $stmt->close();

    print "ok";

} else {
    print $mysqli->error;
}

$mysqli->close();

?>

作为测试，我尝试了:

print "('" . implode("', '", $selected) . "')";

哪个正确地给了我

('me@me.com', 'you@you.com')

推荐答案

让我为您省去一些麻烦并告诉您您尝试做的事情无论如何都行不通.您只将一个参数绑定到您的 IN() 函数调用.您认为您正在传递一个逗号分隔的列表，但实际上您只传递了一个逗号分隔的字符串，该字符串被视为一个值.这意味着您将搜索具有'me@me.com'、'you@you.com'"值的一条记录，而不是与me@me.com"匹配的记录或you@you.com".

Let me save you some trouble and tell you what you're trying to do won't work anyway. You are only binding one parameter to your IN() function call. You think you're passing a comma separated list but you are actually only passing a comma separated string which is treated as one value. This means you will be search for one record with a value of "'me@me.com', 'you@you.com'" instead of records that match "me@me.com" or "you@you.com".

要克服这个问题，您需要:

To overcome this you need to:

  1. 动态生成类型字符串
  2. 使用call_user_func_array()绑定你的参数

您可以像这样生成类型字符串:

You can generate the types string like this:

$types = str_repeat('s', count($selected));

所有这些都是创建一个 s 的字符串，它的字符数与数组中的元素数一样多.

All this does is create a string of s's that is as many characters as the number of elements in the array.

然后你可以像这样使用 call_user_func_array() 绑定你的参数(注意我把括号放回 IN() 函数):

You would then bind your parameters using call_user_func_array() like this (notice I put the parenthesis back in for the IN() function):

if ($stmt = $mysqli->prepare("DELETE FROM email_addresses WHERE email_addresses IN (?)")) {
    call_user_func_array(array($stmt, "bind_param"), array_merge($types, $selected));

但是如果你尝试这个你会得到一个关于 mysqli_stmt::bind_param() 期望参数二通过引用传递的错误:

But if you try this you will get an error about mysqli_stmt::bind_param() expecting parameter two to be passed by reference:

警告:mysqli_stmt::bind_param() 的参数 2 应为引用，已给定值

Warning: Parameter 2 to mysqli_stmt::bind_param() expected to be a reference, value given

这有点烦人，但很容易解决.要解决此问题，您可以使用以下函数:

This is kind of annoying but easy enough to work around. To work around that you can use the following function:

function refValues($arr){ 
    $refs = array(); 
    foreach($arr as $key => $value) 
        $refs[$key] = &$arr[$key]; 
    return $refs; 
}

它只是创建一个值数组，这些值是对 $selected 数组中值的引用.这足以让 mysqli_stmt::bind_param() 开心:

It just creates an array of values that are references to the values in the $selected array. This is enough to make mysqli_stmt::bind_param() happy:

if ($stmt = $mysqli->prepare("DELETE FROM email_addresses WHERE email_addresses IN (?)")) {
    call_user_func_array(array($stmt, "bind_param"), array_merge($types, refValues($selected)));

编辑

从 PHP 5.6 开始，您现在可以使用 ... 运算符来使这更简单:

As of PHP 5.6 you can now use the ... operator to make this even simpler:

$stmt->bind_param($types, ...$selected);

相关文章