in reply to Re^2: DBI hashref does not return data in order of query
in thread DBI hashref does not return data in order of query

A couple things I noticed, 1) you are assigning the array reference to the same hash key each iteration through the loop (is this what you want?), 2) pushing the array ref into the hash gives you an anonymous array within an anonymous array, which I don't think is what you want. It might make more sense to assign the row to the key (provided you are using different keys through the loop).

If you just want an array of array references (unless there's a field in the returned row that is unique enough to use as a hash key) you can try: 

my @allrows = ();
while (my $row = $sth->fetchrow_arrayref) {
    push @allrows, $row;
}

[download]

Replies are listed 'Best First'.
Re^4: DBI hashref does not return data in order of query
by hallikpapa (Scribe) on Oct 23, 2007 at 02:57 UTC
    The problem is since they can choose their own data, it's entire possible to not have any unique columns on their own. Trying your example I <anon> element tags again. I am using XML::Simple to create the xml. 
            my @xml_array = ();
    while ( my $row = $sth->fetchrow_arrayref ) {
        push @xml_array, $row;
    }

    my $cgi = CGI->new;
    print $cgi->header('text/xml');
    print Dumper( \@xml_array );    
    my $testxml = XMLout( \@xml_array, NoAttr => 1, RootName => 'datas
+et', );
    print $testxml;

    [download]
    In another section, I use XML::Generator, like this. Maybe somehow I can loop through so the right amount of columns are created in this hash, and it's generated on it's own. UNLESS it's possible to get the column names in the element tags using the above array solution, I just can't see it. :( 
    $xml->cdr(
            $xml->Date($date_time),
            $xml->IngressTG($orig_gw),
            $xml->EgressTG($term_gw),
            $xml->Dialed($call_dtmf),
            $xml->DUR($duration),
            $xml->PDD($hold_time),
            $xml->ISDN($isdn),
            $xml->CallType($call_type),
            $xml->Link("URL")
          );

    [download]
[reply]
[d/l]
[select]
      I think one of the other posters mentioned a way to get the column names (outside of using the fetchrow_hashref) but, if the query is static you can just create an array of column names and create your own hash, since the fetchrow_arrayref returns data in the order of the SQL select column order. Maybe, 
      my @cols = ('field1','field2','field3');      

while ( my $row = $sth->fetchrow_arrayref ) {
    # create a hash ref to assign values to cols
    my $tmp = {};
    @$tmp{@cols} = @$row;                     
    push @xml_array, $tmp;                    
}
# Access fields like so...
print $xml_array[0]->{field1},"\n";

      [download]
      I've used this quite a bit in code where I'm reading in files which don't have the column names in them as a header. This should get you a hash per record which sounds like what you want.
[reply]
[d/l]
        It was a misunderstanding on my part. Here is my whole function, and I get an error about coercing an array into a hash. 
                my @names = @{ $sth->{NAME} };
    my @ordered_values = ();
    my %xml_hash = ( dataset => [] );
    while ( my $row = $sth->fetchrow_arrayref ) {
        @ordered_values = @{$row}{@names};  <----Can't coerce array in
+to hash HERE
    }

    my $cgi = CGI->new;
    print $cgi->header('text/xml');    
    my $testxml = XMLout( \@ordered_values, NoAttr => 1, RootName => '
+dataset', );
    print $testxml;

        [download]
[reply]
[d/l]

In Section Seekers of Perl Wisdom