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

I'm used to doing bind_columns() and sth->fetch on most everything I do with MySQL. I need to pull back the highest 'id' column that I have.. that's it, just the highest. That way I can +1 in my perlish ways to help complete my current script. I know I can do with this bind_columns but I'm curious to see if anyone has a better way.

Replies are listed 'Best First'.
Re: mysql pull back highest column
by Corion (Patriarch) on Aug 20, 2007 at 21:40 UTC
    That way I can +1 in my perlish ways to help complete my current script

    This suspiciously sounds as if you're trying to manually create a primary key for your table. That's bad because if your script runs twice at the same time, you can get the same number twice:

    # Two scripts in parallel: my $highest_id1 = $dbh->do("select max(id) from my_table"); # first sc +ript my $highest_id2 = $dbh->do("select max(id) from my_table"); # second s +cript $highest_id1++; # first script $highest_id2++; # second script if ($highest_id1 == $highest_id2) { die };

    What you want is a primary key on your table. In most SQL databases, you can do that with:

    create table my_table ( id integer primary key not null, .... ); -- or create unique index idx_id on my_table (id); -- or, in MySQL I think: create table my_table ( id integer not null auto_increment, .... );

    Also read the DBI documentation on retrieving the last insert id.

Re: mysql pull back highest column
by jZed (Prior) on Aug 20, 2007 at 21:46 UTC
    I am not really sure what you are asking but I am going to guess (pretend?) that your question is: Is there a way in DBI to fetch a single value from a database without explicitly doing prepare, bind, execute, fetch, finish as single steps? And the answer is, yes!
    my $dbh = DBI->connect(...); my($max_foo) = $dbh->selectrow_array(q{ SELECT MAX(foo) FROM bar });
Re: mysql pull back highest column
by Joost (Canon) on Aug 20, 2007 at 21:14 UTC
Re: mysql pull back highest column
by technojosh (Priest) on Aug 20, 2007 at 21:36 UTC
    If you just want to grab the highest 'id' existing in your table, this mysql query should do the trick:

    SELECT MAX(id) FROM tablename