Categories
Development

Bulk db prepared insert with rollback even if 1 record fails, PHP

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());
}

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.