0

I have these set of queries, its working fine except that I'm only able to get one value from LAST_INSERT_ID() in my UPDATE query; 

   $query .= "INSERT into itemorders 
   (itemID,colourID,itemName,itemSize,itemPrice,quantity,orderID) SELECT 
     itemID,colourID,itemName,itemSize,itemPrice,quantity, 
     LAST_INSERT_ID() FROM mycart WHERE email='".$email."'; ";

     $query .= "UPDATE CatalogueItemsSize p
     INNER JOIN itemorders i ON p.size = i.itemSize AND p.colourID = 
     i.colourID SET p.quantity = p.quantity - i.quantity WHERE i.id = 
     LAST_INSERT_ID();";

I understand that it can only retrieve the last single inserted row, how do I loop this so that I am able to catch all the values inserted in the first query?

Currently it is only subtracting quantity one row from the CatalogueItemsSize Table.

ken
  • 11
  • 3
  • Why are you calling last_insert_id() in your INSERT clause? – DavidScherer Jan 22 '19 at 16:44
  • sorry theres more queries before this – ken Jan 22 '19 at 16:48
  • **WARNING**: Whenever possible use **prepared statements** to avoid injecting arbitrary data in your queries and creating [SQL injection bugs](http://bobby-tables.com/). These are quite straightforward to do in [`mysqli`](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [PDO](http://php.net/manual/en/pdo.prepared-statements.php) where any user-supplied data is specified with a `?` or `:name` indicator that’s later populated using `bind_param` or `execute` depending on which one you’re using. – tadman Jan 22 '19 at 19:17
  • This also looks really alarming as you're concatenating together *multiple queries* into a single execution. You should always run each statement individually and check for any errors that may result. Use of functions like `mysqli_multi_query` creates huge security risks because the that function does not support placeholder values and is extremely easy to exploit. It's simply not safe to use under any circumstances. – tadman Jan 22 '19 at 19:19
  • I believe it's duplicate of https://stackoverflow.com/questions/4637367/mysql-last-insert-id-used-with-multiple-records-insert-statement. However, one possible solution is to use `TRIGGER` for `INSERT` operation for updating another table – Anton Jan 23 '19 at 10:04

1 Answers1

1

The Last insert ID ONLY returns the LAST inserted record's ID.

If you need to get the last insert ID for each record inserted, you will need to run your SELECT statement, get the results, then run an INSERT statement for each record in your SELECT and then run your update with the last_insert_id()

Also, in your INSERT statement, the last_insert_id() there will likely either cause an error or at the very least have unintended consequences.

DavidScherer
  • 863
  • 1
  • 14
  • 26