How do I obtain the id of the result of the last Insert statement without a second query?
I am aware I could select the last row of the database to get what I'm looking for... sometimes. The issue is if two users are inserting at the same time I'm worried that they may get the wrong results.
Pseudo Example Code:
User 1 - Insert Record A
User 1 - Select last Record
User 1 - Record A Selected
Pseudo Multi-user Problem:
User 1 - Insert Record A
User 2 - Insert Record B
User 1 - Select last Record
**User 1 - Record B Selected**
User 2 - Select last Record
User 2 - Record B Selected
The problem does not occur, when the users use different connections. That should always be the case on different requests. But even within the same script, utilizing different connections, ensures the separation.
Prove:
$connection_for_user1 = new MySQLi('localhost', 'user', 'pass', 'test');
$connection_for_user2 = new MySQLi('localhost', 'user', 'pass', 'test');
$result_for_user1 = $connection_for_user1->query("INSERT INTO test_table (data) VALUES ('A')");
$result_for_user2 = $connection_for_user2->query("INSERT INTO test_table (data) VALUES ('B')");
$record_id_for_user1 = $connection_for_user1->insert_id;
$record_id_for_user2 = $connection_for_user2->insert_id;
echo "User 1: record $record_id_for_user1
";
echo "User 2: record $record_id_for_user2
";
Result:
User 1: record 1
User 2: record 2
You can use mysql_insert_id() or mysqli_insert_id to fetch the last inserted id