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