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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
tua1
Forum Commoner
Posts: 28
Joined: Thu May 15, 2008 9:30 pm

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

Post 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
Post Reply