MySQLI stored procedure return PK/AI insertID

2 votes

1 answer

363 views

                          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?
                        

Asked by SBB (161 rep)

Jan 19, 2018, 04:11 AM
Last activity: Dec 10, 2022, 12:00 PM

MySQLI stored procedure return PK/AI insertID

Related Questions