CODEWITHSUNDEEP
phpmysql
Dexx
Dexx

Reputation: 163

INSERT INTO SELECT with VALUES in one query

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:

enter image description here

Upvotes: 0

Views: 3233

Answers (2)

Nigel Ren
Nigel Ren

Reputation: 57141

To put this more into what I would expect to happen, something along the lines of - prepare statement, then loop through each item and add in new row...

$insert = $connection->prepare("INSERT INTO orders (customer_id,product_description, delivery_mode, course_cost)
VALUES (?,?,?,?)");    

foreach($result as $item){
    $customerID = 1; // Have to ensure this is what your after
    $mysql_desc = $item['product_description'];
    $mysql_mode = $item['delivery_mode'];
    $mysql_cost = $item['course_cost'];
    $insert->execute([customerID,mysql_desc,mysql_mode,mysql_cost]);
}

Upvotes: 0

Shadow
Shadow

Reputation: 34305

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.

Upvotes: 1

Related Questions