CODEWITHSUNDEEP
mysqlsqlinsert
clint
clint

Reputation: 1946

Accessing last inserted row in mysql

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.

Upvotes: 12

Views: 81635

Answers (6)

Kaushik Kyada
Kaushik Kyada

Reputation: 341

For any last inserted record will be get through mysql_insert_id() If your table contain any AUTO_INCREMENT column it will return that Value. 

mysql_query("INSERT INTO test(emsg,etime) values ('inserted',now())");
printf("Last inserted record has id %d\n", mysql_insert_id());
$last_id=mysql_insert_id();
echo $last_id;
?>

Upvotes: 2

user1626936
user1626936

Reputation: 21

you can get the id if you call LAST_INSERT_ID() function immediately after insertion and then you can use it.

Upvotes: 2

user786
user786

Reputation: 391

Use this mysql_insert_id()

It returns the AUTO_INCREMENT ID generated from the previous INSERT operation.

This function returns 0 if the previous operation does not generate an AUTO_INCREMENT ID, or FALSE on MySQL connection failure.

Upvotes: 4

PermGenError
PermGenError

Reputation: 46408

try this

SELECT LAST_INSERT_ID(colid) From tablename;

heres the Link

Upvotes: 5

chhameed
chhameed

Reputation: 4446

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

Upvotes: 10

moCap
moCap

Reputation: 969

call LAST_INSERT_ID() function immediately after insertion and save id somewhere.

Upvotes: 4

Related Questions