Page 1 of 1

Coul anybody help me finish my cart shopping?? (SQL problem)

Posted: Thu May 22, 2008 10:23 am
by tua1
Hello, first sorry about my english, I hope that anybody understand me.

I am doing a shopping cart in my site, and I finished the simply cart, customers
can add or remove items from cart etc. In my opinion it works good, but I have problem with what's next, after customers click "proceed to checkout".

In my db I have tables:

Item (itemID pk....etc),
User (userID pk..etc),
Cart (cartID pk, cookieID, itemID , qty),
Order(orderID pk, userID, total(sum), date, state)
Order_item(orderID, itemID, qty)

If the user want to finish shopping must go to the proceed to checkout, and must be loggedin to do that, If true, a $_SESSION['userid'] and total sum of cart are putting to the Order table.
After that function mysql_insert_id() return the orderID of customer order and I want to next query put orderID to the table Order_item and in the same time copy itemID, qty from Cart to Order_item (only records from Cart where cookieID=user cookie), after that records from A where cookieID=user cookie are delete, and here is my problem I don't how to create SQL statement for this.
I hope that anybody understand me and I count for your help.

For example I have this query:

UPDATE order_item
SET item_id = ( SELECT item_id
FROM cart
WHERE cookie_id=123456781910 limit 1),
qty= (SELECT qty
FROM cart
WHERE cookie_id=123456781910 limit 1)

WHERE order_id=1 limit 1;

DELETE FROM cart
WHERE EXISTS
( select item_id, qty
from order_item
where cart.item_id = order_item.item.id
and cart.qty = order_item.qty limit 1)

And put this statements to for loop (i=0, i<sum of items in cart; i++)
in the end there is only one item in order_item, when I try to change limit to the sum of items in the cart I have an error: subquery returns more than one row,

Could anybody help?

Regards