用于字符串数组的 mysqli bind_param [英] mysqli bind_param for array of strings

问题描述

我无法让它工作.我现在已经花了很多时间.

I can not get this to work. I've spent way to many hours on it now.

这有效:

$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 can NOT get it 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 syntax correct.

我已经严格尝试了 moskito-x 的建议和此处列出的大量示例以及 SO 和随机网站上的其他位置，但没有任何效果.我认为问题可能是 PHP 5.4，这是我的 MAMP 现在设置的.

I've rigorously tried moskito-x's suggestions and tons of examples listed here and else where on SO and random websites, and nothing works. I think the issue might be PHP 5.4, which is what my MAMP is set to right now.

推荐答案

任务有点复杂但可行.我将从我的文章 Mysqli 准备好 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!

所以正确的示例代码是

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

这篇关于用于字符串数组的 mysqli bind_param的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！