foreach value in fetchall_arrayref

Question

I am trying to do a foreach loop for each value in my fetchall_arrayref and am having a bit of trouble.

I have:

my $list = $sth->fetchall_arrayref({});
print Dumper($list);

which gives me:

$VAR1 = [
          {
            'ID_NUMBER' => '123'
          },
          {
            'ID_NUMBER' => '456'
          },
          {
            'ID_NUMBER' => '5666'
          },
          {
            'ID_NUMBER' => '45645'
          },
          {
            'ID_NUMBER' => '23422'
          }
        ];

I am not sure how to format my foreach loop print each id_number's value. Eventually I want to run a query with each value but I can figure that out once I get this working.

Thanks for any help.

Why are you passing an empty hash ref {} to fetchall_arrayref? That function takes a different set of arguments. — TLP
– TLP, Commented Oct 18, 2013 at 14:35
Short method to extract values from that datastructure my @vals = map values %$_, @$list — TLP
– TLP, Commented Oct 18, 2013 at 14:47
TLP: the first parameter is the Slice; if it's a hashref, each row will be returned as a hash instead of an array. — ysth
– ysth, Commented Oct 18, 2013 at 17:21

Chankey Pathak · Accepted Answer · 2016-08-29 12:29:20Z

6

You should use fetchrow_hashref instead, and do each one individually. That will make it a lot more readable, and it does not affect performance form a database point of view.

while (my $res = $sth->fetchrow_hashref) {
  print Dumper $res;
  $sth2->execute($res->{ID_NUMBER});
}

If you wanted to do it with the fetchall_arrayref, it would work like this:

my $list = $sth->fetchall_arrayref({});
foreach my $res (@{ $list }) {
  $sth2->execute($res->{ID_NUMBER});
}

edited Aug 29, 2016 at 12:29

Chankey Pathak

21.8k12 gold badges88 silver badges138 bronze badges

answered Oct 18, 2013 at 14:31

simbabque

54.4k8 gold badges77 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

BluGeni Over a year ago

Oh this is perfect thanks for the other method and I see what you mean. Makes a lot more sense and is easier to understand.

simbabque Over a year ago

@chankey how did you even spot that? :D

Chankey Pathak Over a year ago

@simbabque: Haha! Just searched 'fetchall_arrayref' on Google and this appeared.

simbabque Over a year ago

@achahbar no, you misunderstand. $res->{ID_NUMBER} means take the value that's in the column named ID_NUMBER in the database. There is nothing manual to do here. The code fetches a few rows of ids from one table, and then goes through each row. It makes another query for each of them.

simbabque Over a year ago

@achahbar run this code in a fresh Perl file, you'll see what my example does:

my @rows=({ID_NUMBER => 1}, {ID_NUMBER => 2}, {ID_NUMBER => 3}); foreach my $res (@rows) { print "SELECT foo FROM bar WHERE id=$res->{ID_NUMBER}\n"; }

– this is a simplified version of the program above.

|

David W. · Accepted Answer · 2013-10-18 15:29:02Z

What you have is a reference to an array of hash references.

Let's break this down:

$list is a _reference to an array. I I can _dereference it by putting the right dereferencing symbol before it. You can get the array by using @$list or @{ $list }. I like the latter one because it makes the fact it's a reference very clear.

for my $row_ref ( @{ $list } ) {
    here be dragons...    # We'll figure this out later...
}

The $row_ref is a reference to a hash. We can again get the hash by putting the % in front: %{ $row_ref }. Thus, we can use %{ $row_ref }{$key} to get the value. However, that syntax is hard to read. We can use the alternative -> which looks like this: $row_ref->{$key}. That's much easier to see. We also know that there's one key and the key is ID_NUMBER:

for my $row_ref ( @{ $list } ) {
    print "ID Number: " . $row_ref->{ID_NUMBER} . "\n";
}

Or, if you weren't sure of the column names:

for my $row_ref ( @{ $list } ) {
    for my $column ( sort keys %{ $row_ref } ) {
       print "$column: " . $row_ref->{$column} . "\n";
    }
}

If you're not familiar with references, read the Perl tutorial.

ikegami · Accepted Answer · 2013-10-18 16:18:25Z

0

You have an array of hashes. It helps if you use descriptive variable names.

my $rows = $sth->fetchall_arrayref({});;
for my $row (@$rows) {
   print($row->{ID_NUMBER}, "\n");
}

answered Oct 18, 2013 at 16:18

ikegami

391k17 gold badges291 silver badges555 bronze badges

Collectives™ on Stack Overflow

foreach value in fetchall_arrayref

3 Answers 3

9 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related