multi tables

Hi anyone help with this?

the answer will probably be so simple.

I have a database with 2 tables order_line and shop

i was woundring what the code would be or if its possible for me to say select id_num from shop and insert it into order_line.

I have looked through manuals but not found the relivent pointers.

Thanks

Well as far as i know there is no way to mix a select and an insert statement.

you MIGHT be able to use the MAX/MIN(shop.id_num) functions in conjunction with the insert query, but i am not sure.

so either you have to query 2 times:

1.
SELECT num_id FROM shop WHERE yourfield = yourvalue;

2.
INSERT INTO order_line (order_id) VALUES ($num_id);

OR try the following (as i said, not sure wether this works..and would be for the lowest and highest id value only anyway):

INSERT INTO order_line (order_id) VALUES (MAX(shop.id_num))

i hope that helps 🙂

eg from mySQL site:

INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE
tblTemp1.fldOrder_ID > 100;

Ok follow up.

that worked but i need to beabe to... for example

item_id item price
1 widget 12.40

i need to move the item and price into a new table.

here is the script i use at the moment but it only puts the item in to the item column i need the price in there as well. how do i acheive this?

$sql="INSERT INTO order_line (item) SELECT shop.item FROM shop WHERE shop.item='widget'";

Thanks