Odie Odie - 3 months ago 7
MySQL Question

How to insert values from one table to another with respective fields

I have two tables,

tbl_cart
and
tbl_sales
.

My table cart has 2 fields
product_id
and
quantityCart
.

//SELECT ALL FROM CART
$insertCustomer = "SELECT * FROM cart;";
$exec = mysqli_query($connection, $insertCustomer);

//ASSIGN EACH ROW TO $cart_array
$cart_array = array();
while ($row = mysqli_fetch_array($exec)) {
$cart_array[] = array(
"product_id" => $row['product_id'],
"quantityCart" => $row['quantityCart'],
);
}


print_r($cart_array)
shows me these:

Array ( [0] => Array ( [product_id] => 1 [quantityCart] => 20 )
[1] => Array ( [product_id] => 2 [quantityCart] => 20 )
[2] => Array ( [product_id] => 3 [quantityCart] => 20 )
[3] => Array ( [product_id] => 4 [quantityCart] => 20 ) )


My
tbl_sales
have these fields
sale_id
(Primary and AI),
product_id
,
product_quantity
.

I want to achieve something like this but using array:

INSERT INTO sales (product_id, product_quantity) VALUES (1,20)
INSERT INTO sales (product_id, product_quantity) VALUES (1,20)
INSERT INTO sales (product_id, product_quantity) VALUES (1,20)
INSERT INTO sales (product_id, product_quantity) VALUES (1,20)


Now my question is how do I achieve this using PHP/SQL?

Answer

How about doing all this in one query? If I understand the intent of the code:

INSERT INTO sales(product_id, product_quantity) 
    SELECT product_id, quantityCart
    FROM cart;