MySQL: INSERT and return?

[Spidy]

Greetings, Fellow Monks,

Recently, I've been doing some work on a MySQL system that involves, among other things, inserting rows into a certain table. In order to keep the table organized, we have it arranged like this:

id	path
int not null auto_increment	text

Now, this is all well and good, and it serves our purposes fine. We use the auto_increment attribute of the id column in order to track each entry within our table. To insert something, we use this query:

$sth = $dbh->prepare("INSERT INTO table(path) VALUES(?)");
$sth->execute($path);

And after this, I need to retrieve the id value for the row that we just inserted. I know this can be accomplished with a simple select query after the insertion, but I'm wondering: is there any way for me to reduce this to a single query, that will insert the row and return the id for the row that was just inserted?

Thanks,
Spidy

[chromatic]

I use DBI's last_insert_id() method. If you're using a new enough DBD, it's there.

[initself]

Maybe I'm not using a new enough dbd for MySQL. I had to resort to 'mysql_insertid'.

my $sth = $dbh->prepare($sql)
$sth->{mysql_insertid};

[jdtoronto]

There is also a LAST_INSERT_ID() function available directly in MySQL. You will find it under "Working with Sequences" in the MySQL documentation. Using the AUTO_INCREMENT capability makes some things easy but you need to be aware of the nature of the beast - that is - it always increases monotonically unless you do something to change that behaviour. For example, you add 10,000 records, then delete them. The next insert has an ID of 10001. Not often a problem, but something to be aware of.

jdtoronto