PDO lastInsertId

Question

In my shop system I'm using the PDO lastInsertId to assign the order_id to the products that belongs to it like this:

$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
    ));

    $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>";

    $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: Will this fail and assign the wrong order ID to products?

I've searched and found people just saying that I can blindly trust this method and that it is 100% safe because my database is InnoDB and has transaction support. I did my best to write the code as accurately as possible, but is there something to improve or change? I would love a definitive answer from someone who's been using this method. Can I launch this system for my clients?

Answer 1

If the insert was successful then your code will work.

Just be sure that you're setting ERRMODE_EXCEPTION after connecting to the database with:

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

If you didn't do this, and don't correct your code, you may encounter unpredictable behaviour, because the insert into orders query may fail and you're not testing for the result of $query->execute() (which could be TRUE or FALSE). If you set error mode as above, a failed INSERT will throw an PDOException.