无法将内爆数组绑定到mysql准备的语句中 [英] Trouble binding an imploded array into a mysql 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) . "')";

哪个可以正确地给我

('[e-mail]', '[e-mail]')

推荐答案

让我为您省些麻烦，并告诉您您要执行的操作将无法正常工作.您仅将一个参数绑定到您的IN()函数调用.您认为您正在传递一个逗号分隔的列表，但实际上您只是传递了一个逗号分隔的字符串，该字符串被视为一个值.这意味着您将搜索值为'[e-mail]'，'[e-mail]'"的一个记录，而不是与"[e-mail]"匹配的记录或"[e-mail]".

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 "'[e-mail]', '[e-mail]'" instead of records that match "[e-mail]" or "[e-mail]".

要克服这一点，您需要:

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()的错误，期望通过引用传递参数2:

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

这篇关于无法将内爆数组绑定到mysql准备的语句中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！