Search code examples
sqlperldbiperl-data-structures

Perl - From Database to data structure

I'm querying a table in a database with SQL like this:

Select col1, col2 from table_name

For reference, col2 will be an integer value, and col1 will be the name of an element. E.G.

FOO, 3
BAR, 10

I want a data structure where the values can be addressed like vars->{valueofcol1} should return the value of col2.

So

$vars->FOO

would return 3

Basically I don't know how to return the SQL results into a data structure I can address like this.

Solution

  • You need to fetch reach row and build that hashref yourself.

    my $vars; # declare the variable for the hash ref outside the loop
my $sth = $dbh->prepare(q{select col1, col2 from table_name});
$sth->execute;
while ( my $res = $sth->fetchrow_hashref ) { # fetch row by row
  $vars->{ $res->{col1} } = $res->{col2}; # build up data structure
}

print $vars->{FOO};

__END__
3

    You may want to read up on DBI, especially how to fetch stuff.