How to Perform Multiple Row Insertion Effectively in MySQLi Using Prepared Statements?

Multiple Row Insertion in MySQLi Using Prepared Statements

When dealing with bulk data insertion, constructing a single query with multiple rows can be more efficient than executing multiple separate queries. However, using prepared statements for multiple row insertion requires a slightly different approach.

Creating the Statement with Multiple Rows

To insert multiple rows using a prepared statement, you can leverage the string concatenation method:

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

$stmt = $mysqli->prepare("INSERT INTO table (col1, col2, col3) VALUES $values");</code>

Here, the $values variable creates the portion of the query containing the row values in the format (?, ?, ?), (?, ?, ?), ....

Binding and Executing

Once the statement is prepared, the values can be bound and the statement can be executed. However, note that bind_param expects all parameters to be passed as a single array, so the multiple rows must be flattened and unpacked using the array_merge(...$rows) method:

<code class="php">$stmt->bind_param(str_repeat('i', $rowCount * 3), ...array_merge(...$rows));
$stmt->execute();</code>

This approach allows you to perform multiple row insertions in a single trip to the database, enhancing efficiency for bulk data insertion operations.

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