I am using a PHP framework called CodeIgniter for my application. This is using the MySQLI driver for its database work.
This framework has built in support for things like a query builder
where I could just code the statement into the model, but I am trying to use a stored procedure
setup.
Anyway, I posted some questions with their support and they suggested asking here would be more appropriate.
In my model, I am calling a stored procedure like so:
$data = array("customerID" => 123, "productID" => 456);
...
public function addCustomerToProduct($data){
$procedure = "CALL addCustomerProduct(?,?)";
$result = $this->db->query($procedure, $data);
}
As it stands, this works perfectly fine. I send an array of data over, I tell my stored procedure to expect two variables, and I pass them along.
Stored Procedure:
CREATE DEFINER=`root`@`localhost` PROCEDURE `addCustomerProduct`(IN in_customerID INT, in_productID 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;
END
While this works fine, I am not able to get the insert_id
from the PK/AI.
The only way this data is passed back (it seems) is when you use :
$this->db->insert('customers', $data);
return $this->db->insert_id();
I am trying to stick with stored procedures as it just makes things easier to debug when its all separate.
How can I return the insert_id
to my application when using a stored procedure setup like this?