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.

Solution 1

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});
}

Solution 2

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.

Comments

[TLP]

Short method to extract values from that datastructure my @vals = map values %$_, @$list

[BluGeni]

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]

@chankey how did you even spot that? :D

[Chankey Pathak]

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

[achahbar]

how can you make the ID_NUMBER the max amount of variables that you have in your array?

[simbabque]

@achahbar I don't understand your question. What do you want to do exactly?

[simbabque]

@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]

@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.