How to Insert Multiple Rows into a Database Using Prepared Statements in PHP?

Insert Multiple Rows with Prepared Statements

Inserting multiple rows with prepared statements is a convenient way to add data to a database while maintaining security. Imagine you have an array of values to insert into your database table:

$values = [
    [
        'val1' => 'val1',
        'val2' => 'val2',
        'val3' => 'val3'
    ],
    [
        'val1' => 'another_val1',
        'val2' => 'another_val2',
        'val3' => 'another_val3'
    ],
    // and so on...
];

Using a Single Insert Query

To insert these rows using a prepared statement, we can use INSERT queries with multiple VALUES clauses:

INSERT INTO table (col1, col2, col3)
VALUES (
    (:val11, :val21, :val31),
    (:val12, :val22, :val32),
    (:val13, :val23, :val33)
)

For each row, we create a set of parameters, replacing the static values with named parameters. This ensures that each parameter is bound separately.

$params = [];

foreach ($values as $row) {
    $params[':val1' . count($params)] = $row['val1'];
    $params[':val2' . count($params)] = $row['val2'];
    $params[':val3' . count($params)] = $row['val3'];
}

$sql = "INSERT INTO table (col1, col2, col3) VALUES (" . implode(',', array_keys($params)) . ")";
$stmt = DB::getInstance()->prepare($sql);
$stmt->execute($params);

Chunking for Large Number of Rows

If you have a significant number of rows to insert, it's more efficient to chunk them into smaller batches to avoid excessive memory usage. You can use array_chunk to divide the values array into smaller chunks.

$chunkSize = 100; // Adjust this to your needs
$chunks = array_chunk($values, $chunkSize);

foreach ($chunks as $chunk) {
    // Prepare and execute insert query for each chunk
    // ...
}

Alternative Approach: INSERT One by One

If you need to insert each row individually, you can use the following approach:

$stmt = DB::getInstance()->prepare(
    "INSERT INTO table (col1, col2, col3) VALUES (:val1, :val2, :val3)"
);

foreach ($values as $row) {
    $stmt->bindParam(':val1', $row['val1']);
    $stmt->bindParam(':val2', $row['val2']);
    $stmt->bindParam(':val3', $row['val3']);
    $stmt->execute();
}

This method is less efficient but may be necessary for large datasets.

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