如果多个准备好的语句之一失败，则停止并还原 [英] Stop and revert if one of multiple prepared statements fail

问题描述

我有一个类Database，它具有多种方法，一些执行准备好的语句以在表中创建新行.目前，一切正常，但是如果出现故障，是否有办法停止进一步的查询并还原先前的查询.

I have a class Database that has multiple methods, some executing prepared statements for creating new rows in my tables. Currently, everything is working fine but if something were to fail, is there a way to stop further queries and revert the earlier queries.

这只是我的表单当前处理方式的一个小例子:

Here is just a little example of how my form is currently processing:

if(isset($_POST["someSubmitName"]) : 
//post variables 
....
// Start Queries 
$db->address_table_insert_statement($firstName, $lastName, $companyName, $streetAddress, $streetAddress2, $streetAddress3, $city, $state, $zip, $country, $phone, $fax, $email, $date, $date);
$addressId = mysqli_insert_id($db->connection);
$db->address_table_insert_statement($firstName, $lastName, $companyName, $billingStreetAddress, $billingStreetAddress2, $billingStreetAddress3, $billingCity, $billingState, $billingZipCode, $billingCountry, $billingPhone, $billingFax, $billingEmail, $date, $date);
$billingAdressId = mysqli_insert_id($db->connection);
$db->account_table_insert_statement($active, $addressId, $billingAddressId, $dateCreated, $dateModified);
endif;

再次，如果这3个失败，我想还原所有查询并退出此表单处理.谢谢！

Again, if any of those 3 fail I want to revert all queries and exit this form processing. Thanks!

推荐答案

1. 通过在mysqli连接之前添加此行，将mysqli置于异常模式

1. put mysqli in exception mode by adding this line before mysqli connect

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

通过事务

将查询括起来

在try..catch语句中包装事务，并在其中添加回滚调用.

Wrap your queries in a transaction

Wrap your transaction in a try..catch statement and add a rollback call inside.

所以代码将类似于

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$db = new mysqli(...);
...
try {
    $db->autocommit(FALSE);

    $db->address_table_insert_statement($firstName, $lastName, $companyName, $streetAddress, $streetAddress2, $streetAddress3, $city, $state, $zip, $country, $phone, $fax, $email, $date, $date);
    $addressId = mysqli_insert_id($db->connection);
    $db->address_table_insert_statement($firstName, $lastName, $companyName, $billingStreetAddress, $billingStreetAddress2, $billingStreetAddress3, $billingCity, $billingState, $billingZipCode, $billingCountry, $billingPhone, $billingFax, $billingEmail, $date, $date);
    $billingAdressId = mysqli_insert_id($db->connection);
    $db->account_table_insert_statement($active, $addressId, $billingAddressId, $dateCreated, $dateModified);

    $db->commit();
} catch (\Exception $e) {
    $db->rollback();
    throw $e;
}

这篇关于如果多个准备好的语句之一失败，则停止并还原的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！