INSERT followed by SELECT

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
BigAbe
Forum Commoner
Posts: 66
Joined: Fri Mar 31, 2006 7:41 pm

INSERT followed by SELECT

Post 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 --
Robert Plank
Forum Contributor
Posts: 110
Joined: Sun Dec 26, 2004 9:04 pm
Contact:

Post 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.
BigAbe
Forum Commoner
Posts: 66
Joined: Fri Mar 31, 2006 7:41 pm

Post 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!
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

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