2

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?

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.