php - How to insert multiple records at once with mysqli similar to pdo -

php - How to insert multiple records at once with mysqli similar to pdo -

i've been using pdo , next function insert multiple records in chunks of 1000 @ once. i'm working scheme using mysqli , wondering if modify function work mysqli noticed mysqli execute doesn't take array parameter. next function works fine , fast pdo:

$sub_data = array_chunk($data, 1000); ($b = 0; $b < count($sub_data); $b++) { $insert_values = array(); ($a = 0; $a < count($sub_data[$b]); $a++) { $insert_values = array_merge($insert_values, array_values($sub_data[$b][$a])); $placeholder[] = '(' . implode(', ', array_fill(0, count($sub_data[$b][$a]), '?')) . ')'; } $sql2 = "insert $table_name (" . implode(",", array_keys($sub_data[$b][0])) . ") values " . implode(',', $placeholder) . ""; $prepare = $db->prepare($sql2); seek { $prepare->execute($insert_values); } grab (mysqli_sql_exception $e) { echo "<pre>"; print_r($sub_data[$b]); echo "</pre>"; echo $e->getmessage(); print_r($db->errorinfo()); } unset($insert_values); unset($placeholder); }

thank you!

you've found 1 of bigger gripes people have mysqli extension. pdo uses token binding scheme can pass array of parameters , keys , pdo marry them up. mysqli uses much more vague binding scheme can cause issues when have indeterminate number of elements pass array.

a major problem mysqli wants know type of info expect first argument. if you're not concerned level of filtering skate declaring string. if won't work, add together logic alter between string , integer. this, we'll add together parameter $insert_values @ start can pass appropriate number of strings in first argument

$insert_values = array(0 => ''); ($a = 0; $a < count($sub_data[$b]); $a++) { $insert_values = array_merge($insert_values, array_values($sub_data[$b][$a])); $placeholder[] = '(' . implode(', ', array_fill(0, count($sub_data[$b][$a]), '?')) . ')'; $insert_values[0] .= 's'; }

$insert_values[0] should ssssss (with same number of s elements in array). assuming doing without refactoring array_merge, cause issues. string must first because become first argument of mysqli bind_param call.

we can utilize reflection class binding

$reflect = new reflectionclass('mysqli_stmt'); $method = $reflect->getmethod('bind_param'); $method->invokeargs($prepare, $insert_values); $prepare->execute();

php mysql pdo mysqli