Getting the wrong id using mysqli_insert_id

Question

I am wanting to have the id returned after data is inserted into the database.

There is a strong chance that multiple people will submit their forms at the same time.

I don't want the wrong ID showing up in the results, because this ID will also be recorded in another database log and used to upload files in the next form that shows up to the user.

Is there a chance that if several people submit at the same time that mysqli_insert_id might pick up the wrong id?

Would it be better to do a query on latest date entry for that user and pull the id from there?

Or should I use mysqli_insert_id and then check that id against username in that record to verify that it is the correct one?

Or am I just worrying about something that is not an issue?

Thanks for any input, I am pretty new to this.

Kim :)

"At the same time" means a scale of milliseconds... it depends on how many users/second submit it, but I'd call it improbably at least. However wait for some complete answers and we'll both learn (: — Francisco Presencia, Dec 13 '13 at 14:31
IIRC, using transactions should make this a non issue: http://dev.mysql.com/doc/refman/5.6/en/set-transaction.html — Zarathuztra, Dec 13 '13 at 14:31

score 5 · Accepted Answer · answered Dec 13 '13 at 14:31

last_insert_id()-type functions are tied to a particular DB connection. You will NOT get the id that some other parallel connection produced, you will not get the id of a connection you did with this script 3 requests ago.

It will ALWAYS be the id of the LAST insert you performed with THIS particular db connection.

It would be utterly useless function if it returned what would essentially be a random number. e.g. Consider a series of parent/child connected tables. You insert the parent record, try to retrieve its ID, and get the ID of some OTHER insert? Now you've "lost" your new record, and would be attaching the children to an invalid parent.

Nope. The function works exactly as you'd expect, and you don't have to worry about getting the wrong ID. The only caveat is that it only retrieves the LAST insert's ID. If you do 2+ inserts in a row, you've lost the IDs of all but the last insert.

score -2 · Answer 2 · answered Dec 13 '13 at 14:31

-2

you can use mysql_insert_id like this

<?php
$con = mysql_connect("localhost", "peter", "abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

$db_selected = mysql_select_db("test_db",$con);

$sql = "INSERT INTO person VALUES ('B�rge','Refsnes','Sandnes','17')";
$result = mysql_query($sql,$con);
echo "ID of last inserted record is: " . mysql_insert_id();

mysql_close($con);
?>

answered Dec 13 '13 at 14:31

user7789076

798
2
12
25

mysql_* is deprecated. Do not use it please – Zarathuztra Dec 13 '13 at 14:32
I understand just use the mysqli procedural instead of the mysql, got it. I learned my lesson the hard way with having to rewrite things because of the depreciated list. I tossed out the book I was using and try to stick to the online php site. Maybe one day I will actually forget procedural and focus on object-oriented. Baby steps at this time. Thanks for all the input everyone. – horsey_kim Dec 13 '13 at 15:32

Getting the wrong id using mysqli_insert_id

2 Answers2