php 用于字符串数组的 mysqli bind_param

mysqli bind_param for array of strings

提问by Mark

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

我不能让它工作。我现在已经花了很多时间。

This works:

这有效：

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

But this I can NOT get it to work with a bind_param like this:

但这我无法让它与这样的 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();

What am I doing wrong?

我究竟做错了什么？

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

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

EDIT:
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.

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

回答by Your Common Sense

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.

正如经常发生的那样，这个问题太老了，甚至无法提供远程可靠的解决方案，但谷歌仍然将访问者发送到这里。所以这是 2019 年及以后的答案。

I'll take the explanation from my article Mysqli prepared statement with multiple values for IN clause:

我将从我的文章Mysqli Prepared statement with multiple values for IN 子句中获取解释：

• 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 operatorthat 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!

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

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)"; 
$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.

尽管此代码相当大，但与本主题迄今为止提供的任何其他合理解决方案相比，它是无与伦比的。

回答by moskito-x

You can not bind two variables with one question mark!

你不能用一个绑定两个变量question mark！

For every variable you bind you need one question mark

对于您绑定的每个变量，您都需要一个 question mark

"bind_param" checks each variable whether it matches the requirements. afterwards the string value is placed between quotes.

“bind_param” 检查每个变量是否符合要求。然后将字符串值放在引号之间。

This will not work.

这是行不通的。

"SELECT name FROM table WHERE city IN (?)"; ( becomes too )
$q_prepared->bind_param("s", $cities);
"SELECT name FROM table WHERE city IN ('city1,city2,city3,city4')";

must be.

必须是。

"SELECT name FROM table WHERE city IN (?,?,?,?)"; ( becomes too )
$q_prepared->bind_param("ssss", $city1,$city2,$city3,$city4);
"SELECT name FROM table WHERE city IN ('city1','city2','city3','city4')";

$query_prepared->bind_paramquotes string params one by one.
And the number of variables and length of string types must match the parameters in the statement.

$query_prepared->bind_param一一引用字符串参数。
并且变量的数量和字符串类型的长度必须与语句中的参数匹配。

$query_str= "SELECT name FROM table WHERE city IN ('Nashville','Knoxville')";

will become

会变成

$query_str= "SELECT name FROM table WHERE city IN (?,?)";

now bind_parammust be

现在bind_param必须是

bind_param("ss",$arg1,$arg2)

with this

有了这个

$query_str= "SELECT name FROM table WHERE city IN (?)";

and bind_paramwith

并bind_param与

bind_param("s",$cities)

you get

你得到

$query_str= "SELECT name FROM table WHERE city IN ('Nashville,Knoxville')";

That's why an array not works .
The only solution for this fact is call_user_func_array

这就是数组不起作用的原因。
这个事实的唯一解决方案是call_user_func_array

if you init a statement, following is unnecessary

如果你初始化一个语句，下面是不必要的

$query_prepared = $mysqli->stmt_init();
if($query_prepared && $query_prepared->prepare($query_str)) {

This is correct

这是对的

$query_prepared = $mysqli->stmt_init();
if($query_prepared->prepare($query_str)) {

if you don't want to use call_user_func_array
and you have only a small count of arguments
you can do it with the following code.

如果您不想使用call_user_func_array
并且只有少量参数
，则可以使用以下代码进行操作。

[...]
$cities= explode(",", $_GET['cities']);
if (count($cities)>3) { echo "too many arguments"; }
else
{ 
$count = count($cities); 
$SetIn = "(";
  for($i = 0; $i < $count; ++$i) {    
      $code.='s';
      if ($i>0) {$SetIn.=",?";} else {$SetIn.="?";}
  }
$SetIn.=")";
$query_str= "SELECT name FROM table WHERE city IN ".$SetIn;
// with 2 arguments $query_str will look like
// SELECT name FROM table WHERE city IN (?,?)
$query_prepared = $mysqli->stmt_init();
if($query_prepared->prepare($query_str))
  {       
    if ($count==1) { $query_prepared->bind_param($code, $cities[0]);}
    if ($count==2) { $query_prepared->bind_param($code, $cities[0],$cities[1]);}
    if ($count==3) { $query_prepared->bind_param($code, $cities[0],$cities[1],$cities[2]);
    // with 2 arguments $query_prepared->bind_param() will look like
    // $query_prepared->bind_param("ss",$cities[0],$cities[1])      
  }    

    $query_prepared->execute();
  } 
 [...]
 }

I would suggest you try it with call_user_func_arrayto reach.

我建议你尝试使用它call_user_func_array来达到。

look for the solution of nick9v
mysqli-stmt.bind-param

寻找mysqli-stmt.bind-param的解决方案nick9v

回答by SingSong

use call_user_func_array like this:

像这样使用 call_user_func_array ：

$stmt = $mysqli->prepare("INSERT INTO t_file_result VALUES(?,?,?,?)");

$id = '1111';
$type = 2;
$result = 1;
$path = '/root';

$param = array('siis', &$id, &$type, &$result, &$path);
call_user_func_array(array($stmt, 'bind_param'), $param);

$stmt->execute();

printf("%d row inserted. \n", $stmt->effected_rows);
$stmt->close;

回答by WoodrowShigeru

The way I did it: prepare the query with all its separate question marks, as well as the type string.

我这样做的方式是：用所有单独的问号以及类型字符串准备查询。

$cities = array('Nashville','Knoxville');
$dibs = '';
$query = "SELECT name FROM table WHERE city IN (";
$marks = array();

foreach ($cities as $k => $city) {
    // i,s,b,d type based on the variables to bind.
    $dibs .= 's';
    array_push($marks, '?');
}

$query .= implode(',', $marks) .')';

Connect.

连接。

$mysql = new mysqli($host, $user, $pass, $dbname);
$statement =
    $mysql->prepare($query)
OR  die(sprintf(
        'Query error (%s) %s', $mysql->errno, $mysql->error
    ))
;

Then you use "..." token / ellipsis (documentation) in order to bind the array.

然后使用“...”标记/省略号（文档）来绑定数组。

if ($statement) {
    $statement->bind_param($dibs, ...$cities);
    $statement->execute();

    $statement->close();
}
$mysql->close();

I know it kinda defeats the purpose of binding in order to escape (but at least it works good with a list of integers, i.e. IDs). If you see a way how to improve this approach, feel free to edit/comment.

我知道它有点违背了绑定的目的以逃避（但至少它适用于整数列表，即 ID）。如果您看到如何改进此方法的方法，请随时编辑/评论。

回答by mickadoo

I was having trouble with this too, and got it working with evalbefore finding out that most people are using call_user_func_array

我也遇到了这个问题，eval在发现大多数人都在使用之前就开始使用它call_user_func_array

$fields = array('model','title','price'); // fields in WHERE clause
$values = array( // type and value for each field
    array('s','ABCD-1001'),
    array('s','[CD] Test Title'),
    array('d','16.00')
);
$sql = "SELECT * FROM products_info WHERE "; // start of query
foreach ($fields as $current){ // build where clause from fields
    $sql .= '`' . $current . '` = ? AND ';
}
$sql = rtrim($sql,'AND '); // remove last AND 
$stmt = $db->prepare($sql);
$types = ''; $vals = '';
foreach ($values as $index => $current_val){ // build type string and parameters
    $types .= $current_val[0];
    $vals .= '$values[' . $index . '][1],';
}
$vals = rtrim($vals,','); // remove last comma
$sql_stmt = '$stmt->bind_param("' . $types . '",' . $vals . ');'; // put bind_param line together
eval($sql_stmt); // execute bind_param
$stmt->execute();
$stmt->bind_result($col1,$col2,$col3,$col4,$col5,$col6); // this could probably also be done dynamically in the same way
while ($stmt->fetch()){
    printf("%s %s %s %s %s %s\n", $col1,$col2,$col3,$col4,$col5,$col6);
}

回答by Jennifer

This is what I did after naming the form inputs the same as the mysql column names.

这是我在将表单输入命名为与 mysql 列名称相同后所做的。

$post_fields = array_keys($_POST);
$post_values = array_values($_POST);

$fields_type_i = array("age","age_share","gender_share");  // all mysql col names type int

$fields = "";           // user input fields
$values = "";           // user input vals
$placeholders = "";     // ?,?,?
$params_type = "";      // s=string i=integer

foreach ($post_fields as $field) {
    $fields .= "`".$field."`,"; 
}

---

---

for ($i=0;$i<count($post_fields);$i++) {      // bind i and s param types
    $placeholders .= "?,";
    if (in_array($post_fields[$i],$fields_type_i)) {        
        $params_type .= "i";    
    } else {                                            
        $params_type .= "s";
    }
    $values .= $post_values[$i];
}

OR

或者

for ($i=0;$i<count($post_fields);$i++) {        // binding only s param type
    if (in_array($post_fields[$i],$fields_type_i)) {
        $placeholders .= $post_values[$i].",";  
    } else {
        $placeholders .= "?,";
        $params_type .= "s";
        $values .= $post_values[$i];
    }
}

---

---

$fields = rtrim($fields,",");  // removing last commas
$values = rtrim($values,",");
$placeholders = rtrim($placeholders,",");

$params_string = $params_type.','.$values;
$params_vals = explode(",",$params_string);  // array of vals

$params_refs = array();
foreach($params_vals as $key => $value) $params_refs[$key] = &$params_vals[$key];  // array of refs

$stmt = $mysqli -> prepare('INSERT INTO pets ('.$fields.') VALUES ('.$placeholders.')');

if ($stmt && call_user_func_array(array($stmt, 'bind_param'), $params_refs) && $stmt -> execute()) {
    echo 'Success';
} else {
    echo $stmt -> error;
}