4

I am wondering how exactly does lastInsertId() work. Atm, I am using it like this to get the id of the inserted row so I can use this id in other sections of the code. For example:

$stmt = $db->prepare('INSERT INTO image_table (image_name, image_size) VALUES (:image_name, :image_size)'); 

$stmt->execute(array(
    'image_name' => $photoName,
    'image_size' => $image_size / 1024, 
    ));

$lastInsertId = $db->lastInsertId('yourIdColumn');

Okay, my question is:

1) Does this script get the lastInsertIdfor the SQL insert that was done in that particular script?

2) What happens, for example say, 3 different users inserted data into the same table just a few nano seconds difference. Something like this:

this script -> Inserts to row id 1
Another user -> Inserts to row id 2
Another user -> Inserts to row id 3

In that case, would the lastInsertId() return the value 1 since it was the last id for the row that was inserted by that script or will it return 3 since that was the last id by the time the script came to the line that executes lastInsertId() function?

Neel
  • 9,352
  • 23
  • 87
  • 128
  • 2
    It's session specific. – Strawberry Jan 17 '14 at 16:51
  • 3
    For that script/session; else it would be pretty useless – Mark Baker Jan 17 '14 at 16:51
  • perfect! thanks for clarifying.... I was worried since if it fetched the overall id then it can be a security issue in my case. Glad its per script/session. – Neel Jan 17 '14 at 16:55
  • FWIW, you could open two windows and test this for yourself... or you could read the [documentation](http://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_last-insert-id): "The ID that was generated is maintained in the server on a *per-connection basis*." – Bill Karwin Jan 17 '14 at 17:36
  • @BillKarwin I tried that but didint work. By the time I moved my curser to another window to submit the second query, the first script had already completed. So When I posted this question, I wasnt sure how will it work in a live environment when the difference between each user's insert is just nano seconds. :) – Neel Jan 17 '14 at 17:41
  • 1
    So if LAST_INSERT_ID() returns the *last* id generated -- even if it was 60 seconds ago -- why would you need to test such quick timing? You can test it by entering an insert statement in each window, then `SELECT LAST_INSERT_ID()` in each window (they will return two different numbers), then insert one more row in window 1, and `SELECT LAST_INSERT_ID()` in window 2 to demonstrate the window 2 does not see anything but id's generated in its own session. No precision timing is necessary for this test. – Bill Karwin Jan 17 '14 at 18:15
  • aha... never thought of doing it that way.. – Neel Jan 17 '14 at 18:18

1 Answers1

4

  1. The id of the last sql insert in that scripts execution (not say, another competing insert for another user).

  2. When you have 3 inserts, it gets you the lastInsertId() like it states, the LAST one, not all 3, not 2... just the last one. Combat this by running your inserts individually and fetching the id, OR just search based on insert criteria if you need to pull that data again. inserts by different users/session, see answer #1, the lastInsertId() applies for the specific user/session/execution, not for all operations. There are basic MYSQL db temp operations happening in the background as well that would return ids if that was true.

Hope that helps.

Jakub
  • 20,418
  • 8
  • 65
  • 92
  • 1
    1 is spot on, and actually answers 2 as well. The answer of 2 does not apply, since the question was about different users, not about three statements in the same script. – GolezTrol Jan 17 '14 at 16:56
  • ...oops I read that as "what if i inserted 3 users" into a table (1 sql query), will correct answer – Jakub Jan 17 '14 at 16:57
  • 2
    I think a better answer would be lastInsertId returns the last inserted ID for a given connection. I can have many DB connections in the same script each inserting different information (even multiple DB connections to the same DB), and call lastInsertId on each of them with different results. Using the term 'user' in this sense is a bit ambiguous. The term session is better since each connection has its own session. +1, though, since I believe your answer gets the point across and my issue is purely semantic. – Jeff Lambert Jan 17 '14 at 17:50
  • To piggyback on @watcher, `user` is the wrong term particularly because at the database level, your site likely only has one user that makes connections to the database. – Brian Warshaw Jan 17 '14 at 18:05