Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

Ordering return from fetchrow_hashref

by slayer (Initiate)
on Mar 02, 2004 at 15:42 UTC ( #333280=perlquestion: print w/replies, xml ) Need Help??

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

Oh great monks,

I'm going round in circles on this one. Essentially I want to extract a row from a mysql database, using fetchrow_hashref, but keep the columns in the SAME ORDER as the sql statement. The returned hash seems to be in a completely random order.

I've tried:

  1. Tie a hash, create a reference, then extract the data into that reference:
    my (%row_hash,$row_hash); tie %row_hash, "Tie::IxHash"; my $row_hash = \%row_hash; while ($row_hash = $sth->fetchrow_hashref) {...

    ->This still jumbles the order of the returned hash.

  2. Tie a hash and extract straight in to that hash: while (%row_hash = %{$sth->fetchrow_hashref}) {

    ->This returns a 'Can't use an undefined value as a HASH reference' error

  3. Extract the data into a reference, then tie the hash.

    ->The data is already jumbled and it is too late.

  4. Alter CPANs DBI sub fetchrow_hashref to tie the hash before returning it.

    ->Am incapable. DBI.c seems to order the rows fine...

Anymore suggestions would be greatfully received,

Cheers!

Replies are listed 'Best First'.
Re: Ordering return from fetchrow_hashref
by gmax (Abbot) on Mar 02, 2004 at 17:18 UTC

    tye has shown you one way. However, let me question the reasons for wanting what you are asking

    Hashes are data structures where you can access elements by name rather than by position, as you do with arrays. Therefore a sorted hash is a contradiction. (Although Perl Tie mechanism can change something)

    Now, I see two reasons for wanting the result from a query in a hash:

    • When you want to access your columns by name
    • When you want to process (e.g. display) your column names with their values.

    In the first case, you don't really need a sorted hash, since you are using the hash names hardcoded in your application, so that the application itself will set the order. For example

    while (my $rec = $sth->fetchrow_hashref()) { print $rec->{xy}, $rec->{xz}, $rec->{yz}; }

    If you need to process your columns according to a list of column names, here is a slight variation on tye's answer:

    while (my $row = $sth->fetchrow_hashref()) { print map( { "$_ => $row->{$_}\t"} @{$sth->{NAME}}), "\n"; } #or while (my $row = $sth->fetchrow_hashref()) { for (@{$sth->{NAME}}) { print "$_ => $row->{$_}\t"; } print "\n"; }

    If you just want to use the column names together with their values, then you can use two different methods, according to your needs:

    while (my $row = $sth->fetchrow_arrayref()) { # arrayref, not hash my $index = 0; print "$_ => ", $row->[$index++], "\t" for (@{$sth->{NAME}}); print "\n"; } # or the "C like" way while (my $row = $sth->fetchrow_arrayref()) { # arrayref, not hash for (my $index = 0; $index < $sth->{NUM_OF_FIELDS}; $index++) { print "$sth->{NAME}->[$index] => ", $row->[$index], "\t" } print "\n"; }

    For more ideas, tips, and caveats on the same subject, see DBI Recipes.

    Update
    If, as you say, you need an array of hash references, then it would be much easier this idiom:

    my $aref = $sth->fetchall_arrayref({});

    Notice the empty hashref passed as argument.

     _  _ _  _  
    (_|| | |(_|><
     _|   
    
Re: Ordering return from fetchrow_hashref ({NAME})
by tye (Sage) on Mar 02, 2004 at 15:54 UTC
Re: Ordering return from fetchrow_hashref
by eric256 (Parson) on Mar 02, 2004 at 15:50 UTC
    If order is what you want then you prob should do fetchrow_arrayref instead of hashref. Just my two cents, I would assume the only reason you care about order is because you want to display it out agian looking like the same table. The alternative would be to just have an array of the columns in the order you want to display them.

    ___________
    Eric Hodges
Re: Ordering return from fetchrow_hashref
by rdfield (Priest) on Mar 02, 2004 at 15:52 UTC
    Hashes are random. Use fetchrow_array to preserve order.

    rdfield

Re: Ordering return from fetchrow_hashref
by slayer (Initiate) on Mar 04, 2004 at 16:44 UTC

    Yes, you were quite right, the reason I needed this order is that I will be displaying the data directly from the database to autogenerate a status report, with little user intervention and with the column order derived from the sql query.

    Also, other people are ultimately relying on me to return an array of hash references, so using array ref and converting would be messier.

    The solution you suggested worked perfectly, I've implemented the following routine and it works exactly as it should:

    sub read_from_db { my %params = @_; my $dbh = $params{-dbh} || ""; my $query = $params{-query} || ""; my @results_array; if ($dbh =~ /DBI/) { my $sth = $dbh->prepare($query); my $result = $sth->execute(); while (my $row = $sth->fetchrow_hashref()) { tie my(%hash_to_return), "Tie::IxHash"; map({$hash_to_return{$_}=$row->{$_}} @{$sth->{NAME}}); my $ref = \%hash_to_return; push(@results_array, $ref); } $sth->finish; } return @results_array; }

    So thanks...!

      The other way to do is ... we can get all the column headers using $sth->{NAME} array. get the values using $sth->fetchrow_array() - This would give column values in an order. @{$sth->{NAME}} - This would give column names in an order.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://333280]
Approved by b10m
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (5)
As of 2019-11-14 04:07 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Strict and warnings: which comes first?



    Results (76 votes). Check out past polls.

    Notices?