Mysqli multiple row insert, simple multi insert query

Question

How do I insert this query with mysqli?...

INSERT INTO table (field1, field2, field3) VALUES ('value', 'value', 'value'), ('value', 'value', 'value'), ('value', 'value', 'value');

Normally in mysql this query is straight forward and will insert 3 rows, how do I do this in mysqli without using a prepared statement or maybe using a prepared statement but without getting too complicated?. I just wish to know if there is a way to execute such query without doing extra funky stuff in PHP.

In essence, I have some extracted data that has around 10 rows per insert (and also needs multiple inserts in addition to having multiple rows), and this is what I need it for. I only wish to do this with a query as I have normally done it with mysql, and not add multiple insert as one per each row.

Answer 1

The mysqli class provides a number of different ways of accomplishing your inserts, each with its own benefits. Certainly, one of them should fit your needs.

The following examples assume that your unspecified "extracted data" is stored in an array of arrays: $bigArray[0...datasetsize][0...2].

The mysqli database is assumed to be $db.

Method 1 - Old School

You can do it straight forward like you are used to by simply building your query string and querying the database with it. Inserts are bundled 10 at a time, as you specified. The following code shows one such bundle and is trivially extended to the whole data set (bigArray). The data should probably be escaped using mysqli::escape_string (not done here).

The data to be inserted is assumed to be integers in all examples.

$sql = "INSERT INTO testTable (fieldA, fieldB, fieldC) VALUES ";
for ($i = 0; $i < 10; ++$i)
{
    if ($i > 0) $sql .= ", ";
    $sql .= "({$bigArray[$i][0]}),({$bigArray[$i][1]}),({$bigArray[$i][2]})";
}
$db->query($sql);

Method 2 - As Simple As Possible

If you want to use a prepared statement and parameter binding, a first effort might look like the following. While not optimal, the statement is only prepared once. However, the variables are bound for each insert, which is wasteful (but simple). Since inserts are not bundled, the example loops over 10.

$statement = $db->prepare("INSERT INTO testTable (fieldA, fieldB, fieldC) VALUES (?,?,?)");
for ($i = 0; $i < 10; ++$i)
{
    $statement->bind_param("iii",$bigArray[$i][0],$bigArray[$i][1],$bigArray[$i][2]);
    $statement->execute();
}

Method 3 - Optimized

Prepared statements and multiple inserts combined enable performance which is nearly identical to the raw insert queries of Method 1. Actual results will vary depending on your setup, but a quick test on my system with both a local and a remote database showed performance a few percentage points faster with the optimized method, increasing a few points more if data in Method 1 needs to be escaped.

The following uses call_user_func_array, but you could avoid that if you know how many inserts you want to bundle each time and build call bind_param directly. That would further increase performance slightly.

For clarity, this examples includes the outer loop and assumes 10k total lines to be inserted (i.e. bigArray[0..9999][0..2]).

$sql = "INSERT INTO testTable (fieldA,fieldB,fieldC) VALUES (?,?,?)".str_repeat(",(?,?,?)",9);
$statement = $db->prepare($sql);

// This is the type string used by statement::bind_param. 
// Example assumes all INTs.
$types = (array)str_repeat("i",30);

$values = array_fill(0,30,0); // A bit of unneeded variable init.

// * See notes following code snippet on why the intermediate array is used.
$intermediate = array();
for ($n = 0; $n < 30; ++$n)
{
    $intermediate[$n] = &$values[$n];
}

call_user_func_array(array(&$statement, "bind_param"), array_merge($types,$f));

for ($j = 0; $j < 1000; ++$j)
{
    for ($i = 0; $i < 10; ++$i)
    {
        $values[$i*3] = $bigArray[$i][0];
        $values[$i*3+1] = $bigArray[$i][1];
        $values[$i*3+2] = $bigArray[$i][2];
    }
    $statement->execute();
}

// call_user_func_array with bind_param requires the values be 
// passed by reference which is evaluated only on the initial 
// call. Using $values[...] = &$bigArray[...] below won't work
// and an intermediate array referencing $values is used. This 
// bit of "extra funky stuff" can be avoided at a very slight 
// performance penalty by setting $values[...] = $bigArray[...] 
// AND EVALUATING EACH TIME (move call_user_func_array
// inside the outer loop, i.e. right above $statement->execute()).