PHP shopping cart multiple values in one id

Question

I made a php webshop with a shopping cart for my school project. The cart is working fine but I have some problems with storing the products from the cart into the database with the orders.

I'm now making a query where the products are stored in the order database and the order_items database. But when I store them in the order database all my products get a different order_id, I want to create a query to store the products in one order_id. But for some reason I can't get that working for me.

The function in the script as I have it now:

 function cart() {
global $lang;
global $query;
global $query2;
$total = 0;
$total2 = 0;
foreach($_SESSION as $name => $value) {
    if ($value>0)   {

        if (substr($name, 0, 5)=='cart_')   {
            $id = substr($name, 5, (strlen($name)-5));
            $get = mysql_query('SELECT id, partnr, specs, price FROM parts WHERE id='.mysql_real_escape_string($id));
            while ($get_row = mysql_fetch_assoc($get))  {
                $sub = $get_row['price']*$value;
                echo $get_row['partnr'].' x '.$value.' @ &euro;'.number_format($get_row['price'], 2). ' = &euro;'.number_format($sub, 2).' <a href="cart.php?remove='.$id.'">[-]</a> <a href="cart.php?toevoegen2='.$id.'">[+]</a> <a href="cart.php?delete='.$id.'">[Delete]</a> <br/>';
                if(isset($_POST['behandelen'])){
                    $partnr=$get_row['partnr'];
                    $price=$get_row['price'];
                    $datum=date("Y-m-d H:i:s");
                    $query = "UPDATE parts set hoeveelheid = hoeveelheid - '$value' WHERE id = '$id'";
                    $query2= "INSERT INTO order_items(partnr, price, hoeveelheid, betaaldatum)VALUES('$partnr', '$price', '$value', '$datum')";
                }
                mysql_query($query);
                mysql_query($query2);
            }

        }
        $total += $sub; //totaalprijs exclusief btw
        $total2 += $sub*1.21; //berekening inclusief btw van 21%
        $btw = $total2-$total;
    }
}
if ($total==0)  {
    echo $lang['CART_EMPTY'];
}
else {
    global $lang;

    echo $lang['CART_SUBTOTAL'], '&euro;'.number_format($total, 2); echo "<br/>";
    echo $lang['CART_BTW'], '&euro;'.number_format($btw, 2); echo "<br/>";
    echo $lang['CART_TOTAL'], ' &euro;'.number_format($total2, 2); //prijs inclusief btw wordt weergegeven op de site
    echo "<form method='post'>
    <input type='submit' name='behandelen' value='"; echo $lang['BUTTON_PAY']; echo "'>
</form>";
}


}

The tables I have now:

Orders: link to orders image

order_items: link to order_items image

I hope someone can show me how it is done.

Thanks in advance.

Updated script:

function cart() {
global $lang;
global $query;
global $query2;
global $query3;
$total = 0;
$total2 = 0;
$datum=date("Y-m-d H:i:s");
$query3= "INSERT INTO orders (betaaldatum) VALUES ('$datum')";
$iid = mysql_insert_id();
mysql_query($query3);
foreach($_SESSION as $name => $value) {
    if ($value>0)   {

        if (substr($name, 0, 5)=='cart_')   {
            $id = substr($name, 5, (strlen($name)-5));
            $get = mysql_query('SELECT id, partnr, specs, price FROM parts WHERE id='.mysql_real_escape_string($id));

            while ($get_row = mysql_fetch_assoc($get))  {
                $sub = $get_row['price']*$value;
                echo $get_row['partnr'].' x '.$value.' @ &euro;'.number_format($get_row['price'], 2). ' = &euro;'.number_format($sub, 2).' <a href="cart.php?remove='.$id.'">[-]</a> <a href="cart.php?toevoegen2='.$id.'">[+]</a> <a href="cart.php?delete='.$id.'">[Delete]</a> <br/>';
                if(isset($_POST['behandelen'])){
                    $partnr=$get_row['partnr'];
                    $price=$get_row['price'];
                    $datum=date("Y-m-d H:i:s");
                    $query = "UPDATE parts set hoeveelheid = hoeveelheid - '$value' WHERE id = '$id'";
                   // $query3= "INSERT INTO orders (betaaldatum) VALUES ('$datum')";
                   // $iid = mysql_insert_id();
                    $query2= "INSERT INTO order_items(order_id, partnr, price, hoeveelheid, betaaldatum)VALUES('$iid','$partnr', '$price', '$value', '$datum')";
                }
                mysql_query($query);

                mysql_query($query2);
            }

        }
        $total += $sub; //totaalprijs exclusief btw
        $total2 += $sub*1.21; //berekening inclusief btw van 21%
        $btw = $total2-$total;
    }
}
if ($total==0)  {
    echo $lang['CART_EMPTY'];
}
else {
    global $lang;

    echo $lang['CART_SUBTOTAL'], '&euro;'.number_format($total, 2); echo "<br/>";
    echo $lang['CART_BTW'], '&euro;'.number_format($btw, 2); echo "<br/>";
    echo $lang['CART_TOTAL'], ' &euro;'.number_format($total2, 2); //prijs inclusief btw wordt weergegeven op de site
    echo "<form method='post'>
    <input type='submit' name='behandelen' value='"; echo $lang['BUTTON_PAY']; echo "'>
</form>";
}


}

Answer 1

You have to insert your order and get the last id inserted mysql_insert_id

Then use the returned id to identify order_id in order_items table.

So, you need to have:

create order table;

before insert products in orders_items table, create one order: INSERT INTO order (date,...) VALUES (now(),...);

recover last inserted id: $order_id = mysql_insert_id();

insert products in order_items: INSERT INTO order_items (order_id,...) VALUES ('$order_id',...)

You have to change:

$query3= "INSERT INTO orders (betaaldatum) VALUES ('$datum')";
$iid = mysql_insert_id();
mysql_query($query3);

To:

$query3= "INSERT INTO orders (betaaldatum) VALUES ('$datum')";
mysql_query($query3);
$iid = mysql_insert_id();