How do I find the value MYSQL assigned to an AUTO

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: How do I find the value MYSQL assigned to an AUTO_INCREMENT ID (int) Field? by plaid (Chaplain) on Jun 19, 2000 at 04:23 UTC
From the DBD::mysql man page: `$insertId = $dbh->{'mysql_insertid'}` [download]	[reply] [d/l]
Re: How do I find the value MYSQL assigned to an AUTO_INCREMENT ID (int) Field? by Ovid (Cardinal) on Jun 19, 2000 at 04:25 UTC
I haven't tested it, but you should be able to use the following: `#!/usr/bin/perl # connect to database .... my $sql_statement = "INSERT INTO $table (field1,field2) VALUES($value1,$value2)"; my $sth = $dbh->prepare($sql_statement); $sth->execute or die "Can't Add record : $dbh->errstr"; # Now we can retrieve the primary key that # was just created in our last insert. my $table_key = $sth->{insertid}; # or you could use this method which # is DBI-standard my $table_key = $dbh->{'mysql_insertid'}; $sth->finish;` [download] From the Giving Credit where Credit is Due department: I got this from this link.	[reply] [d/l]
RE: How do I find the value MYSQL assigned to an AUTO_INCREMENT ID (int) Field? by chromatic (Archbishop) on Jun 19, 2000 at 04:26 UTC
You can use the LAST_INSERT_ID() function. That's a MySQL specific function, so you'll probably be better off at their site for details. (I believe you can call it in a normal UPDATE statement.) Looking at the DBI-provided functions, though, I'd use them.	[reply]
RE: How do I find the value MYSQL assigned to an AUTO_INCREMENT ID (int) Field? by Russ (Deacon) on Jun 19, 2000 at 06:23 UTC
Just as a "dot all the i's and cross all the t's" follow-up to plaid's and Ovid's posts... If you are still in the stone ages (like me) and have a version of mysql older than 3.22.2? (23, I think), you must use $sth->{insertid}. Newer versions warn us that this is deprecated. Newer versions should use $sth->{mysql_insertid}. Not trying to muddy the waters, but hoping to prevent confusion for people too lazy to upgrade (like me). :-) Russ	[reply]
Re: How do I find the value MYSQL assigned to an AUTO_INCREMENT ID (int) Field? by Brovnik (Hermit) on Jun 05, 2001 at 15:57 UTC
I use : `my $sth = $dbh->prepare($sql); $sth->execute(); my $id = $sth->{'mysql_insertid'};` [download] for me, `$dbh->{'mysql_insertid'}` doesn't work, (always returns 0). -- Brovnik	[reply] [d/l] [select]
Re: How do I find the value MYSQL assigned to an AUTO_INCREMENT ID (int) Field? by nmerriweather (Friar) on Mar 31, 2005 at 04:47 UTC
FYI Using DBD::mysql 2.9004 Works `$id = $dbh->{mysql_insertid} $id = $dbh->{insertid} $id = $sth->{mysql_insertid}` [download] Does Not Work `$id = $sth->{insertid}` [download]	[reply] [d/l] [select]


good chemistry is complicated, and a little bit messy -LW
	PerlMonks

How do I find the value MYSQL assigned to an AUTO_INCREMENT ID (int) Field?