0

Disclaimer: I'm working with old code that uses mysql functions instead of mysqli functions. I am not in a position to retrofit the code with the updated functionality and have to work within the old, depredated functions. I am aware this is not best practice.

We are trying to use a multiline INSERT but then need to pull out the mysql_insert_id() for each inserted row. Is this possible?

Example: 

INSERT INTO banner (date, count) VALUES ('2013-06-06', 1), ('2013-06-07', 1), ('2013-06-08', 2);

How can I then grab the insert_id for each line?

TH1981
  • 3,105
  • 7
  • 42
  • 78
  • 3
    You can't, you can only grab the last inserted id. Also you missed a single quote on your first date. – Prix Jun 07 '13 at 16:46
  • 2
    I don't think you can. Write a query that gets the ID fields for rows based on the inserted data (assuming they're unique). – Barmar Jun 07 '13 at 16:47
  • You can insert with multiple statement and use `mysql_insert_id()` after each statment creating an array with all entered id for later use. This would slowe but i don't see any other way – Fabio Jun 07 '13 at 16:50
  • Perhaps you can put the values into an array, iterate over the array - inserting a row on each iteration - then save the `mysql_insert_id()` into a result array. – Rob W Jun 07 '13 at 16:50
  • Once I asked a similar question (not exactly duplicate): http://stackoverflow.com/questions/6895679/mysqls-auto-increment-behavior-in-a-multiple-row-insert – Mehran Jun 07 '13 at 17:20
  • thanks. sometimes the answer is no, which it appears to be here. I'm going to have to rework the tables slightly to take the emphasis off of needing that uid value. doing a multi line insert is more important that the id, due to performance issues. Ta guys – TH1981 Jun 07 '13 at 20:29

2 Answers2

1

I would use a transaction and loop through your data:

--- DB SET AUTOCOMMIT TO FALSE ---

--- DB START TRANSACTION ---

--- LOOP BEGIN ---

    --- INSERT ONE ROW ---

    --- YOU CAN FETCH LAST_INSERT_ID HERE AND USE/STORE IT ---

--- LOOP END ---

--- DB END TRANSACTION (COMMIT) ---

--- DB SET AUTOCOMMIT BACK TO TRUE ---

(pseudocode)

Evan Mulawski
  • 54,662
  • 15
  • 117
  • 144
0

Perhaps you could take a different approach to this problem.

Since you'll only get the ID of the last inserted row, you can easily get all the IDs based on the number of rows that are inserted (provided that you use AUTO_INCREMENT, which seems so from your code, and that you know how many rows are inserted each time). You can easily subtrack the number of added items from the last ID and you'll get the range of IDs you need.

The range would be from Last ID - rows added + 1 to Last ID.

So for example, if you insert 5 rows and you get the ID of 42, the list of IDs would be: 38, 39, 40, 41, 42.

Shomz
  • 37,421
  • 4
  • 57
  • 85
  • 1
    That's not guaranteed if multiple users insert at the same time. – Evan Mulawski Jun 07 '13 at 17:10
  • Exactly if he does not have control over the inserts and there are concurrent inserts this would not work out. – Prix Jun 07 '13 at 17:13
  • @EvanMulawski Agreed! I gave a suggestion using as much info as we got from the question... – Shomz Jun 07 '13 at 17:38
  • yeah, they're right I can't guarantee there aren't multiple users inserting. I think i'm going to have to restructure the tables slightly for this case. thanks! – TH1981 Jun 07 '13 at 20:30