CI MySQLI Stored Procedure with Last Insert ID as output

CI MySQLI Stored Procedure with Last Insert ID as output - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: CI MySQLI Stored Procedure with Last Insert ID as output (/showthread.php?tid=69761)

CI MySQLI Stored Procedure with Last Insert ID as output - sbbcarl - 01-14-2018

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?

RE: CI MySQLI Stored Procedure with Last Insert ID as output - enlivenapp - 01-15-2018

So, a couple things...

First, this is CI4 support and you're using $this->db-> syntax, so what version are you using/trying to get help for?

Second, that seems like an overly complicated way to do what you're trying to do, why not use the query builder(https://codeigniter.com/user_guide/database/query_builder.html) and then call $this->db->insert_id()?

https://codeigniter.com/user_guide/database/helpers.html

Lastly, if you're determined to use MySQL like this, you may find someone here that can help but, you'd probably be better served asking in a MySQL forum.