http://www.perlmonks.org?node_id=1186414

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

This post is more about trying to find a cleaner way to accomplish my task. I use DBI a lot but I am curious if there is a better way to populate and array with the returned value when only a single field is returned. Example ---


SELECT `UserName` FROM Account a, asscAccountAccountGroup aaag, asscRoleAccountGroup arag, Role r WHERE a.Id = aaag.AccountId AND aaag.AccountGroupId = arag.AccountGroupId AND arag.RoleId = r.Id AND r.Name = 'ROLE_REVIEWER';

I have always acomplished this by doing the following --

my @regUsers; my $sth = $dbh->prepare("SELECT `UserName` FROM Account a, asscAccount +AccountGroup aaag, asscRoleAccountGroup arag, Role r WHERE a.Id = aaa +g.AccountId AND aaag.AccountGroupId = arag.AccountGroupId AND arag.Ro +leId = r.Id AND r.Name = 'ROLE_REVIEWER';") || die "Cannot prepare th +e query :$!"; $sth->execute() || die "Cannot execute the query :$!"; while ( my @userRows = $sth->fetchrow_array() ) { push @regUsers, @use +rRows; } print join("\n",@regUsers);
Which always works as expected but I'd like to simplify that code if possibe.

Doing the following seems to return a list of references but is close --

my @users = $dbh->selectall_array("SELECT `UserName` FROM Account a, a +sscAccountAccountGroup aaag, asscRoleAccountGroup arag, Role r WHERE +a.Id = aaag.AccountId AND aaag.AccountGroupId = arag.AccountGroupId A +ND arag.RoleId = r.Id AND r.Name = 'ROLE_REVIEWER'"); print join("\n",@users);

Thanks for the suggestions