How to insert multiple records at the same time with mysqli similar to pdo
I have used PDO and the following function to insert multiple records in 1000 chunks at the same time. Now I am working with a system using mysqli and I was wondering if I could change my function a bit to work with mysqli, but I noticed that mysqli execute does not take an array as a parameter. The following function works fine and fast with PDO:
$sub_data = array_chunk($data, 1000);
for ($b = 0; $b < count($sub_data); $b++)
{
$insert_values = array();
for ($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 INTO $table_name (" . implode(",", array_keys($sub_data[$b][0])) . ") VALUES " . implode(',', $placeholder) . "";
$prepare = $db->prepare($sql2);
try
{
$prepare->execute($insert_values);
}
catch (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!
source to share
You have discovered one of the biggest problems with the mysqli extension. PDO uses a token binding system where you can pass an array of parameters and keys and PDO will marry them. Mysqli uses a much more vague binding system, which can cause problems when you have an undefined number of elements to pass into your array.
The main problem is that mysqli wants to know what datatype to expect as the first argument. If you are not interested in this level of filtering, you can probably roll around by simply declaring everything as a string. If that doesn't work, add some logic to change between string and integer. To do this, we'll add another parameter to yours $insert_values
at the beginning so that we can pass the appropriate number of lines as the first argument.
$insert_values = array(0 => '');
for ($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 now look like ssssss
(with the same number s
as the elements in your array). I am doing this without refactoring array_merge
, which can cause problems. This line must be the first because it will be the first argument to the mysqli call bind_param
.
Then we can use the Reflection class to bind
$reflect = new ReflectionClass('mysqli_stmt');
$method = $reflect->getMethod('bind_param');
$method->invokeArgs($prepare, $insert_values);
$prepare->execute();
source to share