时间戳

首页 > 解决方案 > 如果一个失败，如何处理多个插入请求？

php - 如果一个失败，如何处理多个插入请求？

问题描述

我的注册表单在两个表中插入一行。如果其中一个未完成，我如何回滚所有事务？

到目前为止，这是我的片段：

try {

   // insert row for account
   $stmt = $dbh->prepare("INSERT INTO accounts (account_num) VALUES (:account)");
   $params = [
   ":account_num" => $account_num
   ]   
   $stmt=>execute($params);

   // insert row for user
   $stmt = $dbh->prepare("INSERT INTO users (email, account_num) VALUES (:email, :account_num)");
   $params = [
      ":email" => $email,
      ":account_num" -> $account_num;
   ]
   $stmt->execute($params);

} catch (PDOExeception $e) {
   echo "error: could not create your account and profile";
}

标签： phppdomariadb

解决方案

You can do something inside a try catch like this-

$this->pdo->beginTransaction(); //prepare database for rollback changes if needed
try{
  $stmt1 = $this->pdo->prepare(...); //prepare your first statement for execution
  $stmt1->execute(...);              //execute first statement
  $stmt2 = $this->pdo->prepare(...); //prepare your second statement for execution
  $stmt2->execute(...);              //execute second statement
  $this->pdo->commit();              //confirms that all statements are executed and no errors occured
} catch (\PDOException $e) {
  $this->pdo->rollBack();            //if there is any error, the exception handler will rollback the operation
}

Please be noted that if you have an auto incremented primary key, then you may miss two auto-incremented values here because this rollback operation first creates/ inserts the data. If any error occurred then simply deletes them. So the auto incremented primary keys might be missing.

推荐阅读