CODEWITHSUNDEEP
phpmysqliprepared-statementsql-insertbulkinsert
eevaa
eevaa

Reputation: 1457

How to insert multiple rows in one trip to the database using a MySQLi prepared statement

I use a very simple insert statement

INSERT INTO table (col1, col2, col3) VALUES (1,2,3), (4,5,6), (7,8,9), ...

Currently, the part of the query that holds the values to be inserted is a separate string constructed in a loop.

How can I insert multiple rows using a prepared statement?

edit: I found this piece of code. However, this executes a seperate query for every row. That is not what I am looking for.

$stmt =  $mysqli->stmt_init();
if ($stmt->prepare("INSERT INTO table (col1, col2, col3) VALUES (?,?,?)")){ 
    $stmt->bind_param('iii', $_val1, $_val2, $_val3);
    foreach( $insertedata as $data ){
        $_val1 = $data['val1'];
        $_val2 = $data['val2'];
        $_val3 = $data['val3'];
        $stmt->execute();
    }
}

edit#2: My values come from a multidimensional array of variable length.

$values = array( array(1,2,3), array(4,5,6), array(7,8,9), ... );

Upvotes: 4

Views: 3615

Answers (1)

mickmackusa
mickmackusa

Reputation: 48041

This is normally only a technique that I use when I am writing a prepared statement for a query that contains an IN clause. Anyhow, I've adapted it to form a single prepared query (instead of iterated prepared queries) and I tested it to be successful on my server. The process is a bit convoluted and I don't know if there will ever be any advantage in speed (didn't benchmark). This really isn't the type of thing that developers bother with in production.

In the following snippet, the number of columns to be inserted with each row is known. For this reason, there is a "magic" 3 and a ?,?,? hardcoded.

...array_merge(...$rows) is used to flatten then unpack the payload of values.

For researchers that are dealing with string type values, just change the i to s. (When in doubt, use s for everything that is not BLOB.)

Tested/Working Code:

$rows = [[1, 2, 3], [4, 5, 6], [7, 8, 9]];  // sample indexed array of indexed arrays
$rowCount = count($rows);
$values = "(" . implode('),(', array_fill(0, $rowCount, '?,?,?')) . ")";

$conn = new mysqli("localhost", "root", "", "myDB");
$stmt = $conn->prepare("INSERT INTO test (col1, col2, col3) VALUES $values");
$stmt->bind_param(str_repeat('i', $rowCount * 3), ...array_merge(...$rows));
$stmt->execute();

There is also nothing wrong with using a prepared statement with a single row of placeholders and executing the query one row at a time in a loop.

For anyone looking for similar dynamic querying techniques:

Upvotes: 5

Related Questions