I like to let MySQL generate unique keys for most of my tables by making them numerical and using the AUTO_INCREMENT feature. It is both an easy and convenient way to deal with primary keys. It has one disadvantage though.
Since the key is generated upon insertion of a new row, you can never know its value before the SQL statement is executed. Furthermore, it can be problematic to insert a new row into a table, and then use its key as a foreign key in another table. One could potentially follow the insert statement with a selection, but unfortunately it is possible that the primary key is the only unique value that distinguishes several rows. We can never be a 100% sure that we are getting the right one, using a selection statement.
MySQL provides us with a nifty feature that does precisely what we need:
SELECT LAST_INSERT_ID()
Lets say we have two tables tab1(id, value) and tab2(id, value, tab1_id). To insert a new row to tab1, and then new row to tab2 while referencing tab1 we can do this:
INSERT INTO tab1 (name) VALUES ('some value')
INSERT INTO tab2 (value, tab1_id) VALUES ('another value', LAST_INSERT_ID())
If you are working with PHP, this whole thing becomes even easier. The mysql module contains a great function that lets you get the id of the last inserted row:
$last_inserted_row = mysql_insert_id($dblink)
Here, $dblink is a database connection identifier variable (the one returned by mysql_connect statement). The returned key is found based on your unique connection, you can safely assume you are getting the correct value, even in a multi-user server environment. Other users, connecting to the db at the same time will get last unique id’s that match their connection.
[tags]php, mysql, database, primary key, foreign key, sql, insert, last inserted id[/tags]
Really a good one !
However just wanna know if I get the last inserted id of the row from my computer, because this has to be taken care of when there is a concurrent system/multi user system
Lokesh – this will give you the last inserted id in the current DB session. Note that mysql_insert_id takes a database resource as a parameter. That resource is the same one returned by mysql_connect, and the same one you use to run queries.
You will therefore get the last insert id created with that resource. That resource is unique for each PHP session so it will indeed be the last ID inserted from “your computer”.
Same goes for doing this on the command line. You will get the last inserted ID that you inserted since login.
If you call this function right after logging into the database, without actually running an insert query it will return 0 (zero).
Hello everyone,
As always have a question, Can we write queries inside a procedure[PHP/MYSQL]. Say, in a scenario, I want to find out if the duration is less than an hour and i happen to write a procedure for this, one way(That I know) is to write a block of code to get the duration thats stored in secods in to H:i:s format and then Check for the desired condition, In this can i use TIME_TO_SEC of mysql instead of writing a block of code in my procedure? Any help is highly appreciated
Thanks in advance
Lokesh, you might need to be more specific with what you want to do there. I’m assuming you have a column “duration” in your table “t” which is in MySQL time format. To get all the values from that column that are greater than 1 you can do:
SELECT * FROM t WHERE HOUR(duration) > 1Of course you mention duration so perhaps you have 2 columns “start_time” and “end_time”. In that case you can do:
SELECT * FROM t WHERE HOUR(end_time-start_time) > 1You could use TIME_TO_SEC but I guess HOUR might be more readable.
Thanks a lot for this really straight forward and fool-proof solution!
I’m working on a multi-user Ajax db-system and had exactly this problem.
The fact that
mysql_insert_id($dblink)
gives the last ID from that CONNECTION/SESSION is just perfect!
Thanks again!
Seb
Hello there can anyone help me with my current problem..
Pingback: How to fetch the ID of the table to be stored | Yankey Answers
