动态绑定mysqli_stmt参数，然后绑定结果（PHP） [英] Dynamically bind mysqli_stmt parameters and then bind result (PHP)
问题描述
我试图动态地绑定mysql_stmt参数，并获得一个关联数组中的结果。我在stackoverflow上发现了这个帖子，Amber用以下代码发布了一个答案：
原始帖子：
如何使用准备好的语句制作适当的mysqli扩展类？
假设你其实是想写自己的版本（而不是使用其他答案建议的现有库之一，而且这些都是很好的选择） ）...
这里有一些功能，您可能会发现它有用的检查。第一个允许您将查询的结果绑定到关联数组，而第二个允许你传递两个数组，一个是有序的数组的键，另一个是这些键的数组数组，并将这些数据绑定到一个准备语句中：
function stmt_bind_assoc（& $ stmt，& $ out）{
$ data = mysqli_stmt_result_metadata（$ stmt）;
$ fields = array（）;
$ out = array（）;
$ fields [0] = $ stmt;
$ count = 1;
while（$ field = mysqli_fetch_field（$ data））{
$ fields [$ count] =& $ out [$ field-> name];
$ count ++;
}
call_user_func_array（mysqli_stmt_bind_result，$ fields）;
}
函数stmt_bind_params（$ stmt，$ fields，$ data）{
//动态构建bind_param的参数
$ paramstr = '';
$ params = array（）;
foreach（$ fields as $ key）
{
if（is_float（$ data [$ key]））
$ paramstr。='d';
elseif（is_int（$ data [$ key]））
$ paramstr。='i';
else
$ paramstr。='s';
$ params [] = $ data [$ key];
}
array_unshift（$ params，$ stmt，$ paramstr）;
//然后调用bind_param和正确的参数
call_user_func_array（'mysqli_stmt_bind_param'，$ params）;
}
我尝试学习代码来了解它的作用，第二个功能正常工作，但我不知道我应该怎么做才能使用第一个功能。我如何使用它来检索类似于mysqli_result :: fetch_assoc（）的数组？
我希望能够像以前一样使用结果（$ row = mysql_fetch_array（$ result））{
echo $ row ['foo' ]。。$ row ['bar'];
}
请帮助我取得一些进展：）
好的，这是一种方法：
修复多个行时出错
$ sql =SELECT`first_name`，`last_name` FROM`users `WHERE`country` =？AND`state` =？;
$ params = array（'Australia'，'Victoria'）;
/ *
在我的真实应用程序中，下面的代码被包装在一个类
中，但这只是为了例子。
您可以轻松地将其放在一个函数或类中
* /
//这将循环遍历参数，并生成类型。例如'ss'
$ types ='';
foreach（$ params as $ param）{
if（is_int（$ param））{
$ types。='i'; // integer
} elseif（is_float（$ param））{
$ types。='d'; // double
} elseif（is_string（$ param））{
$ types。='s'; // string
} else {
$ types。='b'; // blob和unknown
}
}
array_unshift（$ params，$ types）;
//开始stmt
$ query = $ this-> connection-> stmt_init（）; // $ this-> connection是mysqli连接实例
if（$ query-> prepare（$ sql））{
//绑定参数
call_user_func_array（array （$查询， 'bind_param'），$ params）方法;
$ query-> execute（）;
//获取字段名元的元数据
$ meta = $ query-> result_metadata（）;
//初始化一些空数组
$ fields = $ results = array（）;
//这是一个棘手的动作，创建一个变量数组，使用
//绑定结果
while（$ field = $ meta-> fetch_field（） ）{
$ var = $ field-> name;
$$ var = null;
$ fields [$ var] =& $$ var;
}
$ fieldCount = count（$ fieldNames）;
//绑定结果
call_user_func_array（array（$ query，'bind_result'），$ fields）;
$ i = 0;
while（$ query-> fetch（））{
for（$ l = 0; $ l <$ fieldCount; $ l ++）$ results [$ i] [$ fieldNames [$ l]] = $ fields [$ fieldNames [$ l]];
$ i ++;
}
$ query-> close（）;
//现在我们有一个美丽的
//数组结果，就像
// fetch_assoc
echo< pre>;
print_r（$ results）;
echo< / pre>;
}
I'm trying to dynamically bind mysql_stmt parameters and get the result in an associative array. I've found this post here on stackoverflow where Amber posted an answer with the following code:
Original post: How to make a proper mysqli extension class with prepared statements?
"Assuming you're actually wanting to write your own version (as opposed to utilizing one of the existing libraries other answers have suggested - and those are good options, too)...
Here are a couple of functions which you may find it useful to examine. The first allows you to bind the results of a query to an associative array, and the second allows you to pass in two arrays, one an ordered array of keys and the other an associative array of data for those keys and have that data bound into a prepared statement:"
function stmt_bind_assoc (&$stmt, &$out) {
$data = mysqli_stmt_result_metadata($stmt);
$fields = array();
$out = array();
$fields[0] = $stmt;
$count = 1;
while($field = mysqli_fetch_field($data)) {
$fields[$count] = &$out[$field->name];
$count++;
}
call_user_func_array(mysqli_stmt_bind_result, $fields);
}
function stmt_bind_params($stmt, $fields, $data) {
// Dynamically build up the arguments for bind_param
$paramstr = '';
$params = array();
foreach($fields as $key)
{
if(is_float($data[$key]))
$paramstr .= 'd';
elseif(is_int($data[$key]))
$paramstr .= 'i';
else
$paramstr .= 's';
$params[] = $data[$key];
}
array_unshift($params, $stmt, $paramstr);
// and then call bind_param with the proper arguments
call_user_func_array('mysqli_stmt_bind_param', $params);
}
I tried studying the code to understand what it does and I've made the second function work properly but I don't know what I should do to be able to utilize the first function. How do I use it to retrieve an array similar to mysqli_result::fetch_assoc()?
I want to be able to utilize the result in such a way like you used to do with:
while ($row = mysql_fetch_array($result)){
echo $row['foo']." ".$row['bar'];
}
Please help me to make some progress with this :)
Okay, here is a way to do it:
Edited, to fix bug when fetching multiple rows
$sql = "SELECT `first_name`,`last_name` FROM `users` WHERE `country` =? AND `state`=?";
$params = array('Australia','Victoria');
/*
In my real app the below code is wrapped up in a class
But this is just for example's sake.
You could easily throw it in a function or class
*/
// This will loop through params, and generate types. e.g. 'ss'
$types = '';
foreach($params as $param) {
if(is_int($param)) {
$types .= 'i'; //integer
} elseif (is_float($param)) {
$types .= 'd'; //double
} elseif (is_string($param)) {
$types .= 's'; //string
} else {
$types .= 'b'; //blob and unknown
}
}
array_unshift($params, $types);
// Start stmt
$query = $this->connection->stmt_init(); // $this->connection is the mysqli connection instance
if($query->prepare($sql)) {
// Bind Params
call_user_func_array(array($query,'bind_param'),$params);
$query->execute();
// Get metadata for field names
$meta = $query->result_metadata();
// initialise some empty arrays
$fields = $results = array();
// This is the tricky bit dynamically creating an array of variables to use
// to bind the results
while ($field = $meta->fetch_field()) {
$var = $field->name;
$$var = null;
$fields[$var] = &$$var;
}
$fieldCount = count($fieldNames);
// Bind Results
call_user_func_array(array($query,'bind_result'),$fields);
$i=0;
while ($query->fetch()){
for($l=0;$l<$fieldCount;$l++) $results[$i][$fieldNames[$l]] = $fields[$fieldNames[$l]];
$i++;
}
$query->close();
// And now we have a beautiful
// array of results, just like
//fetch_assoc
echo "<pre>";
print_r($results);
echo "</pre>";
}
这篇关于动态绑定mysqli_stmt参数，然后绑定结果（PHP）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
