如何在 PHP 中动态绑定 mysqli bind_param 参数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5100046/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to bind mysqli bind_param arguments dynamically in PHP?
提问by laukok
I have been learning to use prepared and bound statements for my sql queries, and I have come out with this so far, it works ok but it is not dynamic at all when comes to multiple parameters or when there no parameter needed,
我一直在学习为我的 sql 查询使用准备好的和绑定的语句，到目前为止我已经提出了这个，它可以正常工作，但是当涉及多个参数或不需要参数时，它根本不是动态的，
public function get_result($sql,$parameter)
{
# create a prepared statement
$stmt = $this->mysqli->prepare($sql);
# bind parameters for markers
# but this is not dynamic enough...
$stmt->bind_param("s", $parameter);
# execute query
$stmt->execute();
# these lines of code below return one dimentional array, similar to mysqli::fetch_assoc()
$meta = $stmt->result_metadata();
while ($field = $meta->fetch_field()) {
$var = $field->name;
$$var = null;
$parameters[$field->name] = &$$var;
}
call_user_func_array(array($stmt, 'bind_result'), $parameters);
while($stmt->fetch())
{
return $parameters;
//print_r($parameters);
}
# close statement
$stmt->close();
}
This is how I call the object classes,
这就是我调用对象类的方式，
$mysqli = new database(DB_HOST,DB_USER,DB_PASS,DB_NAME);
$output = new search($mysqli);
Sometimes I don't need to pass in any parameters,
有时我不需要传入任何参数，
$sql = "
SELECT *
FROM root_contacts_cfm
";
print_r($output->get_result($sql));
Sometimes I need only one parameters,
有时我只需要一个参数，
$sql = "
SELECT *
FROM root_contacts_cfm
WHERE root_contacts_cfm.cnt_id = ?
ORDER BY cnt_id DESC
";
print_r($output->get_result($sql,'1'));
Sometimes I need only more than one parameters,
有时我只需要一个以上的参数，
$sql = "
SELECT *
FROM root_contacts_cfm
WHERE root_contacts_cfm.cnt_id = ?
AND root_contacts_cfm.cnt_firstname = ?
ORDER BY cnt_id DESC
";
print_r($output->get_result($sql,'1','Tk'));
So, I believe that this line is not dynamic enough for the dynamic tasks above,
所以，我相信这条线对于上面的动态任务来说不够动态，
$stmt->bind_param("s", $parameter);
To build a bind_param dynamically, I have found this on other posts online.
为了动态构建 bind_param，我在其他在线帖子中找到了这个。
call_user_func_array(array(&$stmt, 'bind_params'), $array_of_params);
And I tried to modify some code from php.netbut I am getting nowhere,
我试图从php.net修改一些代码，但我无处可去，
if (strnatcmp(phpversion(),'5.3') >= 0) //Reference is required for PHP 5.3+
{
$refs = array();
foreach($arr as $key => $value)
$array_of_param[$key] = &$arr[$key];
call_user_func_array(array(&$stmt, 'bind_params'), $array_of_params);
}
Why? Any ideas how I can make it work?
为什么？有什么想法可以让它发挥作用吗？
Or maybe there are better solutions?
或者也许有更好的解决方案？
采纳答案by laukok
found the answer for mysqli:
找到了 mysqli 的答案：
public function get_result($sql,$types = null,$params = null)
{
# create a prepared statement
$stmt = $this->mysqli->prepare($sql);
# bind parameters for markers
# but this is not dynamic enough...
//$stmt->bind_param("s", $parameter);
if($types&&$params)
{
$bind_names[] = $types;
for ($i=0; $i<count($params);$i++)
{
$bind_name = 'bind' . $i;
$$bind_name = $params[$i];
$bind_names[] = &$$bind_name;
}
$return = call_user_func_array(array($stmt,'bind_param'),$bind_names);
}
# execute query
$stmt->execute();
# these lines of code below return one dimentional array, similar to mysqli::fetch_assoc()
$meta = $stmt->result_metadata();
while ($field = $meta->fetch_field()) {
$var = $field->name;
$$var = null;
$parameters[$field->name] = &$$var;
}
call_user_func_array(array($stmt, 'bind_result'), $parameters);
while($stmt->fetch())
{
return $parameters;
//print_r($parameters);
}
# the commented lines below will return values but not arrays
# bind result variables
//$stmt->bind_result($id);
# fetch value
//$stmt->fetch();
# return the value
//return $id;
# close statement
$stmt->close();
}
then:
然后：
$mysqli = new database(DB_HOST,DB_USER,DB_PASS,DB_NAME);
$output = new search($mysqli);
$sql = "
SELECT *
FROM root_contacts_cfm
ORDER BY cnt_id DESC
";
print_r($output->get_result($sql));
$sql = "
SELECT *
FROM root_contacts_cfm
WHERE root_contacts_cfm.cnt_id = ?
ORDER BY cnt_id DESC
";
print_r($output->get_result($sql,'s',array('1')));
$sql = "
SELECT *
FROM root_contacts_cfm
WHERE root_contacts_cfm.cnt_id = ?
AND root_contacts_cfm.cnt_firstname = ?
ORDER BY cnt_id DESC
";
print_r($output->get_result($sql, 'ss',array('1','Tk')));
mysqli is so lame when comes to this. I think I should be migrating to PDO!
mysqli 在这方面太蹩脚了。我想我应该迁移到 PDO！
回答by rray
Using PHP 5.6 you can do this easy with help of unpacking operator(...$var) and use get_result()insted of bind_result()
使用PHP 5.6，你可以做到这一点很容易与帮助拆包经营者（...$var）和使用get_result（）insted的的bind_result（）
public function get_result($sql,$types = null,$params = null) {
$stmt = $this->mysqli->prepare($sql);
$stmt->bind_param($types, ...$params);
if(!$stmt->execute()) return false;
return $stmt->get_result();
}
Example:
例子：
$mysqli = new database(DB_HOST,DB_USER,DB_PASS,DB_NAME);
$output = new search($mysqli);
$sql = "SELECT * FROM root_contacts_cfm WHERE root_contacts_cfm.cnt_id = ?
AND root_contacts_cfm.cnt_firstname = ?
ORDER BY cnt_id DESC";
$res = $output->get_result($sql, 'ss',array('1','Tk'));
while($row = res->fetch_assoc()){
echo $row['fieldName'] .'<br>';
}
回答by Charles
Or maybe there are better solutions??
或者也许有更好的解决方案？
This answer doesn't really help you much, but you should seriously consider switching to PDOfrom mysqli.
这个答案并没有真正帮助你，但你应该认真考虑从 mysqli切换到PDO。
The main reason for this is because PDO does what you're trying to do in mysqli with built-in functions. In addition to having manual param binding, the execute methodcan take an array of arguments instead.
这样做的主要原因是因为 PDO 使用内置函数在 mysqli 中执行您尝试执行的操作。除了具有手动参数绑定之外，execute 方法还可以采用参数数组。
PDO is easy to extend, and adding convenience methods to fetch-everything-and-return instead of doing the prepare-execute dance is very easy.
PDO 很容易扩展，并且添加方便的方法来获取一切并返回而不是做准备-执行舞蹈是非常容易的。
回答by Matt Raines
With PHP 5.6 or higher:
使用PHP 5.6 或更高版本：
$stmt->bind_param(str_repeat("s", count($data)), ...$data);
With PHP 5.5 or loweryou might (and Idid) expect the following to work:
使用PHP 5.5 或更低版本，您可能（我确实）期望以下内容起作用：
call_user_func_array(
array($stmt, "bind_param"),
array_merge(array(str_repeat("s", count($data))), $data));
...but mysqli_stmt::bind_paramexpects its parameters to be references whereas this passes a list of values.
...但mysqli_stmt::bind_param期望它的参数是引用，而这会传递一个值列表。
You can work around this (although it's an ugly workaround) by first creating an array of references to the original array.
您可以通过首先创建一个对原始数组的引用数组来解决这个问题（尽管这是一个丑陋的解决方法）。
$references_to_data = array();
foreach ($data as &$reference) { $references_to_data[] = &$reference; }
unset($reference);
call_user_func_array(
array($stmt, "bind_param"),
array_merge(array(str_repeat("s", count($data))), $references_to_data));
回答by Steely Wing
I found a nice mysqli class, it can handle dynamic parameters, and easy to use
我发现了一个不错的mysqli类，它可以处理动态参数，并且易于使用
https://github.com/ajillion/PHP-MySQLi-Database-Class
https://github.com/ajillion/PHP-MySQLi-Database-Class
You could refer the source code how it dynamic build the query
您可以参考源代码如何动态构建查询
https://github.com/ajillion/PHP-MySQLi-Database-Class/blob/master/MysqliDb.php
https://github.com/ajillion/PHP-MySQLi-Database-Class/blob/master/MysqliDb.php
回答by Sal Celli
I solved it by applying a system similar to that of the PDO. The SQL placeholders are strings that start with the double-point character. Ex .:
我通过应用类似于 PDO 的系统来解决它。SQL 占位符是以双点字符开头的字符串。前任 。：
:id, :name, or :last_name
Then you can specify the data type directly inside the placeholder string by adding the specification letters immediately after the double-point and appending an underline character before the mnemonic variable. Ex .:
然后，您可以直接在占位符字符串内指定数据类型，方法是在双点之后立即添加规范字母并在助记变量之前附加下划线字符。前任 。：
:i_id (i=integer), :s_name or :s_last_name (s=string)
If no type character is added, then the function will determine the type of the data by analyzing the php variable holding the data. Ex .:
如果没有添加类型字符，那么该函数将通过分析保存数据的php变量来确定数据的类型。前任 。：
$id = 1 // interpreted as an integer
$name = "John" // interpreted as a string
The function returns an array of types and an array of values with which you can execute the php function mysqli_stmt_bind_param() in a loop.
该函数返回一个类型数组和一个值数组，您可以使用它们在循环中执行 php 函数 mysqli_stmt_bind_param()。
$sql = 'SELECT * FROM table WHERE code = :code AND (type = :i_type OR color = ":s_color")';
$data = array(':code' => 1, ':i_type' => 12, ':s_color' => 'blue');
$pattern = '|(:[a-zA-Z0-9_\-]+)|';
if (preg_match_all($pattern, $sql, $matches)) {
$arr = $matches[1];
foreach ($arr as $word) {
if (strlen($word) > 2 && $word[2] == '_') {
$bindType[] = $word[1];
} else {
switch (gettype($data[$word])) {
case 'NULL':
case 'string':
$bindType[] = 's';
break;
case 'boolean':
case 'integer':
$bindType[] = 'i';
break;
case 'double':
$bindType[] = 'd';
break;
case 'blob':
$bindType[] = 'b';
break;
default:
$bindType[] = 's';
break;
}
}
$bindValue[] = $data[$word];
}
$sql = preg_replace($pattern, '?', $sql);
}
echo $sql.'<br>';
print_r($bindType);
echo '<br>';
print_r($bindValue);
