mysqli bind_param 用于字符串数组

2022-01-30 00:00:00 php mysqli bindparam

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

I need to bind an array of values to WHERE IN(?) clause. How can I do that?

这行得通:

$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:

But this I cannot get to work with a bind_param like this:

$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，但似乎无法获得正确的语法.

I've also tried call_user_func_array, but can't seem to get the correct syntax.

推荐答案

这个任务有点复杂但可行.我将从我的文章 Mysqli Prepared statement with multiple values for IN 子句中获取解释:

The task is a bit elaborate but doable. I'll take the explanation from my article Mysqli prepared statement with multiple values for IN clause:

  • 首先，我们需要创建一个字符串，其中包含与数组中的元素一样多的 ? 标记.为此，我们将使用 str_repeat() 函数，该函数非常方便.
  • 然后这个用逗号分隔问号的字符串必须添加到查询中.虽然它是一个变量，但在这种情况下它是安全的，因为它只包含常量值
  • 那么这个查询必须像任何其他查询一样准备好
  • 然后我们需要创建一个字符串，其中包含要与 bind_param() 一起使用的类型.请注意，通常没有理由为绑定变量使用不同的类型 - mysql 很乐意将它们全部作为字符串接受.有边缘情况，但极为罕见.对于日常使用，您始终可以保持简单并使用s"；为一切.str_repeat() 又来了.
  • 然后我们需要将我们的数组值绑定到语句.不幸的是，你不能把它写成一个单一的变量，像这样 $stmt->bind_param("s", $array)，bind_param().幸运的是，有一个 参数解包操作符 这正是我们需要的——将一组值发送到函数中，就好像它是一组不同的变量一样！
  • 其余的和往常一样 - 执行查询，获取结果并获取您的数据！
  • 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!

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

So the correct example code would be

$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

虽然这段代码相当大，但它比目前本主题中提供的任何其他合理解决方案都小得多.

Although this code is rather big, it is incomparably smaller than any other plausible solution offered in this topic so far.

相关文章