I am working with a very simple (or so I thought) process of passing a few inputs to a stored procedure, inserting a record, and then trying to get back the ID of that record that was inserted.
I just now realized that when calling a Stored Procedure, the insert_id is always 0. I have been bashing my head on the desk for a while with that one.
So, now I am trying to figure out how else to do it.
Model Function:
Code:
public function addCustomerToProduct($data){
$procedure = "CALL addCustomerProduct(?,?,@id)";
$result = $this->db->query($procedure, $data);
$newID = $this->db->query('SELECT @id as newID');
echo $newID;
}
Stored Procedure:
Code:
CREATE DEFINER=`root`@`localhost` PROCEDURE `addCustomerProduct`(IN in_customerID INT, in_productID INT, OUT insertID INT)
BEGIN
INSERT INTO order_customer_product (customerID, productID, retailAmountAtPurchase, faceValue)
SELECT
in_customerID,
in_productID,
p.retail,
p.faceValue
FROM
products as p
WHERE
p.productID = in_productID;
SELECT LAST_INSERT_ID() as insertID;
END
This was my last attempt after reading some solutions line but then I get the error:
Code:
Commands out of sync; you can't run this command now
How can I resolve this to get the PK/AI that is generated by this stored procedure?