Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

MySQL: INSERT and return?

by Spidy (Chaplain)
on Jul 04, 2006 at 18:31 UTC ( #559211=perlquestion: print w/ replies, xml ) Need Help??
Spidy has asked for the wisdom of the Perl Monks concerning the following question:

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

Comment on MySQL: INSERT and return?
Download Code
Re: MySQL: INSERT and return?
by chromatic (Archbishop) on Jul 04, 2006 at 18:54 UTC

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

      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};
Re: MySQL: INSERT and return?
by jdtoronto (Prior) on Jul 05, 2006 at 04:04 UTC
    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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://559211]
Approved by Paladin
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (8)
As of 2014-09-17 10:10 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (71 votes), past polls