CODEWITHSUNDEEP
mysqlperlhash
Lisa
Lisa

Reputation: 331

Using fetchrow_hashref to store data

I am trying to take information out of a MySQL database, which I will then 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 doesn't produce any errors, but the %hash only stores the last row taken from the database (I got the idea of writing it this way from this website). If I type:

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

Then it does print off all the rows, but I need all this information to be available once I've closed the connection to the database.

I also have a related question: in my MySQL database the row consists of e.g 'Gene1'(Gene) '4'(TransNo). Once I have taken this data out of the database as I am doing above, will the TransNo still know which Gene it is associated with? Or do I need to create some kind of hash of hash structure for that?

Upvotes: 4

Views: 7377

Answers (1)

Filip Roséen
Filip Roséen

Reputation: 63797

You are calling the "wrong" function

fetchrow_hashref will return one row as a hashref, you should wrap it's use inside a loop, ending it when fetchrow_hashref returns undef.

It seems like you are looking for fetchall_hashref, that will give you all of the returned rows as a hash with the first parameter specified what field to use as a 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 fetch all the data to be returned from a prepared and executed statement handle.

It returns a reference to a hash containing a key for each distinct value of the $key_field column that was fetched.

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

Documentation links

Upvotes: 6

Related Questions