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

Hi Monks, I have a multi level hash from a db call of employees details. I can print it out and am working on querying specific employees. Problem is that when I query one, I get all records returned...I just want to return "Gay" last name.. How can I return just one record from the final if statement. I think its because its in the foreach Please help wise monks.. 

#!/opt/local/bin/perl

use DBI;
use DBD::mysql;
use Data::Dumper;;
use strict;
my %names=();
my $driver = "mysql";
my $database = "employees";
my $dsn = "DBI:$driver:database=$database";
my $userid = "hungrysumo";
my $password = "";

my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;


my $sth = $dbh->prepare("SELECT *
                        FROM employees"
                        );
$sth->execute() or die $DBI::errstr;
print "Number of rows found :" + $sth->rows;
while (my @row = $sth->fetchrow()) {
$names{$row[0]}{$row[1]} = [$row[2],$row[3],$row[4],$row[5]];#print Du
+mper \%names;
}
$sth->finish();

for my $key (keys %names){
#print "Employee Number:$key\n"; 
for my $key2(keys $names{$key}){
#print "Birth Date:$key2\n";
print "First Name:".$names{$key}{$key2}[0], "\n";
#print "Last Name:".$names{$key}{$key2}[1], "\n";
#print "Gender:".$names{$key}{$key2}[2], "\n";
#print "Hire Date:",$names{$key}{$key2}[3],"\n";

if ($names{$key}{$key2}[0] = 'Gay'){
print "Last Name:".$names{$key}{$key2}[1]."\n";
}
}
}

[download]
  • Comment on multi level hash terror - if statement to access specific elements
  • Download Code

Replies are listed 'Best First'.
Re: multi level hash terror - if statement to access specific elements
by Athanasius (Archbishop) on Feb 13, 2016 at 04:45 UTC

    Hello hungrysumo79, and welcome to the Monastery!

    In addition to the problem identified by beech — using an assignment (=) where a comparison (== or, in this case, eq) is needed — there is a serious problem in the way the hash %names is populated. These lines:

    while (my @row = $sth->fetchrow()) {
    $names{$row[0]}{$row[1]} = [$row[2],$row[3],$row[4],$row[5]];
}

    [download]

    overwrite the hash entry for $names{$row[0]}{$row[1]} each time a new entry is found, so in the end only the last entry is retained in the hash. Rather than an assignment, you need to push each entry onto an anonymous array:

    while (my @row = $sth->fetchrow())
{
    push @{ $names{ $fields[0] }{ $fields[1] } }, [ @fields[2 .. 5] ];
}

    [download]

    (Note the use of an array slice to reduce the amount of typing required.) This adds another level to the data structure, so you need to be careful when dereferencing. The following example should give you the idea:

    Read more... (2 kB)

    By the way, you should always use warnings. And why do the work yourself when the database can do it for you? Why not change your query to something along these lines?

    my $sth = $dbh->prepare("SELECT *
                        FROM employees
                        WHERE firstname = 'Gay'"
                       );

    [download]

    :-)

    Update: minor edits.

    Hope that helps,

    Athanasius <°(((>< contra mundum Iustus alius egestas vitae, eros Piratica,

[reply]
[d/l]
[select]
      Thank you this helped me so much - I will study this to get it in my head.. thanks so much!
[reply]
Re: multi level hash terror - if statement to access specific elements
by beech (Parson) on Feb 13, 2016 at 01:33 UTC
[reply]
[d/l]
      Thanks but that still prints out the entire hash instead of just the last name of "Gay"
[reply]

        Thanks but that still prints out the entire hash instead of just the last name of "Gay"

        So you want to fix this? 

        for my $key ( keys %names ) {
    for my $key2 ( keys $names{$key} ) {
        
        print "First Name:" . $names{$key}{$key2}[0], "\n";
        
        if ( $names{$key}{$key2}[0] = 'Gay' ) {
            print "Last Name:" . $names{$key}{$key2}[1] . "\n";
        }
    }
}

        [download]

        You want to only print when last name is Gay?

        Its simple, get rid of the other print statement

[reply]
[d/l]
Re: multi level hash terror - if statement to access specific elements
by chacham (Prior) on Feb 15, 2016 at 14:18 UTC

    Side comment: Please do not use SELECT * outside of EXISTS() clauses and ad hoc queries. Specifying the column is not only self-documenting, it also protects against added or removed columns, and possible column reordering.

[reply]

Back to Seekers of Perl Wisdom