How to Insert Multiple Rows Efficiently Using Prepared Statements in MySQLi?

Efficient Insertion of Multiple Rows Using Prepared Statements in MySQLi

Question:

How to efficiently insert multiple rows of data into a MySQL database using a single prepared statement?

Answer:

To achieve optimal performance when inserting multiple rows, consider the following approach:

Using a prepared statement, construct a query with a dynamic number of placeholders, matching the number of columns to be inserted for each row. Flatten and unpack the payload of values using array_merge(...$rows) to populate the placeholders.

Code Sample:

<code class="php">$rows = [[1, 2, 3], [4, 5, 6], [7, 8, 9]];
$rowCount = count($rows);
$values = "VALUES (" . implode('),(', array_fill(0, $rowCount, '?,?,?')) . ")";

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

Note that this approach assumes a fixed number of columns to be inserted for each row.

Alternative Approach:

Alternatively, you can utilize a prepared statement with a single row of placeholders and execute the query multiple times in a loop. This method is also effective, although potentially less efficient for a large number of rows.

The above is the detailed content of How to Insert Multiple Rows Efficiently Using Prepared Statements in MySQLi?. For more information, please follow other related articles on the PHP Chinese website!