Do you mean you want a variable number of arrays, @field1, @field2, ... @fieldn where you have n columns, depending on the query?

If so, you could do that, but you really don't want to go that route. Instead, you might consider an array of arrays.

something like:

while (@data = $sth1->fetchrow_array())
{
    my $col = 0;
    foreach my $coldata (@data)
    {
        push @{$fields[$col]}, $coldata;
        $col++;
    }
}
[download]

Does that get you where you were trying to go?

Well ... you could do that ... ;) or you could do this:

use DBI;
use Data::Dumper;
use Math::Matrix;
 
my $dbh = DBI->connect(
    qw(DBI:vendor:database:host user pass),
    {RaiseError=>1},
);
 
my $sth = $dbh->selectall_arrayref('select id,first,last from user');
my $matrix = Math::Matrix->new(@$sth);
 
print Dumper $matrix->transpose;
[download]

Use the CPAN. Write more glue code. :)

jeffa

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)

    my $sth1 = $dbh->prepare($full_query);
    $sth1->execute();
    my @data_aoa = [];
    while (@data = $sth1->fetchrow_array()) {
        push @data_aoa[0], $data[0];
        push @data_aoa[1], $data[1];
        push @data_aoa[2], $data[2];
    }
    .
    .
    .
    $sth1->fini
[download]

Why use pseudocode which is only a few chars different than the real code?

    my $sth1 = $dbh->prepare($full_query);
    $sth1->execute();
    my @data_aoa;
    while (@data = $sth1->fetchrow_array()) {
        push @{$data_aoa[0]}, $data[0];
        push @{$data_aoa[1]}, $data[1];
        push @{$data_aoa[2]}, $data[2];
    }
    $sth1->finish()
[download]

$tbl_ary_ref = $sth->fetchall_arrayref; returns a reference to an array each element of which is a reference to an array containing the data for each field in this row.

You can traverse this structure, so as to emulate your "1 array per field" requirement:

foreach (@$tbl_ary_ref) {
    print "@$_[1]\n"; # print the values of the second field in the da
+tabase
}
[download]

Alternatively, if you'd like more descriptive field names, you could construct a hash of array refs as follows:

my $sth1 = $dbh->prepare($full_query);
$sth1->execute();

my $fields = {};

my $hashref;
while ( $hashref = $sth1->fetchrow_hashref )
{
        for ( keys %$hashref )
        {
                push @{ $fields->{$_} }, $hashref->{$_};
        }
}

$sth1->finish;

# data is in $fields
[download]

(Code untested). Take a look at the fetchrow_hashref() method in the DBI docs.

while (@data = $sth1->fetchrow_array()) {
    for (my $field = 0; $field <= $#data ; $field++) {
        my $var_cnt = $field+1; #needed for cat in next line to work
        eval('push @field'.$var_cnt.', $data['.$field.'];');
    }
}
[download]

my %results;
while ($data = $sth1->fetchrow_hashref) {
    foreach (keys %$data) {
        push $reults{$_}, $$data{$_};
    }
}
[download]

foreach (keys %results) {
   print "Results for column $_:\n";
   foreach (@{$results{$_}}) {
     print " $_\n";
   }
}
[download]

For fun,

while (@data = $sth1->fetchrow_array()) {
    for (my $field = 0; $field <= $#data ; $field++) {
        my $var_cnt = $field+1; #needed for cat in next line to work
        eval('push @field'.$var_cnt.', $data['.$field.'];');
    }
}
[download]

can be rewritten as

{
   no strict 'refs';
   while (@data = $sth1->fetchrow_array()) {
      for (my $field = 0; $field <= $#data ; $field++) {
         my $var_name = 'field' . ($field+1);
         push(@$var_name, $data[$field]);
      }
   }
}
[download]

to eliminate the costly eval. You lose the ability to write to lexicals (my vars), although using those is just asking for trouble in this scenario.

A here's version (with the same caveat as the previous one) that works without (even partially) turning off strict:

my $pkg = \%::;
$pkg = $pkg->{$_.'::'} foreach (split(/::/, __PACKAGE__));

while (@data = $sth1->fetchrow_array()) {
   for (my $field = 0; $field <= $#data ; $field++) {
      push(@{$pkg->{'field' . ($field+1)}}, $data[$field]);
   }
}
[download]

Symtab manipulation is so much fun!