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, Oct 18 '13 at 14:35
Short method to extract values from that datastructure `my @vals = map values %$_, @$list` — TLP, Oct 18 '13 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, Oct 18 '13 at 17:21

score 6 · Accepted Answer · edited Aug 29 '16 at 12:29

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 '16 at 12:29

Chankey Pathak

21,187
12
85
133

answered Oct 18 '13 at 14:31

simbabque

53,749
8
73
136

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. – BluGeni Oct 18 '13 at 14:35
@chankey how did you even spot that? :D – simbabque Aug 29 '16 at 12:36
@simbabque: Haha! Just searched 'fetchall_arrayref' on Google and this appeared. – Chankey Pathak Aug 29 '16 at 12:40
how can you make the ID_NUMBER the max amount of variables that you have in your array? – achahbar May 04 '18 at 11:57
@achahbar I don't understand your question. What do you want to do exactly? – simbabque May 04 '18 at 11:58
>>>>$sth2->execute($res->{ID_NUMBER}); here you need to manually put the id_number in. But if you want to loop through all id numbers , you have to somehow do i++ ; iexecute($res->{i}); but I can't find the rowsize(the max amount of i's to use it in i – achahbar May 04 '18 at 12:05
1

@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 May 04 '18 at 12:07
1

@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. – simbabque May 04 '18 at 12:10
Ow i thought you loop through the rows . because i have the same problem. but when i print the row in the fetchall_arrayref. and loop through the rows en print the values independently it got an array out of index. Can you take a look at my stackoverflow question that you got an idea what i mean and what my problem is ? https://stackoverflow.com/q/50174145/6351302 – achahbar May 04 '18 at 12:12

score 1 · Answer 2 · answered Oct 18 '13 at 15:29

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.

score 0 · Answer 3 · answered Oct 18 '13 at 16:18

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 '13 at 16:18

ikegami

367,544
15
269
518

foreach value in fetchall_arrayref

3 Answers3