Would this work?

[g7pwx]

everything in this command works but when i add the $qty. am i doing it wrong or is this correct. if it is then it will help me as i know this wont be at fault.

thanks

$sql="INSERT INTO order_line (item, qty, price, order_id) SELECT shop.item, shop.price, 
".$tran_ID.", ".$qty." FROM shop WHERE shop.item='milk'";

[abnfire]

this is totally wronge way to do it, it wont work.

[ednark]

your order is incorrect

you are asking for the insertion order of

(item, qty, price, order_id)

but getting the results in order of

(item, price, $tran_ID, $qty)

they need to be in the same order, so just rearrange one of them and you should be fine.

ps:

the specific syntax of sql depends on which server you are running, they all accept different syntax.

mysql (which i am assuming you are using) however does allow this type of INSERT INTO SELECT statement, but not the SELECT INTO:

http://www.mysql.com/doc/en/INSERT_SELECT.html
http://www.mysql.com/doc/en/ANSI_diff_SELECT_INTO_TABLE.html