in reply to Re: Why a reference, not a hash/array?
in thread Why a reference, not a hash/array?

Also this:

@data = @{$sth->fetchall_arrayref()};

Doesn't DWIW! If I do this: 

@data = @{$sth->fetchall_arrayref()};
print Dumper(\@data)

[download]
I get this: 
$VAR1 = [
          [
            'foo'
          ],
          [
            'bar'
          ],
          [
            'baz'
          ]
        ];

[download]
that is, I've got an AoA -- I just want ... an A!

Nobody says perl looks like line-noise any more
kids today don't know what line-noise IS ...

Replies are listed 'Best First'.
Re^3: Why a reference, not a hash/array?
by naikonta (Curate) on Jul 07, 2007 at 04:51 UTC
    The "fetch*" methods family mostly about returning a row, one at a time. The "*array_ref" indicates that a row will be returned as an array reference, so virtually you get ['value', 'for', 'each', 'column'] instead of ('value', 'for', 'each', 'column'). The "all" part indicates that all that array references will be returned, representing all results match the query. And the whole is bundled in another array reference. So you end up with AoA: 
    $all_rows = [
  [R1C1, R1C2, ..., R1Cn],
  [R2C1, R2C2, ..., R2Cn],
  ...,
  [RnC1, RnC2, ..., RnCn],
];

    [download]
    Doesn't DWIW! If I do this:
    ...

    that is, I've got an AoA -- I just want ... an A!

    Well, AFAIK, Perl is more DWIM rather than DWIW. You may W an A, but by using fetchall_arrayref, you really M AoA. If you really M an A and you only want a single column from each row, then you can use selectcol_arrayref instead. 
    # Table: group
# Columns: id, name
# Values: 1, Admin
#         2, User
#         3, Public

my $sql = 'select * from group';
my $r = $dbh->selectcol_arrayref($sql); # default to first column

# or, if you want the the 'name' column
my @r = @{$dbh->selectcol_arrayref($sql, {Columns=>[2]})}; # we want t
+he 2nd column

# if you know the column name you want in advanced:
my $sql2 = 'select name from group';
my @group_names = @{$dbh->selectcol_arrayref($sql2)};

print Data::Dumper->Dump(
  [$r, \@r, \@group_names],
  [qw(id name group_names)],
);

    [download]
    Output with Data::Dumper: 
    $id = [
        '1',
        '2',
        '3',
      ];
$name = [
          'Admin',
          'User',
          'Public',
        ];
$group_names = [
                 'Admin',
                 'User',
                 'Public',
               ];

    [download]

    Update: (09-07-2007) Fixed typo, corrected syntax to Data::Dumper->Dump() (was Data::Dumper(...)). I know I should just copy/paste instead of retyping. *sighs*

    Open source softwares? Share and enjoy. Make profit from them if you can. Yet, share and enjoy!

[reply]
[d/l]
[select]
      naikonta, you definitely do get extra points for pointing me to that module and that method, neither of which I knew about. Very helpful, and effectively pointing out that I had something of an X-Y problem.

      I do indeed need to select a single column into an array, and of course selectall methods should return AoAs.


      Nobody says perl looks like line-noise any more
      kids today don't know what line-noise IS ...
[reply]
Re^3: Why a reference, not a hash/array?
by GrandFather (Saint) on Jul 07, 2007 at 03:42 UTC

    fetchall_arrayref fetches rows and each row may contain many fields, hence the AoA structure.

    DWIM is Perl's answer to Gödel
[reply]
[d/l]
Re^3: Why a reference, not a hash/array?
by friedo (Prior) on Jul 07, 2007 at 03:19 UTC
    Try: 
    my @data = map { $_->[0] } @{ $sth->fetchall_arrayref };

    [download]
[reply]
[d/l]
Re^3: Why a reference, not a hash/array?
by mojodaddy (Pilgrim) on Jul 07, 2007 at 04:20 UTC
    Something like this:
    use DBIx::Simple;
my @names = $db->query('SELECT name FROM people WHERE id > 5')->flat;

    [download]
    fetches everything into "one big flattened list."
[reply]
[d/l]

In Section Seekers of Perl Wisdom