php - 如何使用 mysqli 准备语句绑定(bind)字符串数组？

Question

我需要将一组值绑定(bind)到 WHERE IN(?) 子句。我该怎么做？

这个有效:

$mysqli = new mysqli("localhost", "root", "root", "db");
if(!$mysqli || $mysqli->connect_errno)
{
    return;
}
$query_str= "SELECT name FROM table WHERE city IN ('Nashville','Knoxville')";
$query_prepared = $mysqli->stmt_init();
if($query_prepared && $query_prepared->prepare($query_str))
{       
    $query_prepared->execute();

但是我无法像这样使用 bind_param:

$query_str= "SELECT name FROM table WHERE city IN (?)";
$query_prepared = $mysqli->stmt_init();
if($query_prepared && $query_prepared->prepare($query_str))
{       
    $cities= explode(",", $_GET['cities']);
    $str_get_cities=  "'".implode("','", $get_cities)."'"; // This equals 'Nashville','Knoxville'

    $query_prepared->bind_param("s", $cities);
    $query_prepared->execute();

我做错了什么？

我也尝试过 call_user_func_array，但似乎无法获得正确的语法。

Answer 1

从 PHP 8.1 开始，您可以直接传递一个数组来执行:

$sql = "INSERT INTO users (email, password) VALUES (?,?)"; // sql
$stmt = $mysqli->prepare($sql); // prepare
$stmt->execute([$email, $password]); // execute with data! 

对于早期版本，任务有点复杂但可行。对于一个简单的情况，当您已经有一个带有占位符的查询时，代码将是

$sql = "INSERT INTO users (email, password) VALUES (?,?)"; // sql
$data = [$email, $password]; // put your data into array
$stmt = $mysqli->prepare($sql); // prepare
$stmt->bind_param(str_repeat('s', count($data)), ...$data); // bind array at once
$stmt->execute();

虽然，就像您的情况一样，我们有任意数量的占位符，但我们将不得不添加更多代码。我将从我的文章中获取解释 Mysqli prepared statement with multiple values for IN clause :

• First of all we will need to create a string with as many ? marks as many elements are in your array. For this we would use str_repeat() function which comes very handy for the purpose.
• Then this string with comma separated question marks have to be added to the query. Although it's a variable, in this case it is safe as it contains only constant values
• then this query must be prepared just like any other query
• then we will need to create a string with types to be used with bind_param(). Note that there is usually no reason to use different types for the bound variables - mysql will happily accept them all as strings. There are edge cases, but extremely rare. For the everyday use you can always keep it simple and use "s" for the everything. str_repeat() is again to the rescue.
• then we need to bind our array values to the statement. Unfortunately, you cannot just write it as a single variable, like this $stmt->bind_param("s", $array), only scalar variables are allowed in bind_param(). Luckily, there is an argument unpacking operator that does exactly what we need - sends an array of values into a function as though it's a set of distinct variables!
• the rest is as usual - execute the query, get the result and fetch your data!

所以正确的示例代码应该是

$array = ['Nashville','Knoxville']; // our array
$in    = str_repeat('?,', count($array) - 1) . '?'; // placeholders
$sql   = "SELECT name FROM table WHERE city IN ($in)"; // sql
$stmt  = $mysqli->prepare($sql); // prepare
$types = str_repeat('s', count($array)); //types
$stmt->bind_param($types, ...$array); // bind array at once
$stmt->execute();
$result = $stmt->get_result(); // get the mysqli result
$data = $result->fetch_all(MYSQLI_ASSOC); // fetch the data   

虽然这段代码相当大，但它比迄今为止本主题中提供的任何其他合理的解决方案小得无法比拟。