Unique Array from DB

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

Hi Monks!

I am querying a database table and returning several values
and as you can see the individual names for the Groups could be different depending in what will be coming from the query result.
I am trying to group all the same groups into different arrays so I can use them later. How could I do that if I don't know how many different values I will get for the
Groups which it will be in $account_name; I don't know if I am making myself clear here but here is a sample code of a possible situation.

while (my $pointer = $sth->fetchrow_hashref)   
{

 $c++;

     $account_name = $pointer->{'account_name'};
        
     $username = $pointer->{'username'};

print "$c - $account_name = $username;

#if I print this here it could return something like:
=code

1 - Group A = John
2 - Group A = Mary
3 - Group A = Joe
4 - Group A = Ann
5 - Group A = Carl
6 - Group A = Joen

1 - Group B  = Maria

1 - Group C = Junior
2 - Group C = Eva
3 - Group C = Mario
4 - Group C = Raimund
5 - Group C = Gerald
6 - Group C = Jasmin
7 - Group C = Rose
8 - Group C = Suelly

# I have to find a way to whatever comes from my db query to be 
# separated by the group so later I could use on my program:
# could be something like: push @array1 = all Group A and usernames an
+d so forth...
=cut

}
[download]

Let say all I've got for this test here was the 3 different Groups, I could at the end do this after using maybe "push":
#Do something with @array1.
#Do something with @array2.
#Do something with @array3.

Thanks for the help!

Comment on Unique Array from DB Download Code

Replies are listed 'Best First'.
Re: Unique Array from DB by ikegami (Patriarch) on Mar 09, 2009 at 16:08 UTC
Hashes are great for grouping. `my %users_by_account; ... push @{ $users_by_account{$account_name} }, $username; ... for my $account_name (sort keys %users_by_account) { my $users = $users_by_account{$account_name}; print("Account $account_name\n"); for my $username (@$users) { print("$username\n"); } print("\n"); }` [download]	[reply] [d/l]
Re^2: Unique Array from DB by Corion (Patriarch) on Mar 09, 2009 at 16:13 UTC
Why use a hash and sort yourself when the DB can do that already? `select ... order by account_name, username` [download] then, in Perl: `my $last_account; while (my $row = $sth->fetchrow_arrayref({ Slice => {})) { if (! defined $last_account or $last_account ne $row->{account_nam +e}) { if (defined $last_account) { print "\n"; }; $last_account = $row->{account_name}; print "Account $last_account\n", }; print $row->{username}; };` [download]	[reply] [d/l] [select]
Re^2: Unique Array from DB by Anonymous Monk on Mar 09, 2009 at 17:08 UTC
If one more field from the DB would have to be added to this like including the date the account was opened, `$date_opened = $pointer->{'dateopened'};` Is that the case of one of those multidimensional hash? Printing, Group A - Name=Mary - Date Opened 03/03/2009	[reply] [d/l]
Re^3: Unique Array from DB by ikegami (Patriarch) on Mar 09, 2009 at 19:33 UTC
Why is it in the same table as the user names if it applies to the account? Or are you joining in SQL then unjoining in Perl? Either way, seems odd to me. See Re: Grouping an array of hashrefs by similar key values.	[reply]
Re: Unique Array from DB by davido (Cardinal) on Mar 09, 2009 at 18:04 UTC
Anytime you're tempted to start manipulating variables by their names, or tempted to name variables something like @array1, @array2, etc., you're probably going down the wrong road. Instead of @array1, @array2, etc., have a single array that holds references to the individual datastructures. Instead of `@array1 = ( list )`, how about `$array->[1] = [ list ]`? The difference is that with the first method you're going to end up using symbolic references needlessly, and probably introducing a heightened potential for bugs, unmaintainable code, and security breeches into your script. With the second method, you're using real "hard" references how they were intended to be used, and will end up with cleaner, more maintainable, less buggy, and more secure code. Naming variable names programatically is almost always a bad idea, and creating lists of soft references begs the question why aren't we just using an array of hard references in the first place? I hope this is helpful in describing that there is a "better way to do it" out there. Dave	[reply] [d/l] [select]