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

There's a problem with your orders table. It currently has the order_items_id, which it should not. Your orders table should store "header" level information about your order, with no reference to the items. Then in your order items table, each item has an id, and all items in the order share the same order id, which is the id in the orders table.

To add a new order to your orders table, do an insert, and then get the resulting id from mysql. The basic structure of your insert to the orders table should be:

mysql_query("INSERT INTO orders (gebruikersnaam, hoeveelheid) VALUES ('blah','blah')";
$id = mysql_insert_id();

$id will have the order_id of the order that was just created.

Then, you know the order id, but you're not adding it to the order_items table with your insert statement.

You have:

INSERT INTO order_items(partnr, price, hoeveelheid, betaaldatum)VALUES('$partnr', '$price', '$value', '$datum')

Change that to:

INSERT INTO order_items(order_id,partnr, price, hoeveelheid, betaaldatum)VALUES('$id','$partnr', '$price', '$value', '$datum')

Here is a MySQL Fiddle showing a demonstration of the table relationships you should have.

Answer 2

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