CODEWITHSUNDEEP
perldbi
user2361820
user2361820

Reputation: 449

Using selectall_hashref as I would selectall_arrayref

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.

Upvotes: 3

Views: 15941

Answers (3)

hwnd
hwnd

Reputation: 70732

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

Upvotes: 8

Jim Garrison
Jim Garrison

Reputation: 86774

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.

Upvotes: 3

perreal
perreal

Reputation: 97948

Hashes define an association between 2 things, but what is the key, or value in this case? Assuming that you want a 1 as the value:

my %hash = map { $_, 1 } @array;

or,

my %hash = map { $_->[0], 1 } @$sql;

Upvotes: 0

Related Questions