Perl DBI fetchall_hashref

Question

Consider the following table:

mysql> select * from vCountryStatus;
+-------------+------------+------+---------+--------+-----------------+
| CountryName | CountryISO | Code | Status  | Symbol | CurrencyName    |
+-------------+------------+------+---------+--------+-----------------+
| Brazil      | BR         |   55 | LIVE    | BRL    | Brazilian Real  |
| France      | FR         |   33 | offline | EUR    | Euro            |
| Philippines | PH         |   63 | LIVE    | PHP    | Philippino Peso |
+-------------+------------+------+---------+--------+-----------------+
3 rows in set (0.00 sec)

I am trying to construct a hash based on this table. For this I do the following:

#!/usr/bin/perl

use DBI;
use Data::Dumper;

my $dbh = DBI->connect("dbi:mysql:database=db", "user", "password", {RaiseError => 1, AutoCommit => 0, FetchHashKeyName => "NAME_lc"}) || die "DB open error: $DBI::errstr";

my $sth = $dbh->prepare("select * from vCountryStatus");
$sth->execute;
my $hash = $sth->fetchall_hashref('countryiso');
print Dumper($hash);

Here is the output this generates:

$VAR1 = {
          'PH' => {
                    'symbol' => 'PHP',
                    'status' => 'LIVE',
                    'countryname' => 'Philippines',
                    'countryiso' => 'PH',
                    'currencyname' => 'Philippino Peso',
                    'code' => '63'
                  },
          'BR' => {
                    'symbol' => 'BRL',
                    'status' => 'LIVE',
                    'countryname' => 'Brazil',
                    'countryiso' => 'BR',
                    'currencyname' => 'Brazilian Real',
                    'code' => '55'
                  },
          'FR' => {
                    'symbol' => 'EUR',
                    'status' => 'offline',
                    'countryname' => 'France',
                    'countryiso' => 'FR',
                    'currencyname' => 'Euro',
                    'code' => '33'
                  }
        };

The question is: why is the key of the hash (countryiso) repeated in the values inside the hash?

What I would prefer is the following output:

$VAR1 = {
          'PH' => {
                    'symbol' => 'PHP',
                    'status' => 'LIVE',
                    'countryname' => 'Philippines',
                    'currencyname' => 'Philippino Peso',
                    'code' => '63'
                  },
          'BR' => {
                    'symbol' => 'BRL',
                    'status' => 'LIVE',
                    'countryname' => 'Brazil',
                    'currencyname' => 'Brazilian Real',
                    'code' => '55'
                  },
          'FR' => {
                    'symbol' => 'EUR',
                    'status' => 'offline',
                    'countryname' => 'France',
                    'currencyname' => 'Euro',
                    'code' => '33'
                  }
        };

Is it possible using fetchall_hashref DBI method? Or do I have to go the traditional way, looping through each row and constructing the hash on the fly?

Speechmaking

FYI, the prepare, execute, and fetchall_hashref calls can be collapsed into a single selectall_hashref call, like this: my $hash = $dbh->selectall_hashref("select * from vCountryStatus", 'countryiso'); – Dioecious

if there is another row with the CountryISO value as PH then will both rows be printed???if not then how to print all the rows having same key?? – Holophrastic

Answer 1

No, it cannot be done using fetchall_hashref. But you can iterate over the hash values and delete the key:

delete $_->{countryiso} for values %$hash;

Elka

Thanks, that's what I was expecting. Not a big deal, as you said I can either delete them or another solution would be to ignore them as the extra memory overhead (in my case) would be very small. – Speechmaking

if there is another row with the CountryISO value as PH then will both rows be printed???if not then how to print all the rows having same key??will while loop be used?? – Holophrastic

Answer 2

I had this same problem but was using multiple keys on fetchall_hashref, so I had to go deeper in the hash references. Not exactly rocket science, but here it is:

(...)          
           my @keys=('key1','key2','key3');
           my $result_ref=$sth->fetchall_hashref(\@keys);

           remove_key_values($result_ref,\@keys);
(...)


sub remove_key_values {
    my ($href_values,$aref_keys) = (@_);

    foreach my $hk (keys %$href_values) {
        foreach my $ak (@$aref_keys) {
            if ($ak eq $hk) {
                delete $href_values->{$hk};
            }
        }
        if (exists $href_values->{$hk} and ref($href_values->{$hk}) eq 'HASH') {
                remove_key_values($href_values->{$hk},$aref_keys);
        }
    }
}

Edom