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

Hi Monks,
I need to search a db table using some account numbers and at the end I need to attach the account numbers to its id, but having problems trying to store the data into a hash to use it later, I think, looking at this sample code will probably tell more from the comments, thanks:
#########################
#original account numbers
my @code_array = qw(7772344 2233421 TMW2222987 TMA2222333 TMW9999988 A
+MQ8873332 AMQ1111877);

#account numbers in @code_array has been processed and the letters bef
+ore any account has been removed, so I have this:
my @code_array_noletter = qw(7772344 2233421 2222987 2222333 9999988 8
+873332 1111877);

my $plc_holders = join ( ',', ('?') x @code_array_noletter );

#this table is numeric, letters has been removed from account numbers
my $databasse_cheks="SELECT accounts, id FROM accountstable WHERE acco
+unts IN ($p_holders_state)";
# assuming all db stuff like connection,prepare,execute has been done 
+at this point

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

          my $accountnumber = $pt->{'accounts'};
          my $acc_id = $pt->{'id'};
          
      #matching account numbers in @code_array at the numeric level, w
+ith what was found in the DB:::
      if ( "@code_array" =~ /(^|\s|[[:alpha:]]{0,3})$accountnumber(\s|
+$)/ )
       {
              # in here I need to store the acc_id the match what was 
+found in the @code_array.
              # matching on these account numbers as an example, 77723
+44 2233421 TMW2222987 i have to attache
              # the acc_id to it. And that's my problem.
              # I think it has to be done using a hash but having prob
+lems trying.
              # results before storing in a hash or some other way, wi
+ll be the account number and its id:
              # 
                  7772344 34
                  2233421 54
                  TMW2222987 66
                  
              # need to push all this here to get access out side of t
+he while
           }


#print here for testing the results::::

#########################

[download]

Thanks for the Help!!!

Replies are listed 'Best First'.
Re: Array or Hash problem!
by jethro (Monsignor) on Mar 04, 2010 at 17:55 UTC

    I have difficulties understanding your problem since there seem to be words missing in your problem description: "in here I need to store the acc_id the match what was found in the @code_array"

    Apart from correcting above sentence you could answer the following question to shed more light on your problem: What would be wrong with the following solution:

    $ids{$accountnumber}= $acc_id;

    [download]

    or, if accountnumbers had more than one acc_id:

    push @{$ids{$accountnumber}}, $acc_id;

    [download]
[reply]
[d/l]
[select]
      This $ids{$accountnumber}= $acc_id; is good, but:
      "in here I need to store the acc_id the matchs what was found in the db with the acount number from the @code_array array". I am searching for "2222987" and I find in the db 2222987 the id 66 is also found, that's what it means storing the value into this hash. But how to keep the original account number like TMW2222987 instead of 2222987 and the id of course. Thanks.
[reply]
[d/l]

        So you want to have '2222987' as key and 'TMW2222987' and '66' as values? In that case you could use a two-element array or just concatenate the two values into a string:

        $ids{$accountnumber}= "$acc_id:$1$accountnumber";

        [download]

        The $1 is what was matched with the first parenthesis in your regex, so should be 'TMW' or ' ' or '' or ...

        You get both values separated again with:

        my($acc_id,$fullaccount)= split /:/,$ids{$accountnumber};

        [download]
[reply]
[d/l]
[select]
Re: Array or Hash problem!
by spazm (Monk) on Mar 04, 2010 at 18:05 UTC
    I think I understand your request, but I may have misunderstood the mapping of code, number and acc_id.

    you have: @code_array ==> full code strings of the accounts.
    you have: @code_array_no_letters => numeric version of code strings.
    you have: a database keyed on numeric versions of the code strings mapping to an acc_id.
    You want: a list of code => acc_id.

    My advice is to always start with a picture (literal or figurative) of your data and your final output. This is very helpful when explaining to others and asking for help. 

    #!/usr/bin/perl
use warnings; use strict;

my @code_array = qw(7772344 2233421 TMW2222987 
TMA2222333 TMW9999988 AMQ8873332 AMQ1111877);
#prime a lookup from the numeric code back to the full code.
my %code_by_num;
for my $code (@code_array)
{
  (my $num = $code) =~ s/\D//g;
  $code_by_num{ $num } = $code;
}
#...
while (my $pt = $sth->fetchrow_hashref) 
       {
          my $accountnumber = $pt->{'accounts'};
          my $acc_id = $pt->{'id'};
          my $accountcode = $code_by_num{ $accountnumber };
          print "$accountcode $acc_id\n"
        }

    [download]
[reply]
[d/l]
      This is one way of doing it, values in @code_array are the accounts in its original format, values in @code_array_noletter are the values from @code_array without the letters at the beginning of the account number since this table is numeric. My objective is, remove the letters from the beginning of the code from the account numbers that of course has letters at the beginning, its done. Second go to this database table find if any of this account numbers are in there and bring the results back which will include the ID, done I can go in there find what I want. Now after I find the match by the number of the account only i need to reattach the code back, like if one of my result will be "2222987" and its ID is "66", at the end I would like to be able to print TMW2222987 (original format) and its ID 66. I hope I made it a little clear this time. Thanks!
[reply]
Re: Array or Hash problem!
by youlose (Scribe) on Mar 04, 2010 at 18:00 UTC
    I don't understand this chunk of code at all: 
    if ( "@code_array" =~ /(^|\s|[:alpha:]{0,3})$accountnumber(\s|
+$)/
    Show your initial data, db data and which result you seek and i can tell you, how you can improve and finish your program. P.S. to receive hash from array you need: %hash = @array, it simple ;-)
[reply]
      I am just searching the array without the use of a loop.
[reply]
        Searching array without loop in Perl - is "grep" or "~~" Show some data please.
[reply]

Back to Seekers of Perl Wisdom