在一个查询中使用VALUES INSERT INTO SELECT

Question

Bit new to MYSQL and PHP - I have the following code that will create a new record in multiple tables that I have setup however I need to merge the following code together somehow as it creates separate rows instead of one record

$sql .=("INSERT INTO orders (customer_id) SELECT customer_id FROM 
customer_details;");    

foreach($result as $item){
$mysql_desc = $item['product_description'];
$mysql_mode = $item['delivery_mode'];
$mysql_cost = $item['course_cost'];
$sql .=("INSERT INTO orders(product_description, delivery_mode, course_cost) VALUES('$mysql_desc', '$mysql_mode', '$mysql_cost');");
}

The result I'm getting:

Answer 1

Based on your data I assume that you want to insert the customer id and the values from php into the same record. In this case you need to combine them into the same insert ... select ... statement:

$sql .=("INSERT INTO orders(customer_id, product_description, delivery_mode, course_cost) select customer_id, '$mysql_desc', '$mysql_mode', '$mysql_cost' from customer_details;");

Couple of things to note:

1. This insert ... select ... statement will insert the same records for all customers in the customer details table. I'm not sure if this is your ultimate goal.

2. Pls consider the advices made in the comments regarding the old mysql API and the use of prepared statements.