MySQLI stored procedure return PK/AI insertID 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" =&gt; 123, "productID" =&gt; 456);

...

public function addCustomerToProduct($data){
       $procedure = "CALL addCustomerProduct(?,?)";
       $result = $this-&gt;db-&gt;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-&gt;db-&gt;insert('customers', $data);
return $this-&gt;db-&gt;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?
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.