Using Perl and DBI to get data from SQL database. I am using the code below to get data from a table. Instead of defining 3 different arrays, is there a way to define an array with multiple fields?
$sql = "select * from tblPeople where PersonID=?";
$sth = $dbh->prepare($sql);
$sth->execute($PID);
while ($DRow = $sth->fetchrow_hashref) {
push @KnownPIDs, $DRow->{PersonID};
push @Knownoss, $DRow->{currentpos};
push @Knownnotes, $DRow->{personnotes};
}
What I would do, is use the keys of the DRow hash as keys for a new HoA hash, and dynamically make each value of HoA an array.
#!/usr/bin/perl
use Data::Dumper;
my %HoA; # hash of arrays
my $DRow = {};
# set up some example data
$DRow->{PersonID} = 'steve';
$DRow->{currentpos} = 'one';
$DRow->{personnotes} = 'This is not my suicide note';
# by using a Hash of Arrays (HoA), we can dynamically build
# the entire structure without having to manually type everything
# out for each row item
for my $key (keys(%{ $DRow })){
push @{ $HoA{$key} }, $DRow->{$key};
}
# to access the 'PersonID' of say the third row that was processed:
# my $pid = $HoA{PersonID}->[2];
print Dumper \%HoA;
Output:
$VAR1 = {
'personnotes' => [
'This is not my suicide note'
],
'PersonID' => [
'steve'
],
'currentpos' => [
'one'
]
};
