Using selectall_hashref as I would selectall_arrayref
Solution 1
Using the selectall_hashref
method combines prepare
, execute
and fetchall_arrayref
into a single call. It returns a reference to an array containing a reference to an hash for each row of data fetched. To addon to previous comments of responses.
A verbose way of turning your selectall_arrayref
call in to a similar hash where ID
is your primary key, you could do something like this.
my %rows = ();
for my $i ( 0..$#{$sql} ) {
my ($id, $player, $sport) = @{$sql->[$i]};
$rows{$id} = [ $player, $sport ];
}
And now to access them:
foreach ( sort(keys(%rows)) ) {
print "$_, -> $rows{$_}->[0], -> $rows{$_}->[1]\n";
}
Now using selectall_hashref
, your call would be something like this. ID is the primary
key here.
my $href = $dbh->selectall_hashref( q/SELECT ID, Player, Sport
FROM Players_Sport/, q/ID/ );
To access your keys you can use in many ways, here is an example:
foreach ( keys %$href ) {
print "$_, $href->{$_}->{Player}, $href->{$_}->{Sport}\n";
}
Your primary key is placed as $_
, your individual keys you can be access out of the hash by saying.
$href->{$_}->{Player}
Again it is just like saying:
foreach my $i ( keys %$href ) {
print "$i, $href->{$i}->{Player}, $href->{$i}->{Sport}\n";
}
Each item is looping through $i
Solution 2
When you use selectall_hashref
you must tell it what column to use as the unique key. Specify a column you know will have unique values for every row, then it will populate a hash with 'row' objects using the key column values as the key. Each 'row' is itself a hashref with the column names as the keys.
From the DBI page:
$hash_ref = $dbh->selectall_hashref($statement, $key_field);
$hash_ref = $dbh->selectall_hashref($statement, $key_field, \%attr);
$hash_ref = $dbh->selectall_hashref($statement, $key_field, \%attr, @bind_values);
In all three versions you provide $key_field
to identify the column that will be used as the hash key.
user2361820
Updated on June 27, 2022Comments
-
user2361820 almost 2 years
I'm doing some exercises to increase my Perl skills and one of them involves connecting to an SQL database, running a query, and returning the results as an array. This is what I have so far:
my $search = $_[0]; our $dbh = DBI->connect{"dbi:mysql:dbname=database", "root", "password") or die $DBI::errstr; my $sql = $dbh->selectall_arrayref("SELECT Player from Players_Sport where Sport like '$search'") or die $DBI::errstr; my @array = map { $_->[0] } @$sql; $dbh->disconnect or warn "Disconnection failed: $DBI::errstr\n"; return @array;
My next step is to change the code to return the results as a hash instead of an array, but I'm not certain how to proceed. I expect to use
selectall_hashref
, but all the online examples I've found use WHILE. I want to return the results, then create the hash as separate steps. -
user2361820 almost 11 yearsIn my MySQL table I have ID, Player, and Sport. I'm inputting a search "$search" via a getopt command line argument, and I want to Select * from table where Sport like "$search". I suppose my key would be ID? I want put "Basketball" in my search, and then I get return a reference to players who play that sport. I suppose my "key " would be ID then