Best way to relate the order ID with the products that belongs to it

Question

I'm developing a shop system and I'm using the only method I was able to find to relate the ID of the order with the products that belongs to it.
When someone purchase something, first the order is added to the table orders with their details, including the ID of that order (order_id), that is auto incremented by the SQL.
I use this to add an order to the table orders:

INSERT INTO orders SET customer_id = '{$customer_id}', customer_name = '{$user['customer_name']}', order_price = '{$total_price}', order_date = '{$date}'"

Ok, the order was added. Now, in sequence, the products that belongs to that order will be added to another table, the table purchased_products.
I use PDO lastInsertId() to get the last inserted order_id from the table orders and then add each product of that order with a Foreign Key order_id in another table called purchased_products. To do this, I use:

$respective_order_id = $connection->lastInsertId();

foreach($_SESSION['cart'] as $product)
{
    $sql = "INSERT INTO purchased_products SET order_id = '{$respective_order_id}', product_name = '{$product['product_name']}', product_price = '{$product['product_price']}', quantity = '{$product['quantity']}'";
}

These codes run simultaneously. First the order will be added in the orders table with their order_id auto incremented, and then all the products of that order will also be added to the purchased_products table and the Foreign Key order_id of each one of them will have the value of the last order_id inserted in the orders table. Later I will can display any order with their products by consulting it with the Foreign Key order_id.
So far, it's working well. And as I said, this was the only way I found to assign the ID of the order with the products that belongs to it. My question is: is this secure? How about if several people buy the same time? There is the risk of the IDs be exchanged or the products not added/or goes to the wrong order? I would be immensely grateful if someone experienced answer these questions because this is making me afraid, I'm wondering if I can trust in this method.

EDIT
More details!
My database connection:

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

catch(PDOException $exception)
{
    echo "Connection error: " . $exception->getMessage();
}

All the code I use to insert the data in the two tables:

$sql = "INSERT INTO orders SET customer_id = '{$customer_id}', customer_name = '{$user['customer_name']}', order_price = '{$total_price}', order_date = '{$date}'"

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

$respective_order_id = $connection->lastInsertId();

foreach($_SESSION['cart'] as $product)
{
    $sql = "INSERT INTO purchased_products SET order_id = '{$respective_order_id}', product_name = '{$product['product_name']}', product_price = '{$product['product_price']}', quantity = '{$product['quantity']}'";

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

EDIT 2 UPDATE
Some products can have extra arrays, (i.e. colour) and some not. But all by default have these arrays filled: product_name, product_price and product_quantity.
I've adapted my logic to check for extra parameters with the code provided by peterm:

$connection->beginTransaction();

$sql = "INSERT INTO orders (customer_id, customer_name, order_price, order_date) 
        VALUES (?,?,?,?)";
$query = $connection->prepare($sql);
$query->execute(array
(
    $customer_id, 
    $user['customer_name'], 
    $total_price, 
    $date
));

$respective_order_id = $connection->lastInsertId();

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

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

foreach($_SESSION['cart'] as $product)
{
    $additional_param = array
    (
        'colour' => NULL
    );

    $colour = array_filter($product['colour']);

    if(!empty($colour))
    {
        $additional_param['colour'] = $product['colour']['colour_name'];
    }

    $query->execute(array
    (
        $respective_order_id,
        $product['product_name'],
        $product['product_price'],
        $product['quantity'],
        $additional_param['colour']
    ));
}
$connection->commit();

If the extra parameter array in the SESSION is not null, then the colour key in the SQL table will be filled with the product colour. Otherwise, it will be filled with SQL NULL.

Answer 1

1. Using PDO's lastInsertId(), is driver dependent, but safe to use as long as you're using a proper auto incrementing type of object (i.e. AUTO_INCREMENT in MySQL or sequence in PostgreSQL). MySQL driver uses LAST_INSERT_ID() under the hood.

2. If you care about transactional integrity consider wrapping the whole operation into a transaction. That way it's either all properly committed or rolled back. No orphaned order header rows, no partially populated orders etc.

3. Since you're using PDO stop doing query string interpolation. Learn and make use of prepared statements

4. Once you start using prepared statements you'll not only make things much secure but can speed up your inserts for order detail's rows.

---

Yes, I use AUTO_INCREMENT and also prepare in all insertions before execute: $query = $connection->prepare($sql); $query->execute(); I just don't understood your second explanation.

What you're currently doing is useless and dangerous. You're concatenating the query with the the values (there is nothing to prepare in your case so to speak) instead of using placeholders in the query and then binding values.

UPDATED: Here is how your may look like

$colour = array_filter($product['colour']);

$connection->beginTransaction(); // Turn off auto commit and explicitly open transaction

$sql = "INSERT INTO orders (customer_id, customer_name, order_price, order_date) 
        VALUES (?,?,?,?)"; // Use placeholders in the query. You can use named placeholders if you want to
$query = $connection->prepare($sql); // Prepare the statement
$query->execute(array(
    $customer_id, 
    $user['customer_name'], 
    $total_price, 
    $date)
); // Bind the values and execute the statement

$respective_order_id = $connection->lastInsertId();

$sql = "INSERT INTO purchased_products (order_id, product_name, product_price, quantity, colour) 
        VALUES (?,?,?,?,?)";
$query = $connection->prepare($sql); // Prepare once !!!

foreach($_SESSION['cart'] as $product) {
    $query->execute(array(
        $respective_order_id,
        $product['product_name'],
        $product['product_price'],
        $product['quantity'],
        empty($colour) ? null : $product['colour']['colour_name'], //you can bind null value for nullable columns
    )); // Bind and execute multiple times
}
// Explicitly commit the transaction
$connection->commit();