Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Accessing last inserted row in mysql

Tags:

sql

mysql

insert

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.

like image 328
clint Avatar asked Sep 26 '12 11:09

clint


People also ask

How do I get the last row inserted id in MySQL?

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.

How do I get the latest inserted record ID in SQL?

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.

How do I get last insert?

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.

How can I get last inserted record in MySQL using PHP?

If you use php to connect to mysql you can use mysql_insert_id() to point to last inserted id.


1 Answers

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

like image 143
chhameed Avatar answered Sep 22 '22 12:09

chhameed