使用回滚在事务内部进行SQL查询插入检查

Question

In my shop system I'm using this code to insert in the DB customer order details and the products that belongs to that o:

$connection->beginTransaction();
try
{
    $sql = "INSERT INTO orders (customer_id, order_price, order_date, order_hour)
            VALUES (?, ?, ?, ?)";

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

    if($query)
    {
        $id_of_respective_order = $connection->lastInsertId();

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

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

        foreach($_SESSION['cart'] as $product)
        {
            $extras = null;
            $product_price = $product['product_price'] * $product['quantity'];

            if($product['extras'] != NULL)
            {
                foreach($product['extras'] as $extra)
                {
                    $extras .= $extra['extra_quantity'] ."x". $extra['extra_name'] ."<br/>";
                    $product_price += $extra['extra_total'] * $product['quantity'];
                }
            }

            $query->execute(array
            (
                $id_of_respective_order,
                $product['product_name'],
                $product_price,
                $product['quantity'],
                $extras
            ));
        }

        unset($_SESSION['cart']);

        echo "<script>alert('Your purchase was completed!');
        window.location = '/my-orders.php';
        </script>";
    }
    else
    {
        echo "<script>alert('An error ocurred while completing your purchase. Please try again!');
        window.location = '/my-cart.php';</script>";
    }
    $connection->commit();
}
catch(PDOException $exception) 
{
    $connection->rollBack();
    echo "<script>alert('An error ocurred while completing your purchase. Please try again!');
    window.location = '/my-cart.php';</script>";
}

My question is in regards to code optimization for error checking. It is recommended that I use if ($query) even with the catch and rollBack as I'm doing? It is necessary or I can use only catch and rollBack because it will check for erros by itself?

Answer 1

You don't need to use if if you have your PDO error set to throwing the exception.

$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);