Recently I needed to make a bulk insert into db with prepared statement query. The task was to do it so that if one record failed one can rollback all records and return an error. That way no data is affected by faulty code and/or wrong data provided.
The PHP code:
try {
$db = $this->db()->prepare("INSERT INTO `data` (`amount`, `currency` `message`)
VALUES ( :amount, :currency, :message)");
$this->db()->beginTransaction();
foreach ($data as $item) {
try {
$db->execute([ ':amount' => $item['amount'], ':currency' => $item['currency'], ':message' => "Invoice {$item['invoice']}"]);
} catch (\PDOException $e_pdo) {
$this->db()->rollback();
throw new Exception($e_pdo->getMessage());
}
}
$this->db()->commit();
} catch (\Exception $e) {
throw new Exception('DB bulk insertion error: ' . $e->getMessage() . ' code: ' . $e->getCode());
}