building a hash from a database

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

I am trying to build a hash depending on the in a database table that contains a 1 or 0 depending if there is data for a given element.

Given the following input table:

id    fname    lname
1     Joe      Smith
2     Sally    Smith
[download]

Here is the code I am using:

use DBI;
$dbh = DBI->connect("DBI:CSV:f_dir=./");
$query = "select id, fname, lname";
$sth->prepare($query);
$sth->execute();
while ($rowdata = $sth->fetchrow_hashref) {
    $hash{$rowdata->{'id'}} = $rowdata{'id'};
    if ($rowdata->{'fname'}) { $hash{$rowdata->{'id'}}{'fname'} = 1; }
    if ($rowdata->{'lname'}) { $hash{$rowdata->{'id'}}{'lname'} = 1; }
}

print Dumper(\%hash);
[download]

Yet, I do not get the expected results. I get the following:

$VAR1 = {
          '' => undef
        };
[download]

I would expect to see something like the following (constructed my myself and may or may not be valid):

$VAR1 = {
            {
              '' => 1
              'fname' => 'John'
              'lname' => 'Smith'
            {
            {
              '' => 2
              'fname' => 'Sally'
              'lname' => 'Smith'
            {
        };
[download]

Update: Changed all references to $rowdata{} to $rowdata->{}.

Comment on building a hash from a database Select or Download Code

Replies are listed 'Best First'.
Re: building a hash from a database by bart (Canon) on May 02, 2006 at 15:34 UTC
(update First of all, $rowdata contains a hashref, that's not the same as %rowdata. You have to use `$rowdata->{'id'}` instead of `$rowdata{'id'}`. And now, continuing with the rest of the story, assuming that is fixed...) `use strict;`!!! You're doing this in two steps. As a result of the line `$hash{$rowdata->{'id'}} = $rowdata->{'id'};` [download] `$hash{$rowdata->{'id'}}` has an integer for a value, and you're using that as a symbolic reference to a new hash. `use strict` would have prevented you from doing that. I think you can change the code to do what you want, by changing that line to `$hash{$rowdata->{'id'}} = { '' => $rowdata->{'id'} };` [download]	[reply] [d/l] [select]
Re^2: building a hash from a database by bfdi533 (Friar) on May 02, 2006 at 15:42 UTC
Hmmm... I must admit (if it is not obvious) that there a number of things about hashes that I do not understand. What do you mean by symbolic reference to a new hash? I have made the change that you suggested and now I get this: `$VAR1 = { '' => { '' => undef } };` [download] What I need to be able to do later is to reference the has created like this: `foreach $key (keys %hash) { print $key, $key{'fname'}, $key{'lname'}, "\n"; }` [download]	[reply] [d/l] [select]
Re^3: building a hash from a database by bart (Canon) on May 02, 2006 at 15:44 UTC
You're still making the mistake of confusing `$rowdata->{$field}` with `$rowdata{$field}`. I fixed that in my code with an update, I admit I only spotted that later. So there was more than one thing wrong with the code. :) What do you mean by symbolic reference to a new hash? Well, first of all, you're setting the value of `$hash{$id}` to, say, 1. And then you set `$hash{$id}{'fname'}` to "foo". As a result, you really are trying to set `$1{'fname'}` to "foo". What I need to be able to do later is to reference the has created like this: `foreach $key (keys %hash) { print $key, $key{'fname'}, $key{'lname'}, +"\n"; }` [download] No way, but you will be able to use `foreach $key (keys %hash) { print $key, $hash{$key}{'fname'}, $hash{$key}{'lname'}, "\n"; }` [download]	[reply] [d/l] [select]
Re^4: building a hash from a database by bfdi533 (Friar) on May 02, 2006 at 16:20 UTC
Re: building a hash from a database by zer (Deacon) on May 02, 2006 at 15:32 UTC
DBIx::Lookup::Field - Create a lookup hash from a database table	[reply]
Re^2: building a hash from a database by bfdi533 (Friar) on May 02, 2006 at 15:35 UTC
That is not exactly what I am looking for. I need to basically create a has with each of the input columns indexed by id that has a 1 or 0 in the field depending on whether the database field is null or if it has data. That code just facilitates repeated lookup on the database for a given input column. Thanks for the reply, though.	[reply]
Re: building a hash from a database by Codon (Friar) on May 02, 2006 at 16:10 UTC
How big of a CSV are we looking at here? If you have the memory, you could simplify with `$sth->fetchall_arrarref({})` which would give you something like this: `$VAR1 = [ { 'id' => 1, 'fname' => 'John', 'lname' => 'Smith', }, { 'id' => 2, 'fname' => 'Sally', 'lname' => 'Smith', }, ];` [download] Then with your friend map, you can massage this structure thusly: `my %hash = map { $_->{'id'} => $_ } @$data;` [download] which would result in something along the lines of `$VAR2 = { '1' => { 'id' => 1, 'fname' => 'John', 'lname' => 'Smith', }, '2' => { 'id' => 2, 'fname' => 'Sally', 'lname' => 'Smith', }, };` [download] Ivan Heffner Sr. Software Engineer, DAS Lead WhitePages.com, Inc.	[reply] [d/l] [select]
Re: building a hash from a database by runrig (Abbot) on May 02, 2006 at 15:44 UTC
fetchrow_hashref(), like the name suggests, returns a hash reference (i.e. a reference to a hash). You are accessing it as if it were a hash. As bart suggests, if you Use strict and warnings, you would be aware of this error. Hash vs. hashref: `my %hash = (id => "123", fname => "Fred); my $hashref = \%hash; print "$hash{id}\n"; print "$hashref->{id}\n";` [download]	[reply] [d/l]
Re: building a hash from a database by duff (Parson) on May 02, 2006 at 15:35 UTC
You've used `fetchrow_hashref` but you're treating the value you get from it as if it were a hash rather than a hashref. Use `$rowdata->{id}` instead of `$rowdata{id}`. (Use `->` everywhere you mention `$rowdata`) `use strict` would have caught this for you. duff	[reply] [d/l] [select]