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 ```
>Solution :
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.