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

Question

I've been using PDO and the following function to insert multiple records in chunks of 1000 at once. Now I'm working with a system that is using mysqli and I was wondering if I could slightly modify my function to work with mysqli as well however I noticed that mysqli execute doesn't accept an array as a parameter. The following function works perfectly 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 you!

`mysqli` doesn't support as many methods for binding as PDO, so you may have more trouble doing this. — tadman, Oct 09 '14 at 17:47
May be useful: [15149157/best-way-to-insert-many-values-in-mysqli](http://stackoverflow.com/questions/15149157/best-way-to-insert-many-values-in-mysqli) — Ryan Vincent, Oct 09 '14 at 19:40

Machavity · Accepted Answer · 2014-10-10T01:59:55.690

You've found one of the bigger gripes people have 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 up. Mysqli uses a much more vague binding system which can cause issues when you have an indeterminate number of elements to pass to your array.

A major problem is that mysqli wants to know what type of data to expect as the first argument. If you're not concerned with that level of filtering you could probably skate by just declaring everything a string. If that won't work, add some logic to change between string and integer. To do this, we'll add another parameter to your $insert_values at the start so we can pass the appropriate number of strings in 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 of s as elements in your array). I am assuming doing this without refactoring your array_merge, which could cause issues. That string MUST be first because it will become the first argument of the mysqli bind_param call.

We can then use the Reflection class to do the binding

$reflect = new ReflectionClass('mysqli_stmt');
$method = $reflect->getMethod('bind_param');
$method->invokeArgs($prepare, $insert_values);
$prepare->execute();

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

1 Answers1