How to Insert Multiple Rows Using MySQLi Prepared Statements Efficiently?

Inserting Multiple Rows with MySQLi Prepared Statements

Problem:

You want to efficiently insert multiple rows into a database using MySQLi prepared statements. Your current approach involves constructing a separate string for each row to be inserted, which is inefficient. You seek a method to execute a single prepared statement that inserts multiple rows in one execution.

Solution:

While prepared statements are typically used for single-row operations, there is a convoluted technique that allows inserting multiple rows in one statement. Tested on a sample indexed array of indexed arrays ([[1, 2, 3], [4, 5, 6], [7, 8, 9]]), the following code demonstrates the process:

<code class="php">$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();</code>

This approach constructs a single prepared statement ("INSERT INTO test (col1, col2, col3) VALUES (...)") and fills in the placeholder values from the flattened $rows array. The str_repeat function is used to define an appropriate number of placeholder characters for binding parameters.

Alternative Approach:

If inserting multiple rows requires executing a loop, consider using a single-row prepared statement approach:

<code class="php">foreach ($rows as $row) {
    $stmt = $conn->prepare("INSERT INTO test (col1, col2, col3) VALUES (?,?,?)");
    $stmt->bind_param('iii', $row[0], $row[1], $row[2]);
    $stmt->execute();
}</code>

Additional Resources:

• [SELECT with dynamic number of values in IN()](https://stackoverflow.com/questions/2216867/select-with-dynamic-number-of-values-in-in)
• [SELECT with dynamic number of LIKE conditions](https://stackoverflow.com/questions/6022078/select-with-dynamic-number-of-like-conditions)

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