MySQL & auto_increment
One of the placement students here had a problem yesterday, and is something I have comes across before, and I don't think I gave him a satisfactory answer.
He had a script where he was doing an insert into two tables, the first column of which were auto_increment. He reckoned that because the inserts were happening at the same time that both values would be the same. This seemed very insane.
We ended up combining the two tables, which meant there was only one auto_increment column and that made more sense, but the original table definitions were over ruled to make life easier.
I have come across this problem before where I want to use the auto_incremented value straight away to do an insert into another table. I would like a query that did an insert into an auto_increment column and then returned the value of what it just inserted.
Perhaps there is something that does this, or perhaps there is a very simple workaround, but I don't know it. If anyone does I would appreciate knowing how.
Comments
Is this what you are looking for?
I would seriously consider using Class::DBI though.
--#
my $q_insert = $dbh->prepare(q{
INSERT INTO foo
(bar, blah, arse )
VALUES ( ?, ?, ? )
});
$q_insert->execute( $bar, $blah, $arse );
return $dbh->{'mysql_insertid'};
}
#--
I probably have read the question wrong.
Posted by: Stephen McCullough | June 11, 2003 02:32 PM
Cheers Stephen,
That's exactly what I was after. I suppose I should have RTFM, it appears to be on page 668.
Duggie sent me some gubbins for Class::DBI, it does look impressive, but I haven't got round to doing anything about it yet. I like to learn by looking at others code, and as I don't work with anyone who codes in Perl at the moment, I haven't changed my methods at all since Blackstar days. Be Afraid... there is worse code than I wrote at Blackstar lurking on servers in and around Belfast.
Posted by: Andy | June 11, 2003 02:51 PM
I just did some experimentation with this.
return $dbh->{'mysql_insertid'};
always returns 0, yet
return $sth->{'mysql_insertid'};
always returns the correct value.
A quick google shows this happens to others, I must look into this further.
Posted by: Andy | June 11, 2003 03:25 PM
You can also use the MySQL "LAST_INSERT_ID" function.
So can do:
INSERT INTO foo (0, "foovalue");
INSERT INTO bar (0, LAST_INSERT_ID(), "barvalue");
And the second column of bar will get the auto-inc value from foo.
Tony
Posted by: Tony Bowden | June 11, 2003 03:36 PM
You want the insertid resulting from your statement handle ($sth) - that's the query which you've just performed. Your database handle ($dbh) won't know what the $sth insertid is, and shouldn't know, so don't ask it!
Posted by: Duggie | June 11, 2003 03:39 PM
Yet the manual (the one I borrowed from Blackstar ;-)) on Page 668 show $dbh->{'mysql_insert'} returning the auto_increment value that was most recently generated on the connection.
The LAST_INSERT_ID looks good, but would that not just return the last MySQL insert and not the last one associated with that sth, so if there were two or three scripts running at once you could end up with conflicting values.(or is that unlikely to happen).
Posted by: Andy | June 11, 2003 03:45 PM