php - 从一个准备好的语句中进行许多 php mysqli 查询，而不知道有多少个参数

Question

这是对我苦苦试图解释的问题的更好解释 previously .

我希望执行多个查询，这些查询都使用相同的准备好的语句，如下所示(工作代码):

$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
}

Answer 1

// 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();
}

祝你好运!