http://www.perlmonks.org?node_id=516254

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

I have a question. See a code:
use strict; use warnings; use Mysql; my @ip; my %record; #### Connect to db my $dbh = Mysql->connect( $mysql_host, $mysql_db, $mysql_user, $mys +ql_pwd); #### Create query my $query = "select ip from mal_switch"; #### query to database my $sth = $dbh->query($query); while (%record = $sth->fetchhash) { push @ip, $record{'ip'}; } print(@ip);
at the end, @ip is an array of ip. But, for load @ip I have used a loop while because I used
%record = $sth->fetchhas
Is there a faster method?

Thank you.

Replies are listed 'Best First'.
Re: array from query to db
by rnahi (Curate) on Dec 13, 2005 at 09:08 UTC

    Besides the DBI documentation, have a look at DBI Recipes. What you need, and much more, is already there.

Re: array from query to db
by Juerd (Abbot) on Dec 13, 2005 at 09:30 UTC

    (Hey, that looks like a nice node for posting a plugging reply. Let's click it and see. Hm, indeed, it's suitable for a shameless DBIx::Simple plug.)

    Consider using my module DBIx::Simple, which would allow you to simply write:

    use DBIx::Simple; my $dsn = "dbi:mysql:host=$host;database=$dbname"; my $db = DBIx::Simple->connect($dsn, $user, $pass); my @ip = $db->query('SELECT ip FROM mal_switch')->flat;

    In any case, please don't use the deprecated Mysql module, but something that is more generic. In addition, please indent according to syntax, not semantics. It's cute, but not easy to read or maintain.

    Juerd # { site => 'juerd.nl', plp_site => 'plp.juerd.nl', do_not_use => 'spamtrap' }

Re: array from query to db
by tirwhan (Abbot) on Dec 13, 2005 at 09:00 UTC

    An easier way of doing this would be:

    my $sth = $dbh->query($query); @ip = $sth->fetchcol(1);

    If you are looking for a faster method to retrieve your data you should take a look at the DBI documentation, particularly at the fetchrow_arrayref method and prepare/execute/bind. The Mysql module is just a wrapper on top of DBI, so you're already using it internally, but if you're going to do something a bit more involved it'll be easier to use DBI directly.


    Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. -- Brian W. Kernighan
Re: array from query to db
by holli (Abbot) on Dec 13, 2005 at 08:49 UTC
    try the fetchall_hashref and fetchall_arrayref methods.


    holli, /regexed monk/