How to insert multiple rows using prepared statements

Question

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), ... );

insert query with multiple values clause is just a regular query like any other. means you just have to prepare and run it. — Your Common Sense, Oct 09 '13 at 12:04
How you are getting the values (1,2,3), (4,5,6), (7,8,9). Is it from any loops ? — Shafeeque, Oct 09 '13 at 12:08
I'd like to insert multiple values sets, like (1,2,3), (4,5,6), (7,8,9), and so on. The values come from a multidimensional array of variable length. — eevaa, Oct 09 '13 at 12:09
Can you run such a query `INSERT INTO table (col1, col2, col3, col4) VALUES (1,2,3,4)` with prepared statements? — Your Common Sense, Oct 09 '13 at 13:16
Yes I can. I just don't know how to put multiple value sets into one query — eevaa, Oct 09 '13 at 13:49
I mean this one: `INSERT INTO table (col1, col2, col3, col4) VALUES (?,?,?,?)` - can you run it using prepared statement? — Your Common Sense, Oct 09 '13 at 13:53
As I said, this works fine when I am just inserting ONE SINGLE set of values. It also works in a loop. — eevaa, Oct 09 '13 at 14:02
that's exactly how prepared statements are supposed to work: you prepare the statement (parsing and all the difficult stuff done) and then send the values in a loop one row at a time. — , Oct 25 '18 at 00:45

mickmackusa · Answer 1 · 2022-04-03T05:21:46.363

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:

How to insert multiple rows using prepared statements

1 Answers1

Linked

Related