将多个参数绑定到mysqli查询中 [英] Bind multiple parameters into mysqli query

问题描述

现在，我需要使用以下结构来将多个参数绑定到mysqli查询中:

Right now I need to use the following structure to cope with binding multiple parameters into a mysqli query:

if ($words_total == 1)
{
    $statement -> bind_param("s", $words[0]);
}
else if ($words_total == 2)
{
    $statement -> bind_param("ss", $words[0], $words[1]);
}
else if ($words_total == 3)
{
    $statement -> bind_param("sss", $words[0], $words[1], $words[2]);
}

//and so on....

我使用下面的代码计算出问号的数量，并将其插入我的查询中:

I work out the number of question marks using the code below and insert it into my query:

$marks = "";
for($i = 1; $i<=$words_total; $i++) {
    if ($i == $words_total)
    {
        $marks .= "?";
    }
    else
    {
        $marks .= "?,";
    }
}

我的问题是，肯定有一种方法可以动态地处理查询中尽可能多的输入.硬编码bind_param()似乎是处理此问题的一种非常糟糕的方法.

My question is surely there must be a way of handling as many inputs into the query as I need dynamically. Hardcoding the bind_param() seems like a really bad way of handling this.

我正在使用php版本5.4.10

I am using php version 5.4.10

推荐答案

不幸的是，默认情况下，bind_param()不接受数组而不是单独的变量.但是，自PHP 5.6以来，已有重大改进，可以解决问题.

Unfortunately, by default, bind_param() doesn't accept an array instead of separate variables. However, since PHP 5.6 there is a magnificent improvement that will do the trick.

要将任意数量的变量绑定到mysqli查询中，您将需要

To bind an arbitrary number of variables into mysqli query you will need an argument unpacking operator. It will make the operation as simple and smooth as possible.

例如，要将PHP数组与mysql的IN()运算符一起使用，您将需要以下代码

For example, to use a PHP array with a mysql's IN() operator, you will need the following code

// our array
$words = ['a','b','c']; 

// create an SQL query with placeholders and prepare it
$in    = str_repeat('?,', count($array) - 1) . '?'; //  returns ?,?,?...
$sql   = "SELECT name FROM table WHERE city IN ($in)"; 
$stmt  = $mysqli->prepare($sql);

// create the types string dynamically and bind an array
$types = str_repeat('s', count($array)); // returns sss...
$stmt->bind_param($types, ...$array); 

// execute and fetch the rows
$stmt->execute();
$result = $stmt->get_result(); // get the mysqli result
$data = $result->fetch_all(MYSQLI_ASSOC); // fetch the data   

这篇关于将多个参数绑定到mysqli查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！