MySQLI stored procedure return PK/AI insertID

Question

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?

Answer 1

Add OUT the_id to the argument list.

Just before the END, add this statement: SELECT INSERT_ID() INTO the_id;

Then decide how you can retrieve the id from whatever variable you pass into the proc.