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

Hi , I have a hash with values as an array. {'col1'=>[1], 'col2'=>[1,2],'col3'=>[3,4,5]} And I have to insert the data from this hash to a database table. So I can't access the values from the traditional foreach loop since this would result in multiple entries depending on the number of keys. So I would like to get a way where I can access the multiple values without looping.
foreach my $results(@$hash_of_excel){ my $specificresults = GroupResults($results); insertResult($specificresults->{'Desc'}); #last if $counter++ == 2; } sub GroupResults{ my( $AllRecords )= @_; my %specificResults; for my $info (keys %details){ $specificResults{$info}{$_} = $AllRecords->{$_} for @{$details{$in +fo}; } } return \%specificResults; } sub SomethingUnPipeModifyRecord { my( $rec ) = @_; for my $val( values %$rec ){ my $newval = [ split /\|/, $val ]; $val = $newval; } return $rec; } sub insertL{ my ($li_results )= @_; my $liarray_values = SomethingUnPipeModifyRecord($ligand_results); #warn Dumper($li_array_values); warn "insert into LI values $li_array_values->{'Code'}[0],$li_array_v +alues->{'US'}[0],$li_array_values->{'IN'}[0],$li_array_values->{'STEM +'}[0]"; #my $ligand_insert = "insert into LI values(LI_SEQ.nextval,'SM',?,?,?,?,?,?,1,sysdate,?)"; }
I can access the first value but not the subsequent values Is there any smart way of doing this? Thanks a ton.

Replies are listed 'Best First'.
Re: access array of values without a loop
by kennethk (Abbot) on Feb 19, 2015 at 12:03 UTC
    This sounds like an XY Problem - why are you so committed to this data structure? Your particular use case is a little unclear from the code you've posted. How do you expect the demo data structure to be stored in the database? Will the elements of the array be joined together into a single string for the field?
    my $field = join ',', @{$hashref->{col3}};
    Are there multiple fields per key?
    $query->execute($hashref->{col1},@{$hashref->{col2}},@{$hashref->{col3 +}});
    How exactly do you envision your data being transformed to fit into your database?

    #11929 First ask yourself `How would I do this without a computer?' Then have the computer do it the same way.

Re: access array of values without a loop
by CountZero (Bishop) on Feb 19, 2015 at 14:53 UTC
    It is perfectly possible to insert this into a database, provided the database structure allows "one-to-many" relationships. You will need at least two tables for this to work: one to hold the key and individual data for this key and another table to hold the repeated data.

    A typical example would be: one table to hold the ISDN code (key) and title of a book and another table to hold a sequence number, the ISDN code and the name of the authors (one author per record). Thus it is easy to accommodate multiple authors for one book.

    CountZero

    A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

    My blog: Imperial Deltronics

      I suspect you hit the central problem square on the head.   And, just for completeness:   you can also represent many-to-many relationships in an SQL database ... this requires three tables.   For example:
      INVOICE <-> INVOICE_LINEITEMS <-> ITEMS.

        Yes and that is even the better solution in case (returning to my example) one has authors which have authored multiple books.

        CountZero

        A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

        My blog: Imperial Deltronics
Re: access array of values without a loop
by Anonymous Monk on Feb 19, 2015 at 10:37 UTC

    So I can't access the values from the traditional foreach loop since this would result in multiple entries depending on the number of keys. So I would like to get a way where I can access the multiple values without looping.

    If the data structure you have isn't working for you, make a better one.

    What kind of sql should   {'col1'=>[1], 'col2'=>[1,2],'col3'=>[3,4,5]} result in?

Re: access array of values without a loop
by ikegami (Pope) on Feb 19, 2015 at 18:14 UTC

    I would like to get a way where I can access the multiple values without looping.

    By definition, that's impossible. It doesn't have to be a foreach loop, but a loop will be involved.

    Why don't you say what you want to do? Would join be of use?

      Hi All, Thanks for all your comments. I think I have more questions than I initially had. Which is good ! Now I understand the XY problem. I have a database which has 12 tables. The keys are column names from the excel sheet. This is my primary table and the rest in one to many relationship with this table. I have one value for the primary column in my LI table(COLUMN NAME LI) and can have multiple values for different columns in the same or different table.
      my $li_insert = "insert into LI values(LI_SEQ.nextval,?,?,?,?,?,?,?,? +,?,sysdate,?,?,?,?)"; my $mechanism_insert = "insert into mechanism (TAGET,ACTIONTYPE,ID,LI_ +ID) values(?,?,MECH_SEQ.nextval,LI_SEQ.currval)";
      Here one LI_ID can have multiple action_type or TAGET. So I want my script to be able to insert
      5,2,1,12 6,1,2,12.
      The data would be like this:
      { 'taget '=> [5,6], 'Action_type' =>[2,1] }
      in the excel sheet it actually comes as joined with a pipe operator I split them to have an array in a subroutine. How could I achieve this type of insertion if I keep them joined? Hope this is clear now!Let me know if you need any more details.
      # to get each hash from an array of hash foreach my $results(@$hash_of_excel){ my $specificresults = GroupResults($results); } sub GroupResults{ #to getonly the relevant columns(excel) for each table in the database +. #%details is a global hash tha holds different tables and their corres +ponding column my( $AllRecords )= @_; my %specificResults; for my $info (keys %details){ $specificResults{$info}{$_} = $AllRecords->{$_} for @{$details{$in +fo}; } } return \%specificResults; } sub SomethingUnPipeModifyRecord { #convert the joined values to array of values my( $rec ) = @_; for my $val( values %$rec ){ my $newval = [ split /\|/, $val ]; $val = $newval; } return $rec; } sub insertLI{ my ($li_results )= @_; my $li_array_values = SomethingUnPipeModifyRecord($li_results); } sub insertMech{ my ($mech_results ) = @_; my $mech_array_values = SomethingUnPipeModifyRecord($mech_results);
        LI_ID can have multiple action_type or TAGET

        Are the multiples always the same ?
        For example, if there are 3 action_type values, will there always be 3 TAGET values ?

        poj
      Hi All, Thanks for all your comments. I think I have more questions than I initially had. Which is good ! Now I understand the XY problem. I have a database which has 12 tables. The keys are column names from the excel sheet. This is my primary table and the rest in one to many relationship with this table. I have one value for the primary column in my LI table(COLUMN NAME LI) and can have multiple values for different columns in the same or different table. my $li_insert = "insert into LI values(LI_SEQ.nextval,?,?,?,?,?,?,?,?,?,sysdate,?,?,?,?)"; my $mechanism_insert = "insert into mechanism (TAGET,ACTIONTYPE,ID,LI_ID) values(?,?,MECH_SEQ.nextval,LI_SEQ.currval)"; Here one LI_ID can have multiple action_type or TAGET. So I want my script to be able to insert 5,2,1,12 6,1,2,12. The data would be like this: { 'taget '=> 5,6, 'Action_type' =>2,1 } in the excel sheet it actually comes as joined with a pipe operator I split them to have an array in a subroutine. How could I achieve this type of insertion if I keep them joined? Hope this is clear now!Let me know if you need any more details.
      # to get each hash from an array of hash foreach my $results(@$hash_of_excel){ my $specificresults = GroupResults($results); } sub GroupResults{ #to getonly the relevant columns(excel) for each table in the database +. #%details is a global hash tha holds different tables and their corres +ponding column my( $AllRecords )= @_; my %specificResults; for my $info (keys %details){ $specificResults{$info}{$_} = $AllRecords->{$_} for @{$details{$in +fo}; } } return \%specificResults; } sub SomethingUnPipeModifyRecord { #convert the joined values to array of values my( $rec ) = @_; for my $val( values %$rec ){ my $newval = [ split /\|/, $val ]; $val = $newval; } return $rec; } sub insertLI{ my ($li_results )= @_; my $li_array_values = SomethingUnPipeModifyRecord($li_results); } sub insertMech{ my ($mech_results ) = @_; my $mech_array_values = SomethingUnPipeModifyRecord($mech_results);
Re: access array of values without a loop
by hotpelmen (Scribe) on Feb 19, 2015 at 16:18 UTC
    spie287, Could you please clarify, based on you hashref example, what the values inserted into those three columns are supposed to look like? Your ultimate goal is not clear. Thanks.
Re: access array of values without a loop
by hotpelmen (Scribe) on Feb 20, 2015 at 14:55 UTC
    So you have a hash where keys are column names and values are arrayrefs of column values. In other words, data for a single record is spread over multiple arrays and this is, I gather, what irritates you, I am getting it right?

    If so, then it might take some work to prepare it but a more intuitive data structure would be to have an array of hashrefs where each hashref will contain all necessary data for one db record, e.g.
    [ {TAGET => 5, ACTIONTYPE =>2}, {TAGET => 6, ACTIONTYPE =>1}, ]
    Then, when you have to do insertions, you will loop through this array, and getting values for inserting each record will be trivial and easy to understand. Hope this helps.