这是对我之前不幸试图解释的问题的更好解释。
我希望执行多个查询，这些查询都使用相同的预准备语句，如下所示（工作代码）：
$params = [
['age'=>10,'id'=>1], ['age'=>12,'id'=>2],
];
$param_types = 'ii';
$sql_template = "UPDATE mytable SET age = ? WHERE id = ?";
$stmt = $mysqli->prepare($sql_template);
$stmt->bind_param($param_types, $age, $id);
foreach($params as $param):
$age = $param['age'];
$id = $param['id'];
$stmt->execute();
endforeach;
我想把这个逻辑放在一个函数中，并像这样使用它：
queries_from_template($sql_template, $params, $param_types);
我一直在试图弄清楚如何编写函数，因为我不知道$params会是什么样子。这是我到目前为止所拥有的：
function queries_from_template($sql_template,$params,$param_types){
//$mysqli is a handle to a live mysqli DB connection
$stmt = $mysqli->prepare($sql_template);
//build the array that holds the arguments of $stmt->bind_param
//result will be eg: ['ii', 10, 1]
$bind_param_args = array_merge([$param_types],array_values($params[0]));
//call bind_param with a dynamic number of arguments
call_user_func_array([$stmt,"bind_param"],$bind_param_args);
foreach($params as $param):
/* THIS IS WHERE I'M STUCK*/
// I need a handle to each of the parameters that were bound with
// bind_param so that I can set them to the correct value
// on each loop before I execute.
// Remember I don't know how many parameters there are
//run query with current value of parameters
$stmt->execute();
endforeach;
//todo: free results, close connection, disconnect
}
// Associated sets
$params_sets = [
['age'=>10,'id'=>1],
['age'=>12,'id'=>2],
];
// Param names in right order
$param_names = ['age', 'id'];
// Param types in right order
$param_types = 'ii';
// SQL template
$sql_template = "UPDATE mytable SET age = ? WHERE id = ?";
$stmt = $mysqli->prepare($sql_template);
// Ok, let's do it!
foreach ($params_sets as $params) {
// Collecting parameters for bind_param function
// You need to do it every iteration!
// First parameter is $param_types
$bind_params = [$param_types];
// Now let's add every parameter in right order using $param_names
foreach ($param_names as $param_name) {
$bind_params[] = $params[$param_name];
}
// Ok! Call bind_param method from $stmt object with $bind_params as parameters
call_user_func_array([$stmt, 'bind_param'], $bind_params);
// And execute query
$stmt->execute();
}
祝你好运！
由于用于引用$stmt->bind_param(...)参数的特定 var 名称无关紧要，因此我将输入从关联数组更改为索引数组
以前：
$params = [
['age'=>10,'id'=>1], ['age'=>12,'id'=>2],
];
现在：
$params = [
[10,1], [12,2],
];
这样可以更轻松地遍历每个查询的参数。 以下是我的解决方案：
/*
* Executes multiple queries from the same prepared statement
*/
function queries_from_template($sql_template, $params, $param_types){
$stmt = $mysqli->prepare($sql_template);
$handles = [];//holds references to parameters
for($i=0; $i<count($params[0]);$i++):
$varname = "param_$i";
$$varname = null; //define variables $param_0, $param_1...
$handles[] = &$$varname; //store references to the new variables
endfor;
//call $stmt->bind_param: bind to the new variables
$bind_param_args = array_merge([$param_types],$handles);
call_user_func_array([$stmt,'bind_param'],$bind_param_args);
foreach($params as $param):
foreach($handles as $index => &$handle):
// assign the values for the current execute loop
// to the created vars ($param_0, $param_1...)
$handle = $param[$index];
endforeach;
$stmt->execute(); //execute, todo: error handling
endforeach;
$stmt->close(); $mysqli->close();
}