MySql - push a hash into an array

[John]

Hi

I am reading a row of hashes from a MySQL table.
I need to add that row to an array to create an 'array of hashes'
I should then be able to access, say, $xyz[34]{'steel'}.

The following is part of the code:

my @xyz=({}); # array of hashes

$sql="SELECT coal,iron,steel,china,wool,cloth,fish,livestock FROM industry";
$sth=$dbh->prepare($sql);
$sth->execute or die "Unable to execute query: $dbh->errstr\n";

my $row;
while ($row = $sth->fetchrow_hashref) {push (@xyz,$row)}

$sth->finish();
$dbh- >disconnect();

print $xyz[6]{'cloth'};

The print statement is empty. Any ideas?

Regards
John

 

[Paul Lalli]

I am reading a row of hashes from a MySQL table.
I need to add that row to an array to create an 'array of hashes'
I should then be able to access, say, $xyz[34]{'steel'}.

The following is part of the code:

my @xyz=({}); # array of hashes

There is no need for that initialization. Indeed, it will hurt you in
the long run. You are specifically adding one completely empty hash
reference to your array. Therefore, when all is said and done, your
array will have one more hash than the number of rows in your data set.
Change to:
my @xyz;

$sql="SELECT coal,iron,steel,china,wool,cloth,fish,livestock FROM industry";
$sth=$dbh->prepare($sql);
$sth->execute or die "Unable to execute query: $dbh->errstr\n";

my $row;

while ($row = $sth->fetchrow_hashref) {push (@xyz,$row)}

$sth->finish();
$dbh- >disconnect();

print $xyz[6]{'cloth'};

The print statement is empty. Any ideas?

Well the first and most obvious is: Are you sure that the statement
handler returned at least 7 rows? The second and second-most obvious
is: Are you sure that the 7th row's value for the 'cloth' column was
neither NULL nor the empty string?

What do you get for these two lines?

use Data:umper;
print Dumper(\@xyz);

Finally, are you aware that DBI has a means by which to do this
already?
my @xyz = @{ $sth->fetchall_arrayref( { } ) };

Paul Lalli

 

[John]

Thank you, gentlemen.

I can now see my mistake.

It's proably better to use the fetchall construct.

Thanks again and regards
John