Lot of time we have requirement to update two tables simultaneously. Say for example, we have two tables CUSTOMER_DETAILS and CUSTOMER_ADDRESS. While adding a row in CUSTOMER_DETAILS, few details like address is first written in CUSTOMER_ADDRESS and its reference is added in CUSTOMER_DETAILS table. Now if ADD_ID is a primary key in CUSTOMER_ADDRESS table and if it is an auto increment field than how to add this key as a foreign key in CUSTOMER_DETAILS table? Well, check following queries:
INSERT INTO CUSTOMER_ADDRESS (ADD_ID, ADD_TEXT) VALUES(NULL, 'some address value');
INSERT INTO CUSTOMER_DETAILS (NAME, ADD_ID, GENDER, PHONE_NO)
VALUES ('James Bond', LAST_INSERT_ID(), 'MALE', 007);
Code language: SQL (Structured Query Language) (sql)SELECT LAST_INSERT_ID();
Code language: SQL (Structured Query Language) (sql)
doesnt sound very thread safe to me
Hi Steven,
LAST_INSERT_ID() is unique per session. The initial value will be 0. If multiple threads are using same connection then there may arise some concurrency issues. But for different sessions it will be fine.
Read TIP 9: at http://kerneltrap.org/node/3096
Hi,
Thanks for the tip but i’ve got 1 question:
In a multi-connection situation, wouldn’t the last_insert_id() changed ,’incremented’, BEFORE the next “insert ” query is executed?
As such foreign key will be wrong for the customer details.
Am i wrong?
Hi Akshay,
As I have mentioned in the above mail, LAST_INSERT_ID() is unique to the login session. Go through the link: http://kerneltrap.org/node/3096
In a multi-connection situation, wouldn’t the last_insert_id() changed ,’incremented’, BEFORE the next “insert ” query is executed?
As such foreign key will be wrong for the customer details.
And this URL is not working http://kerneltrap.org/node/3096 please give me plantation
I hope this works
viral bhai,
hu drupal par work karu chhu.mare ek field autoincrement karvi chhe runtime.
means jyare hu data enter karu ane database maa feed karu to next time unique field autoincrement thavi joye.
very helpful tutorial which solved my sql trouble. thanks
Hi Viral, maybe is a dumb question, but what do you mean with session?
I have a conn.php and all my php pages connect to the database through this connector.
But what happened if I make an insert and immediately I use the LAST_INSERT_ID but hundreds or thousands of users are inserting rows in this same millisecond?
I want to do something like this but I don’t know is safe:
$insert1 = “INSERT INTO table1 (id_table1, description) VAUES (”,’$var_here’)”
$insert2 = “INSERT INTO table2 (id_table2, id_table1) VALUES (”,LAST_INSERT_ID)”
Thanks in advance
Hello Viral,
Nice article i got the solution for my problem, thanks..
HI, The correct function is mysql_insert_id(); not what you mentioned in this post….
i second that, mysql_insert_id(); works for me
SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = $dbName AND TABLE_NAME = $tblName
Huh, it was quite a few Google attempts before I found this super useful PHP/MySQL tip!!!
Hi sir ,, M just Working on Gmail application project .. Where i used the j2ee application tool fror develop this app. so i used the Mysql dB server fr it. I hv a confusen that i want a Profile picture fr any particular user and it is retrived frm the db server wit all the data of user..
so How can Retrive the image of user From Db when User logged into his account ….???
You cannot store the image files in your database..instead you can store the url path of the images in the server and retrieve the url and put it into the src of the img tag. For example : say you have an image at www(dot)yourdomain(dot)com/images/myimag.jpg..you will store thi url into your database and fetch this url and put it into the img tage in the user profile page whenever this user logs in..
Very very good tip found today.. Will use in future, But i believe, this should be done only when you are using transactions. Otherwise its going to give some sleepless nights when debugging your application.
SELECT LAST_INSERT_ID(); doesn’t work. how can you specify which table?
Hi all,
An official answer :
For english people : http://dev.mysql.com/doc/refman/5.5/en/example-auto-increment.html
Pour les francophones : http://dev.mysql.com/doc/refman/5.0/fr/example-auto-increment.html.
Michel
please tel me k kaise database ki query easil smj me aa skti h me bhul jati hu hmesha
jab database m kisi row ko delete kar de toh uske corresponds autoincreament id bhi delete ho jati than uss hi id ko reuse next inserted value m kaise kare…
plz help me to sort out that problem
great , i was really looking for this..
Thank You Sooo Much, Seems it works fine for me.. Thanks again
Thanks for help man..My error has been solve..:D
yea, it works fine with multi-connections
Though already use this technique at many time. but thnx for the nice post. for novice it will help a lot.
actually i was searching for a problem of autoincrement in oracle.
Thanks a ton.
yea its works
The PHP function mysql_insert_id() returns the last insert’s auto increment value. Pretty useful function, naively I would have just done another query to get the ID, but I was expecting there was a SQL sub query could be used to return a value on a successful insert . Good thing I didn’t have to. Thanks everyone.
@satbirkira
mysql_insert_id() is deprecated, and will be removed from PHP soon.
http://www.php.net/manual/en/faq.databases.php#faq.databases.mysql.deprecated
Use PDO or Mysqli instead.
hello dear h r u
i am using jsp ,i want to insert data in stock with increment item value ,and decrement value when we have sale any item in jsp?
can i say some thing!
thank you, you helped me by this post :)
Could anyone tell How could I store or this last_insert_id() value as integer? and use it as integer for further use.