0
\$\begingroup\$

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?

\$\endgroup\$
1
  • 1
    \$\begingroup\$ Did you test it? \$\endgroup\$ Commented Sep 15, 2015 at 15:06

1 Answer 1

1
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.