Array of Hashes Issue

Spidy has asked for the wisdom of the Perl Monks concerning the following question:

Greetings, fellow monks.

For a recent project, we need to retrieve every question, it's single correct answer, and all of the wrong answers associated with it. This is the code that we have used to do this, and store it into an array of hashes:

    my @questions;
    my %pending;
    $sth = $dbh->prepare("SELECT id, question FROM questions");
    $sth->execute();
    $sth->bind_columns(\@pending{qw(id question)});
    while($sth->fetch) {
        my $sth = $dbh->prepare("SELECT answer, is_correct FROM answer
+s WHERE to_question = ?");
        $sth->execute($pending{id});
        $sth->bind_columns(\@pending{qw(answer is_correct)});
        while($sth->fetch) {
            if($pending{is_correct}) {
                $pending{correct_answer} = $pending{answer};
            } else {
                ## Add to array of wrong answers
                push @{$pending{wrong}}, $pending{answer};
            }
        }
        push @questions, %pending;
    }
    return @questions;
[download]

However, upon retrieving the returned array within my caller script, I am unsure as to how I'm supposed to loop through this array. Can anyone give me some pointers in the right direction?

Thanks,
Spidy

My Website
Get Eated!

Comment on Array of Hashes Issue Download Code

Replies are listed 'Best First'.
Re: Array of Hashes Issue by bobf (Monsignor) on Mar 03, 2007 at 06:49 UTC
If you're not sure what a data structure looks like, you can use modules like Data::Dumper or Data::Dump::Streamer (see How can I visualize my complex data structure? for examples). If you're not sure how to navigate through it, perldsc has some good information. perlref and perlreftut may also help if you are not familiar with references. That said, it looks like you've got a few problems in your code. When simplified, it looks like this: `my @questions; my %pending; while($sth->fetch) { while($sth->fetch) { if($pending{is_correct}) { $pending{correct_answer} = $pending{answer}; } else { ## Add to array of wrong answers push @{$pending{wrong}}, $pending{answer}; } } push @questions, %pending; } return @questions;` [download] First, it looks like you're overwriting `$pending{correct_answer}` each time, and all of the wrong answers are ending up in the same array (`@{$pending{wrong}}`). If I can read your intent correctly, I think you need something like `$pending{$id}{correct_answer}` and `@{$pending{$id}{wrong}}`. Second, you flatten the `%pending` hash when you push it onto `@questions`. If you really want to use an array (see below), try pushing a hash reference instead: `push( @questions, \%pending )` (note that this would require you to tighten the scope of `%pending`). Third, you are returning an array, but an array reference might be better: `return \@questions;` Finally, I think you might want to reconsider the type of data structure that you are creating and returning. Something like the following might work: `%hash = ( $question_id => { correct_answer => $whatever, wrong_answers => [ $oops, $my_bad, ... ] },` [download] A complex data structure like this should be returned as a reference: `\%hash`. Update 1: Wow, am I slow tonight. Update 2: As we discussed in the cb, 1) you may also want to consider using a join and combining your queries into a single SQL statement and 2) the DBI `fetchall_*ref` methods may help you simplify the code. HTH	[reply] [d/l] [select]
Re: Array of Hashes Issue by GrandFather (Saint) on Mar 03, 2007 at 06:39 UTC
Before you get to using the data from the array you better figure out if what you have is what you expect. Consider: `use strict; use warnings; use Data::Dump::Streamer; my @questions; my %pending; for (qw(first second)) { $pending{answer} = $_; push @questions, %pending; } Dump \@questions;` [download] Prints: `$ARRAY1 = [ 'answer', 'first', 'answer', 'second' ];` [download] Probably `my @questions; for (qw(first second)) { my %pending; $pending{answer} = $_; push @questions, \%pending; } Dump \@questions;` [download] which prints: `$ARRAY1 = [ { answer => 'first' }, { answer => 'second' } ];` [download] is closer to what you might expect? Take note of the fact that the declaration for %pending is inside the loop so a new instance of the hash is created each time through the loop. DWIM is Perl's answer to Gödel	[reply] [d/l] [select]
Re: Array of Hashes Issue by thezip (Vicar) on Mar 03, 2007 at 06:34 UTC
Seems like the stuff I was looking at today... An array of 2d arrays: `$arr->[n]->[0] is the correct answer $arr->[n]->[1] is a reference to an array of wrong answers` [download] Try using Data::Dumper to display the the root reference, and you should see what I'm talking about... Where do you want them* to go today?*	[reply] [d/l]
Re^2: Array of Hashes Issue by GrandFather (Saint) on Mar 03, 2007 at 06:44 UTC
Note that a little syntactic sugar in Perl allows `$arr->[n][0]` rather than `$arr->[n]->[0]`. DWIM is Perl's answer to Gödel	[reply] [d/l] [select]
Re: Array of Hashes Issue by jmmistrot (Sexton) on Mar 05, 2007 at 09:12 UTC
Do you mean something like this? Writing this off the cuff here so mind your p's and q's :) `foreach my $q (@questions){ my %p = %{$questions[$q]};# copy to be more clear here.. foreach $entry (keys %p){ print "$q : $p{$entry}\n"; } }` [download]	[reply] [d/l]