Return Array of Hashes DBI (MYSQL)

expresspotato has asked for the wisdom of the Perl Monks concerning the following question:

Hey Monks... I'd like to be able to return an array of bound hashes from an SQL Query using the usuall prepare / execute routine. Ideally I would then be able to loop through the values in the array and reference the hashes available on each row.


foreach (@array){
  print " @array[$i]{row_id}, @array[$i]{name}, @array[$i]{date} ..."
 $i++;
}
[download]

or even

foreach (@array){
 print "$_{row_id}, $_{name}, $_{date} ...";
}
[download]

Examples for both would be ideal but the latter is more desirable since I'll have access to direct values.

Comment on Return Array of Hashes DBI (MYSQL) Select or Download Code

Replies are listed 'Best First'.
Re: Return Array of Hashes DBI (MYSQL) by Corion (Patriarch) on Dec 06, 2009 at 20:22 UTC
See the `->fetchall_arrayref` method of DBI, and the `slice` parameter, which allows you to specify that you want hashes back: `my $emps = $dbh->selectall_arrayref( "SELECT ename FROM emp ORDER BY ename", { Slice => {} } ); foreach my $emp ( @$emps ) { print "Employee: $emp->{ename}\n"; }` [download]	[reply] [d/l] [select]
Re^2: Return Array of Hashes DBI (MYSQL) by expresspotato (Beadle) on Dec 08, 2009 at 11:22 UTC
Perfect! Still kinda odd its attaching to the DBH rather than the STH, but what do I know!	[reply]
Re^3: Return Array of Hashes DBI (MYSQL) by Corion (Patriarch) on Dec 08, 2009 at 11:24 UTC
The same, or at least similar methods are available for the STH as well.	[reply]