使用PDO检查数据库插入和回滚的正确方法

Question

I have here a code to insert the order of the customer in the orders table and insert the purchased products in that order in the purchased_products table. I want to check if the insertions were made, otherwise undo the changes with PDO rollback(). My code is:

$options = [
PDO::ATTR_ERRMODE          => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => false
];    

try
{
    $connection = new PDO("mysql:host={$HOST};dbname={$DB_NAME}", $USERNAME, $PASS, $options);
}

$connection->beginTransaction();

try
{
    $sql = "INSERT INTO orders (customer_id, customer_name, order_value, order_date)
            VALUES (?, ?, ?, ?)";

    $query = $connection->prepare($sql);
    $query->execute(array
    (
        $user_id,
        $user['user_name'],
        $order_value,
        $date
    ));

    $id_of_respective_order = $connection->lastInsertId();
}
catch(PDOException $exception) 
{
    $connection->rollback();
    echo "<script>alert('An error occurred while completing your purchase. Please try again later.');</script>";
}

try
{
    $sql = "INSERT INTO purchased_products (order_id, product_name, product_price, quantity)
            VALUES (?, ?, ?, ?)";

    $query = $connection->prepare($sql);

    foreach($_SESSION['cart'] as $product)
    {
        $query->execute(array
        (
            $id_of_respective_order,
            $product['product_name'],
            $product['product_price'],
            $product['quantity']
        ));
    }
}
catch(PDOException $exception) 
{
    $connection->rollback();
    echo "<script>alert('An error occurred while completing your purchase. Please try again later.');</script>";
}

$connection->commit();

Is this way safe? I use a transaction to lock the tables and lastInsertId () to assign the ID of the order to the products that belongs to it. I check the insertions and if something went wrong undo the changes with rollback(). Is my checkout system well prepared and totally safe?

Answer 1

It makes more sence to do all your inserts inside the same Try/Catch and then if the order insert or the order_item insert fails a single catch block will deal with the rollback and any cleanup/reporting that may be required.

The way you had it the order insert could fail and then the order_item insert would still try and run, possibly creating items without a owning order.

try {
    $connection = new PDO("mysql:host={$HOST};dbname={$DB_NAME}", 
                          $USERNAME, $PASS);
    $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (PDOException $e ) {
    echo 'Connection failed: ' . $e->getMessage();
    exit;
}

$connection->beginTransaction();

try {
    $sql = "INSERT INTO orders 
           (customer_id, customer_name, order_value, order_date)
           VALUES (?, ?, ?, ?)";

    $query = $connection->prepare($sql);
    $query->execute(array( $user_id,
                         $user['user_name'],
                         $order_value,
                         $date
                        )
                   );

    $id_of_respective_order = $connection->lastInsertId();

    $sql = "INSERT INTO purchased_products 
                   (order_id, product_name, product_price, quantity)
            VALUES (?, ?, ?, ?)";

    $query = $connection->prepare($sql);

    foreach($_SESSION['cart'] as $product) {
        $query->execute(array( $id_of_respective_order,
                               $product['product_name'],
                               $product['product_price'],
                               $product['quantity']
                              )
                       );
    }

    $connection->commit();

}
catch(PDOException $e) {
    $connection->rollBack();

    echo 'Order creation failed: ' . $e->getMessage();
    echo "<script>alert('An error occurred while completing your purchase. Please try again later.');</script>";
    exit;
}