PDA

View Full Version : Returning Last_Inster_ID to perl Variable

jgil2584
05-11-03, 15:08
Hey all, Im new to Perl and mysql but I have a problem. I am creating an online form used to collect information from a user. All the data from the fourm is collected and perl populates the data just fine, no problems. However, I want to give the user an account number which is an auto increment value in my database. The column name is acct_id. I am trying to find out what the last auto incremet value is using LAST_INSERT_ID() but i do not know how to assign that value to variable in my perl script. I am using dbi. For example,

$dbh->do("SET '$acctnum'=(LAST_INSERT_ID())"); or
$dbh->do("'$acctnum'=(LAST_INSERT_ID())"); or
$dbh->do("$acctnum=(LAST_INSERT_ID())"); or

How do I get the value of Last_Insert_ID into a variable in perl?
I have several books and i dont see a deffinate answer.

Thanks in advance!
John
lelle12
05-24-03, 06:51
I know nothing about mysql, but how do you normally call the function LAST_INSERT_ID()? I'm thinking, couldnt you do something like:

$sth1 = $dbh->prepare("insert into x (x1,x2) values (?,?)");
$sth2 = $dbh->prepare("values LAST_INSERT_ID()");
my $n = $sth1->execute();
if ($n > 0) {
$sth2->execute()
my $row = $sth2->fetchrow_hashref();
my $id = $row->{1};
}
sco08y
05-24-03, 23:12
You read several books and missed this? It's mentioned in the main MySQL manual in the section on DBI, and it's mentioned in the DBD::mysql man page.

perldoc DBD::mysql

Reread the docs to DBI while you're at it.

The easiest way is:

$insertid = $dbh->{'mysql_insertid'};

This also works:

($insertid) = $dbh->fetchrow_array("SELECT LAST_INSERT_ID()");

Note the parentheses around $insertid in the second code snippet. If you don't understand why those are there, you need to go over perldoc perlsyntax or you'll have all kinds of problems with list context.