php - Mysql insert and select id of inserted
Get the solution ↓↓↓I am inserting 1 row in mysql table that has auto_increment id value and want to get id of that row. Do you know some construction like
{
INSERT INTO some_table (`name`) VALUES ('test name')
ON SUCCESS RETURN id
}
Table structure:
id: int not null auto_increment Primary key
name: Varchar (20)
I read about LAST_INSERT_ID, but if my script was called simultaneously, there is a change of 2 different inserts and LAST_INSERT_ID can return wrong value.
I am using php and prepared statements that does not allow several commit and/or rollback.
Thanks for ideas.
Answer
Solution:
You have wrong assumptions about multiuser use of PHP PDO. Last insert ID is stored in PDO object created upon connection. So this "last insert ID" value is stored per connection. This way simultaneous calls will not interfere with each other.
Here's manual reference.
Answer
Solution:
try this
$sql = "INSERT INTO some_table (`name`) VALUES ('test name')";
$result = mysql_query($sql);
$last_id = mysql_insert_id();
Answer
Solution:
First do the insert
INSERT INTO some_table (`name`) VALUES ('test name');
Then immediately after do
SELECT id FROM some_table WHERE name = 'test name';
And make sure you capture the output of latter one (this may vary depending on how you connect to your DB).
EDIT: Alternatively, you could do this right after the insert:
SELECT id FROM some_table WHERE name = 'test name' ORDER BY id DESC LIMIT 1
This way you can avoid getting an earlier input with the same name.
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: foreign key constraint is incorrectly formed laravel
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.