INSERT and then SELECT the inserted row

Question

My table Structure,Table Name- user_tb

User_Id  Name      Mob_No   City
=================================
100      Sumith    34542    dfsc
101      Yadhu     35485    dfgd
102      Aby       34234    jhhg

Here column User_Id is auto_increment and am inserting values to this table using this query,

insert into user_tb(Name,Mob_No,City) values('Yadhu',34542,'dfsc');

I need to get User_Id of the Person at the time of insertion ie, when am inserting using this query,

insert into user_tb(Name,Mob_No,City) values('Yadhu',34542,'dfsc');

i need to get User_id-101 Is this possible....

Anyone please help me to complete this.

Answer 1

Obtaining the value of column that uses AUTO_INCREMENT after an INSERT statement can be achieved in a number of different ways. To obtain the value immediately after an INSERT, use a SELECT query with the LAST_INSERT_ID() function.

For example, using Connector/ODBC you would execute two separate statements, the INSERT statement and the SELECT query to obtain the auto-increment value.

insert into user_tb(Name,Mob_No,City) values('Yadhu',34542,'dfsc');
SELECT LAST_INSERT_ID();

If you do not require the value within your application, but do require the value as part of another INSERT, the entire process can be handled by executing the following statements:

insert into user_tb(Name,Mob_No,City) values ('Yadhu',34542,'dfsc');
insert into tbl2 (id,text) VALUES (LAST_INSERT_ID(),'text');