How to perform batch INSERT, UPDATE, or DELETE operations using PDO in PHP?

To perform batch INSERT, UPDATE or DELETE operations using PDO in PHP, you can follow these steps:

Create a PDO connection by using the new PDO() constructor. Pass the database connection details such as the server, database name, username, and password as parameters.

$db = new PDO('mysql:host=localhost;dbname=my_database', 'username', 'password');

Set the error mode of the PDO connection to throw exceptions on error. This will help you catch any errors that occur during the batch operation.

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Begin a transaction using the beginTransaction() method of the PDO connection. This will ensure that all the batch operations are executed as a single unit.

$db->beginTransaction();

Prepare the SQL statement for batch operation using the prepare() method of the PDO connection. Use placeholders for the values that will be replaced later.

$stmt = $db->prepare('INSERT INTO my_table (column1, column2) VALUES (:value1, :value2)');

Bind the placeholders to the actual values using the bindParam() or bindValue() method of the statement object. You can bind values multiple times before each execution.

$stmt->bindParam(':value1', $value1);
$stmt->bindParam(':value2', $value2);

Execute the statement inside a loop, passing the new values each time. This will execute the batch operation for each set of values.

foreach ($valuesArray as $values) {
    $value1 = $values['value1'];
    $value2 = $values['value2'];
    $stmt->execute();
}

Commit the transaction using the commit() method of the PDO connection. This will save all the changes made during the batch operation.

$db->commit();

Optionally, catch any exceptions that occur during the batch operation and rollback the transaction using the rollback() method. This will undo any changes made so far.

catch (PDOException $e) {
    $db->rollback();
    throw $e;
}

By following these steps, you can perform batch INSERT, UPDATE, or DELETE operations using PDO in PHP.