Page 1 of 1

INSERT followed by SELECT

Posted: Tue Jun 20, 2006 6:00 pm
by BigAbe
Is there any way to insert a row, and have it return a value from that row?

For example, I have a table where the PK is autoincrementing. I want to insert a row, and have it automatically return the auto-incrimenting ID number the DB gives it.

Is there any way to do this without running a separate select query afterwards?

Thanks!

-- Abe --

Posted: Tue Jun 20, 2006 6:03 pm
by Robert Plank
mysql_insert_id() will give you the PK of the most recently inserted row if there is an autoincrement on the table.

Posted: Tue Jun 20, 2006 6:11 pm
by BigAbe
Robert Plank wrote:mysql_insert_id() will give you the PK of the most recently inserted row if there is an autoincrement on the table.
Awesome!

Thank you!

Posted: Tue Jun 20, 2006 6:26 pm
by bdlang
Robert Plank wrote:mysql_insert_id() will give you the PK of the most recently inserted row if there is an autoincrement on the table.
Exactly. Just as an FYI, the MySQL function LAST_INSERT_ID() will perform the same task, but within MySQL, e.g.

Code: Select all

INSERT INTO `sometable` (`somefield`) VALUES ('devnetwork');
INSERT INTO `someothertable` (`sometable_ID`) VALUES (LAST_INSERT_ID());
Please note either method requires that you have just performed the INSERT statement on the auto_increment PK field prior to calling the function, i.e. on the same script using the same connection. Also note that since PHP's mysql_query() function only allows a single query at a time, you can't pipe both queries into a single function call (in other words, the PHP specific function is the way to go, but if you need to perform this in another language you can).

Note the difference: mysql_insert_id() will grab the last value whether you've used a NULL or 0 value to increment the column, OR simply left it out altogether and let MySQL perform the auto increment. LAST_INSERT_ID() will only use the value of the automatically inserted key.

MySQL manual: LAST_INSERT_ID()
PHP manual: mysql_insert_id()