my $addr_lookup = 
    'SELECT u2addr.id FROM u2addr WHERE u2addr.addr = ?';
my $stm = $dbh->prepare($addr_lookup);

foreach my $addr (@addr)
{

    $stm->execute($addr);
    
    my @results=();
    
    while (my @row = $stm->fetchrow_array)
    {
        push (@results, [@row]); #push array ref to @row
                                 #using new memory and copy of @row
                                 #could also just fetchrow_arrayref
    }
    
    ## do something with @results here...
    ## perhaps: "print Dumper \@results;"
    ## to see what you have in @results?
}
[download]

I know the code I'm using is primitive but what am I missing here?

1) you're not using placeholders, and you're probably not using quote( see http://bobby-tables.com/ )

2) and you're preparing way too much (prepare once outside of loop, then ->execute($addr); inside loop )

The situation you describe is hard to believe, so you're better off using Data::Dump::dd for debugging instead of print

Hello Toppo, and welcome to the Monastery!

Two quick observations:

1. @row=();
   while (my @row = $stm->fetchrow_array){
   [download]

   The my in the while loop creates a new @row variable each time through the loop. This is almost certainly not what you wanted. Update: You don’t want both! See the comment by Anonymous Monk, below.

2. the array @results don't appear to have an index

   I don’t know what you’re trying to say here, but be assured that every element in every array has an index! See e.g. perldata.

I think more information is required before the monks can help you. Please provide a minimal but complete, working script that demonstrates the problem, along with sample input, and a description of the desired output.

HTH,

The my in the while loop creates a new @row variable each time through the loop. This is almost certainly not what you wanted.

Actually it most certainly is -- the @row=(); is what is an error -- probably because OP isn't using strict and warnings

He said the program runs under strict and warnings.

But the @row = () and the my @row in the while loop are two distinct variables. Since he is not using the first @row, it does not seem to be the cause of his problems.

CountZero

A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

My blog: Imperial Deltronics

my @results;
my $hr_addresses = $dbh->selectall_hashref("SELECT addr, id FROM u2add
+r", "addr");
if (!defined $hr_addresses) { die("Error executing query: " . $dbh->er
+rstr); }
foreach my $addr (@addr) {
    if (defined $hr_addresses->{$addr}) {
        push(@results, $hr_addresses->{$addr})
    } else {
        print("Error, ID of address [$addr] not found\n");
    }
}
[download]

Unless you made some changes to the array of addresses, it should be possible to fetch the adresses and their IDs in one select statement. That will really speed-up your program.

When using databases, it is my experience that it is almost always "best" (= faster or less memory use or easier) to let the database do all the heavy lifting and only reserve the really difficult parts (like formatting or calculating or transforming the data) for your program.

All arrays have an index, so that's not the problem here. Other people have given you good advice about using placeholders, moving your prepare statement outside your loop, etc. But to narrow down your problem, try adding some debugging statements, like this:

  print "Fetching values for $addr\n";
  while (my @row = $stm->fetchrow_array){
    print "  Got values: ", join( ':', @row ), "\n";
    push (@results, @row);
  }
  $stm->finish;
  print "Finished getting values, printing \@results for $addr:\n";
  print join ':', @results;
  print "\n\n";
[download]

try adding some debugging statements, like this:

No :) use Data::Dump / Data::Dumper... for visualizing data structures

I use those a lot, to the point where I'm thinking of putting 'use Data::Printer' in the blank template I use to start a new Perl script, since I always end up adding it during testing anyway. But in this case, there are two reasons I didn't:

• Those modules print several lines to show a single value. That's fine to display a single structure once at a certain point, but to do it repeatedly in a loop can give you a heap of output that would be difficult for a newbie to parse through.
• I have a feeling his real problem is with program flow. I think he's going to discover that where he thinks he's seeing the whole array printed, he's really seeing one element of the array printed each time through the loop, but with nothing else being printed between them, they look like one output. To see that, he doesn't need to see the structure of @results just once; he needs to see how his program is flowing and how that array is changing with each step.

So in this case, in my opinion, changing my print/join statements to 'print Dumper @results' could make it harder for him to see the problem.

Aaron B.
Available for small or large Perl jobs; see my home node.