How to Insert Multiple Rows with a Single Prepared Statement in MySQLi?

Inserting Multiple Rows with a Single Prepared Statement in MySQLi

Problem:

Insert multiple rows into a database using a prepared statement, avoiding the need for separate queries for each row.

Solution:

While prepared statements typically handle single row operations, it's possible to insert multiple rows using a special query syntax and methodology.

Approach:

1. Create a values string: Use array_merge() and implode() to construct a string of values in the format "(, , ), ...". This represents the rows to be inserted.
2. Prepare the statement: Prepare a query with the values string as the VALUES clause, enclosing it in parentheses.
3. Create a binding parameter: Determine the number of rows and prepare a binding parameter with a string repetition of "i" (for integer values) multiplied by the number of rows multiplied by the number of columns per row.
4. Bind the values: Use ...array_merge(...$rows) to unpack the values from the multidimensional array and bind them to the parameter.
5. Execute the statement: Execute the prepared statement to insert the specified rows with a single query.

Code Example:

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

$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>

Alternative Approach:

It's also acceptable to use a prepared statement with a single row of placeholders and execute it in a loop for each row, which is simpler but potentially less efficient.

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