50-50 has asked for the wisdom of the Perl Monks concerning the following question:

I'm trying to get the last rows id value, which is autoincremented. I looked up the mysql docs, and found this:
$new_id = $sth->{insertid};

But when I try it, it puts nothing in my variable. Is that older syntax, or am I just screwing something up?

thanks for your help

Replies are listed 'Best First'.
Re: Finding last added auto_increment id in mysql
by Zaxo (Archbishop) on Sep 07, 2001 at 09:11 UTC

    $new_id = $sth->{'mysql_insertid'};is the identifier you want. A database handle can also access the property.

    After Compline,
    Zaxo

[reply]
[d/l]
Re: Finding last added auto_increment id in mysql
by broquaint (Abbot) on Sep 07, 2001 at 18:56 UTC
    This approach may be a little filthy but it does the job fine and dandy - 
    $id = $dbh->selectrow_array('SELECT LAST_INSERT_ID()');

    [download]
    What's happening is it's using the awfully handy LAST_INSERT_ID() function in MySQL to get the id of the last autoincremented row, and selectrow_array() returns the value in the first row when called in a scalar context (we love, wantarray(), yes we do).
    HTH

    broquaint

[reply]
[d/l]
[select]
Re: Finding last added auto_increment id in mysql
by bjohnso (Sexton) on Sep 07, 2001 at 13:52 UTC
    I mainly use SQL 2000 and Access for my DB stuff, so don't mind my lack of knowledge of MySQL. That aside, does MySQL support stored procedures? It would probably be a bit faster if you made a stored procedure that just returned the last record. Rather then looking up the ID of the last record, and then retrieving it.
[reply]
      i mainly use oracle or sometimes postgres (im not really a big mysql fan either), although mysql's dbd driver has lots of nice mysql-specific functionality; here's a rather promising implementation of stored procs, and in perl at that : check it out
[reply]
Re: Finding last added auto_increment id in mysql
by seesik (Initiate) on Sep 07, 2001 at 09:27 UTC
    are you actually inserting first? you'll need to perform an operation in order for that statement handle to retrieve the last sequence id.
[reply]

Back to Seekers of Perl Wisdom