Stop and revert if one of multiple prepared statem

2019-02-19 11:40发布

问题:

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;

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

回答1:

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

    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    
  2. Wrap your queries in a transaction
  3. Wrap your transaction in a try..catch statement and add a rollback call inside.

So the code would be something like

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


标签: php mysql mysqli