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:
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-
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.
3 Answers
Answers 1
This answer may explain why your existing code doesn't work. To quote:
CodeIgniter's
insert_id()will only return an ID of aninsert(). Unless you are executing something like$this->db->insert('table', $data);before calling the function it will not be able to return an ID.
MySQL's LAST_INSERT_ID(); should help you here (assuming you have permission to alter the stored procedure definition). Change it to:
CREATE DEFINER=`root`@`localhost` PROCEDURE `addCustomerProduct`( IN in_customerID INT, in_productID INT, OUT out_customerProductID INT) BEGIN INSERT INTO order_customer_product ( customerID, productID, retailAmountAtPurchase, faceValue) VALUES(8,2,'4.55',25); SELECT LAST_INSERT_ID() INTO out_customerProductID; END Then use something like the following to get the output parameter value:
public function addCustomerProduct($data){ $procedure = "CALL addCustomerProduct(?,?)"; $query = $this->db->query($procedure, $data); return $query->result(); } If the above doesn't work, try adding a $this->db->trans_start(); and $this->db->trans_complete(); before and after the stored procedure call to ensure the transaction is committed.
Answers 2
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(); Answers 3
Why
insert_id() will only workes with Query Builder and Queries only. SP's are used to call with $this->db->query() but it won't retuns data insert_id().
How
Before the End of SP add SELECT MAX(id) FROM order_customer_product;. So this will return the last ID to your code.
Suggestion
As I see there is an Insert query to DB. If I use similar case will use normal Query Builder or/and will warp it with Codeigniter Transactions(My answer on another question).
0 comments:
Post a Comment