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.

推荐答案

这个问题经常发生，这个问题太老了，无法提供远程可靠的解决方案，但Google仍将访问者发送到这里.因此，这是2019年及以后的答案.

As it often happens, this question is too old to provide even a remotely reliable solution, yet Google still sends visitors here. So here is the answer for 2019 and beyond.

我将从文章 Mysqli准备的语句中为IN子句提供多个值的解释:

• 首先，我们将需要创建一个带有?标记的字符串，该字符串与数组中的元素数量一样多.为此，我们将使用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)"; 
$stmt  = $mysqli->prepare($sql);
$types = str_repeat('s', count($array)); 
$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 incomparable smaller than any other plausible solution offered in this topic so far.

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