Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
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
Replies are listed 'Best First'.
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 surveying the Monastery: (19)
As of 2015-07-28 15:26 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (257 votes), past polls