Using fetchrow_hashref to store data

Question

I am trying to extract information from a MySQL database, which I will manipulate in perl:

use strict; use DBI; my $dbh_m= DBI->connect("dbi:mysql:Populationdb","root","LisaUni") or die("Error: $DBI::errstr"); my $Genotype = 'Genotype'.1; #The idea here is eventually I will ask the database how many Genotypes there are, and then loop it round to complete the following for each Genotype: my $sql =qq(SELECT TransNo, gene.Gene FROM gene JOIN genotypegene ON gene.Gene = genotypegene.Gene WHERE Genotype like '$Genotype'); my $sth = $dbh_m-> prepare($sql); $sth->execute; my %hash; my $transvalues = $sth->fetchrow_hashref; my %hash= %$transvalues; $sth ->finish(); $dbh_m->disconnect(); my $key; my $value; while (($key, $value) = each(%hash)){ print $key.", ".$value\n; } 

This code does not cause errors, but the hash hash saves only the last row taken from the database (I had the idea to write it this way from this site ). If I print:

 while(my $transvalues = $sth->fetchrow_hashref){ print "Gene: $transvalues->{Gene}\n"; print "Trans: $transvalues->{TransNo}\n"; } 

Then it prints all the lines, but I need all this information to be available after closing the database connection.

I also have a related question: in my MySQL database, the string consists of, for example, 'Gene1' (Gene) '4' (TransNo). As soon as I retrieve this data from the database, as I do above, will TransNo still know which Gene is associated with? Or do I need to create some kind of hash structure hash for this?

Answer 1

You call the "wrong" function

fetchrow_hashref will return the string one as hashref, you must use it inside the loop, ending it when fetchrow_hashref returns undef .

It seems that you are looking for fetchall_hashref that will provide you with all returned rows as a hash with the first parameter specified as the field to use as the key.

 $hash_ref = $sth->fetchall_hashref ($key_field); 

Each row will be inserted into $hash_ref as an internal hashref, using $key_field as the key in which you can find the row in $hash_ref .

What does the documentation say?

The fetchall_hashref method can be used to retrieve all the data that will be returned from the prepared and executed operator descriptor.

It returns a hash reference containing the key for each individual value of the $ key_field column that was retrieved.

For each key, the corresponding value is a reference to a hash containing all the selected columns and their values, as fetchrow_hashref () was returned.

---

Documentation Links

• DBI - search.cpan.org #fetchrow_hashref
• DBI - search.cpan.org #fetchall_hashref