phppdo

Using PHP transactions with PDO


I'm using PDO in PHP to insert multiple rows into a database:

function insert_data($key, $value) {
    global $dbh;
    $stmt = $dbh->prepare('INSERT INTO table (key, value) VALUES (:key, :value)');
    $stmt->execute([
        ':key' => $key,
        ':value' => $value
    ]);
}

insert_data('Name', 'Joe');
insert_data('Email', 'example@example.com');

Right now, each insert runs independently. I want to make sure that if one insert fails, none of the previous inserts are committed.

I thought about wrapping them in a transaction:

$dbh->beginTransaction();
insert_data('Name', 'Joe');
insert_data('Email', 'example@example.com');
$dbh->commit();

Will this work as intended, or do I need to handle errors differently? How can I make this fully safe in case one of the inserts fails?


Solution

  • Enable exceptions, and then use try/catch to roll back the transaction if there's a failure.

    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    try {
        $dbh->beginTransaction();
        $name = insert_data('Name', 'Joe');
        $email = insert_data('Email', 'example@example.com');
        $dbh->commit();
    } except (Exception $e) {
        if ($dbh->inTransaction()) {
            $dbh->rollBack();
        }
    }