当前位置: 动力学知识库 > 问答 > 编程问答 >

php - How to use MySQLi prepared statements to execute multiple INSERTs with variable number of placeholders?

问题描述:

Any ideas about how to use MySQLi prepared statements to execute multiple INSERTs while having variable number (around 40) of placeholders?

I know how to make prepared statements with variable number of placeholders:

array_unshift($paramValues, str_repeat('s', count($paramValues)));

call_user_func_array(

[$statement, 'bind_param'],

sql::makeValuesReferenced($paramValues)

);

I also know how to make multiple executions:

$statement->bind_param('i', $id);

for ($id=0, $id<10, ++$id) {

$statement->execute();

}

But I couldn't wrap my mind enough to combine both methods into single one.

Basically, I have array full of data which I want to INSERT into database without having to manualy hardcode variables. I want function where I put array with data and function will take care of binding and executing.

$data = [

0 => [a => aaa, b => bbb],

1 => [a => ccc, b => ddd],

];

(I am using PHP 5.5 and MySQL 5.5.)

网友答案:

Quite simply you prepare the sql once and then repeat the bind_param() and execute() multiple times. Although I think you already got that.

So using your example input

$data = [
    0 => [a => aaa, b => bbb],
    1 => [a => ccc, b => ddd],
];

.

// get a list of all the field names
// and also build the question mark list
$fields = '';
$qMarks = '';

foreach ( $data[0] as $field => $val ) {
    $fields .= $field . ',';
    $qMarks .= '?,';
}
rtrim($fields, ',');
rtrim($qMarks, ',');

/* 
   Build the datatype list:

   Replace commas with nothing and ? with s
   THIS WILL ONLY WORK IF YOUR DATATYPES ARE ALL THE SAME

   If you also had the datatypes in your $data array this 
   would obviously work better, or rather be more flexible

   I THINK THIS IS THE FLY IN THE OINTMENT!!
*/

$datatypes = '';
$datatypes = str_replace(array(',','?'),array('','s'),$qMarks);

$sql = "INSERT INTO TABLE table ($fields) VALUES($qMarks)";

$stmt = $db->prepare($sql);


foreach ($data as $row ) {

    $params = array();
    foreach ( $row as $name => $val ) {
        $params[] = $val;
    }

    $stmt->bind_param($datatypes, $params);
    $result = $stmt->execute();

    if ( ! $result ) {
        // You have an error, deal with it here and probably stop the loop
    }
}
分享给朋友:
您可能感兴趣的文章:
随机阅读: