MySQL: How to get the key of last inerted row in PHP

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]

This entry was posted in programming and tagged , . Bookmark the permalink.



7 Responses to MySQL: How to get the key of last inerted row in PHP

  1. Lokesh UNITED STATES Internet Explorer Windows says:

    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

    Reply  |  Quote
  2. Luke UNITED STATES Mozilla Firefox Ubuntu Linux says:

    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).

    Reply  |  Quote
  3. Lokesh UNITED STATES Internet Explorer Windows says:

    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

    Reply  |  Quote
  4. Luke UNITED STATES Mozilla Firefox Ubuntu Linux says:

    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) > 1

    Of 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) > 1

    You could use TIME_TO_SEC but I guess HOUR might be more readable.

    Reply  |  Quote
  5. Sebrategy SWITZERLAND Mozilla Firefox Windows says:

    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

    Reply  |  Quote
  6. Kim Google Chrome Windows says:

    Hello there can anyone help me with my current problem..

    Reply  |  Quote
  7. Pingback: How to fetch the ID of the table to be stored | Yankey Answers AUSTRALIA WordPress

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>