MySQL stored procedure no insert ID returned?

I have a very simple query, not sure what I am doing wrong here.

My DB call is not receiving an insert id as I would expect it to.

Table:

enter image description here

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

PHP:

   public function addProduct($data, $userID)
    {
        // Do we already have a pending order for this user?
        $orderID = $this->doesOrderExist($userID);

        // We had no order, lets create one
        if (!$orderID) {
            $orderID = $this->createOrder($userID);
        }

        /**
         * Insert the customer product.
         * This relates a denomination to a customer.
         */
        $customerProductID = $this->addCustomerProduct($data);

        // Add this customer product to the order
        $this->addProductToOrder(array("customerProductID" => $customerProductID, "orderID" => $orderID));

        // Return
        return $customerProductID;
    }

    /**
     * Description: Add a customer product / reward
     * Page: client/add_reward
     */
    public function addCustomerProduct($data){
        $procedure = "CALL addCustomerProduct(?,?)";
        $result = $this->db->query($procedure, $data);
        return $this->db->insert_id();
    }

The line with the issue is: $customerProductID = $this->addCustomerProduct($data);.

A new record is being inserted into the table and the table has a PK/AI. Data goes in fine but 0 is returned as the $customerProductID.

Will an insert from select statement not return an insert ID perhaps?

Update For @Ravi-

enter image description here

Update 2:

I created a separate method and hard coded the query and data being sent.

It adds the records fine, AI goes up, 0 is returned as the last id.

public function test(){
    $procedure = "CALL addCustomerProduct(?,?)";
    $result = $this->db->query($procedure, array("customerID" => 1, "productID" => 20));
    echo $this->db->insert_id();
}

Also restarted the MySQL server to make sure there wasn't anything weird going on there.

Also, updated the SP to just insert random data into the table without using a select.

CREATE DEFINER=`root`@`localhost` PROCEDURE `addCustomerProduct`(IN in_customerID INT, in_productID INT)
BEGIN
    INSERT INTO order_customer_product (customerID, productID, retailAmountAtPurchase, faceValue)
    VALUES(8,2,'4.55',25);
END

Update 3:

Right after the insert, I am printing out the last query that was ran as well as the result. You will notice that there is 1 affected row (the insert is happening) but the insert_id is still 0.

CALL addCustomerProduct('8','33')

CI_DB_mysqli_result Object
(
    [conn_id] => mysqli Object
        (
            [affected_rows] => 1
            [client_info] => mysqlnd 5.0.12-dev - 20150407 - $Id: b396954eeb2d1d9ed7902b8bae237b287f21ad9e $
            [client_version] => 50012
            [connect_errno] => 0
            [connect_error] => 
            [errno] => 0
            [error] => 
            [error_list] => Array
                (
                )

            [field_count] => 0
            [host_info] => Localhost via UNIX socket
            [info] => 
            [insert_id] => 0
            [server_info] => 5.6.35
            [server_version] => 50635
            [stat] => Uptime: 1637  Threads: 3  Questions: 508  Slow queries: 0  Opens: 113  Flush tables: 1  Open tables: 106  Queries per second avg: 0.310
            [sqlstate] => 00000
            [protocol_version] => 10
            [thread_id] => 25
            [warning_count] => 0
        )

    [result_id] => 1
    [result_array] => Array
        (
        )

    [result_object] => Array
        (
        )

    [custom_result_object] => Array
        (
        )

    [current_row] => 0
    [num_rows] => 
    [row_data] => 
)

Update 4:

From some of the research I have done, unless you use the mysqli method such as $this->db->insert(), it won't provide a last insert id back to you.

I am going to try and figure out Ravi's suggestion but it seems that code igniter doesn't allow the example that was shown. At least I know now that I am not crazy and its just not normal behavior unless you use the ``insert` method vs a stored procedure.

1 answer

  • answered 2018-01-14 07:28 Ravi

    Ideally, following line should work

    $this->db->insert_id;
    

    But, I'm not sure why is not working, so I would suggest a workaround as following, recompile your procedure with additional parameter out_lastId, which will return last inserted id

    CREATE DEFINER=`root`@`localhost` PROCEDURE `addCustomerProduct`(IN in_customerID INT, in_productID INT, OUT out_lastId INT)
    

    And, after insert set the value with last inserted id.

     SET out_lastId = LAST_INSERT_ID();
    

    ==Updated==

    $this->db->multi_query( "CALL addCustomerProduct($data, @id);SELECT @id as id" );
    $db->next_result();            // flush the null RS from the call
    $rs=$this->db->store_result();       // get the RS containing the id
    echo $rs->fetch_object()->id, "\n";
    $rs->free();