MySQL stored prod insert from IN data and other table

Question

I have an INSERT statement below, taking from 3 inputs that will be passed from a php post request. The statement below returns an error

INSERT INTO orderitems (orderId, productId, quantity, productName, price) (korderid, kproductid,kquantity, SELECT p.productName, p.price from products p WHERE p.id=kproductid)

The error being:

 The following query has failed: "CREATE PROCEDURE `CreateOrderItem`(IN `korderid` INT(8), IN `kproductid` INT(8), IN `kquantity` INT(8)) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER INSERT INTO orderitems (orderId, productId, quantity, productName, price) (korderid, kproductid,kquantity, SELECT p.productName, p.price from products p WHERE p.id=kproductid)" 

 MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'korderid, kproductid,kquantity, SELECT p.productName, p.price from products p...' at line 1 ``` 

Answer 1

You did not mention how you're doing with the post data. But here is how it could be done:

INSERT INTO orderitems (orderId, productId, quantity, productName, price)
SELECT :orderid, p.id, :quantity, p.productName, p.price
FROM products p
WHERE p.id = :productid

The :xyz indicates parameters for the query.