array from query to db

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);
[download]

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
[download]

Is there a faster method?

Thank you.

Comment on array from query to db Select or Download Code

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.	[reply]
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;` [download] 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' }	[reply] [d/l]
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);` [download] 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	[reply] [d/l]
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/	[reply] [d/l] [select]


Clear questions and runnable code get the best and fastest answer
	PerlMonks