SQL statement possible??

Hello

I am newbie in SQL and I have a problem, btw sorry about my english, I hope anybody understand me.

My problem is:

I have three tables, ex A (cart), B (order), C (order_item). First I fill A table, than I fill B table.

At last I want to fill C table, I want to put in the table C, pk from B as fk in C (mysql_insert_id()) and in the same time copy 2 fields from table A to C and in the end delete A.

Is this possible??

Regards

 

Yes, this is possible.

 

Thank you for your reply.

In my php script first I add data to order table and with help of mysql_insert_id()
I add to table order_item, order_id

for($i=0, i<sum of all items in cart; i++)
{
insert into orders_item (order_id)
VALUE (order_id returned by mysql_inser_id())
}

and than I try this

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,
I don't know how to do this with insert

Could anybody help?

 

yes i agree

you can use select insert into command

or making mysql storeprocedure.