On my db-server i am inserting data in a table having a auto increment field say 'id'. Now i want to use the value of this last inserted 'id' in subsequent steps. I can use this:-
select * from table_name order by id desc limit 1;
But the problem here is, it is a server and many more insertions could be happening and there could be a case where i try to retrieve the data with the query i mentioned and get a different id ie. between my insert and select there could be some other insert and i wont get the value i inserted. Any way in which this could be addressed.?
Thanks in advance.
MySQL LAST_INSERT_ID() Function The LAST_INSERT_ID() function returns the AUTO_INCREMENT id of the last row that has been inserted or updated in a table.
To get an ID of last inserted record, you can use this T-SQL: INSERT INTO Persons (FirstName) VALUES ('Joe'); SELECT ID AS LastID FROM Persons WHERE ID = @@Identity; You can use query like this inside stored procedure or as an ad-hoc query.
Get ID of The Last Inserted Record If we perform an INSERT or UPDATE on a table with an AUTO_INCREMENT field, we can get the ID of the last inserted/updated record immediately.
If you use php to connect to mysql you can use mysql_insert_id() to point to last inserted id.
Use this
mysql_insert_id(&mysql);
as its basic structure are
mysql_insert_id ([ resource $link_identifier = NULL ] )
Retrieves the ID generated for an AUTO_INCREMENT column by the previous query (usually INSERT).
or in mysql use
SELECT LAST_INSERT_ID();
here is the ref links
http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html
http://php.net/manual/en/function.mysql-insert-id.php
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With