SQL statement possible?

tua1

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 that fields from A

Is this possible??

Regards

tomhath

You can't do it all in one statement. But using php or a stored procedure you shouldn't have any problem.

You will need to get the primary key from table A before inserting into table B, then use it to get the values and delete the row. You can use a SELECT statement to get the values from table A as prt of the INSERT statement.

You should put all of the inserts and the delete in a TRANSACTION to make sure it all happens or none of it happens.

tua1

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?