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