Array_ref into Array

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

Hi Monks!

I have two big database tables that I "can not join", and I need to make a report out of it, and the only way I found based on how the real application works is to go first to the first table and than after finding all the "acc_num and dates" go to the second table, and after build my final report with the header values as you can see on this code sample.

# Start first sql...

my $dbh = DBI->connect...

my $sql = "
           SELECT 
                  acc_num,
                  date,
                  user1,
                  user2,
                  user3,
                  user4,
                  user5,
                  user6,
                  user7,
           
           FROM   users_info
           WHERE  date='2009-04-11'

           ";

my $sth = $dbh->prepare($sql)|| die $dbh->errstr;
   $sth->execute() or die "$!\n";

my $array_ref = $sth->fetchall_arrayref();

my @ar_acc_num;
foreach my $vals (@$array_ref){

my ($acc_num, $date, $all_users) =@$vals;

push @ar_acc_num ,$pol_num;
# I will need to push $date into an array as well, but one problem at 
+a time.

}

# here I have part of the data I need
unshift @{ $array_ref };

# here I have all the acc numbers I need to go after on the second tab
+le:
# print "@ar_acc_num";

# Start second sql...

my $dbh_b = DBI->connect...

my $values = 
  join("," 
       ,map { $dbh_sec->quote($_) } @ar_acc_num 
      );


my $sql_b = "
           SELECT add,
                  acc,
                  date,
                  email,
                  phone,
                  city,
                  state,
                  country,
                  reg
           
           FROM   all_info
           WHERE  date='2009'  /* # it should be the value of $date pu
+shed previously but for now I am using static year of 2009 */
           AND    acc IN ($values)
           ";

my $sth_b = $dbh_b->prepare($sql_b)|| die $dbh_b->errstr;
   $sth_b->execute() or die "$!\n";

my $header = [
                  "Acc. Num",
                  "Date",
                  "User 1",
                  "User 2",
                  "User 3",
                  "User 4",
                  "User 5",
                  "User 6",
                  "User 7",
                  "Add",
          "Acc",
          "Email",
          "Phone",
          "City",
          "State",
          "Country",
          "Regular",
          
                  ];



my $array_ref_b = $sth_b->fetchall_arrayref();

#***Here I need to add @{ $array_ref } to  @{ $array_ref_b } to contai
+n all the data I need to format 
my report, that's where my problem is, how to do that?

## @{ $array_ref_b }= @{ $array_ref }.@{ $array_ref_b };

unshift @{ $array_ref_b }, $header;
[download]

What I am trying to do here is to add "@{ $array_ref } to @{ $array_ref_b }" to contain all the data I need to format my report, that's where my problem is, how to do that?
Thanks for the Help!!!!

Comment on Array_ref into Array_ref Help. Download Code

Replies are listed 'Best First'.
Re: Array_ref into Array_ref Help. by kennethk (Abbot) on Apr 22, 2009 at 15:45 UTC
Assuming that by 'add "@{ $array_ref } to @{ $array_ref_b }"' you mean append the contents of the array referenced by $array_ref_b to the end of the array referenced by $array_ref, you need only use push. `push @{ $array_ref }, @{ $array_ref_b };` Of course TIMTOWTDI, but this is probably the most concise. Incidentally, if you wish you can save some typing by referring to the array pointed to by $array_ref with @$array_ref. It's all in perlref and perlreftut.	[reply] [d/l]
Re: Array_ref into Array_ref Help. by toolic (Bishop) on Apr 22, 2009 at 15:47 UTC
Will push help? `use strict; use warnings; use Data::Dumper; my $aref1 = [(5..8)]; my $aref2 = [(1..3)]; push @{ $aref1 }, @{ $aref2 }; print Dumper($aref1); __END__ $VAR1 = [ 5, 6, 7, 8, 1, 2, 3 ];` [download]	[reply] [d/l]
Re: Array_ref into Array_ref Help. by lostjimmy (Chaplain) on Apr 22, 2009 at 15:49 UTC
Just use unshift. `use Data::Dumper; my $array_ref = [ [ qw/a b c/ ], [ qw/d e f/ ], [ qw/g h i/ ], ]; print Dumper $array_ref; my $array_ref_b = [ [ qw/j k l/ ], [ qw/m n o/ ], [ qw/p q r/ ], ]; print Dumper $array_ref_b; unshift @$array_ref_b, @$array_ref; print Dumper $array_ref_b` [download] Update: Suggested `unshift` because it looked like the OP wanted to prepend the contents of `$array_ref` to `$array_ref_b`: `## @{ $array_ref_b }= @{ $array_ref }.@{ $array_ref_b };`	[reply] [d/l] [select]
Re^2: Array_ref into Array_ref Help. by Anonymous Monk on Apr 22, 2009 at 17:16 UTC
Not really working loosing the value of $header.	[reply]
Re^3: Array_ref into Array_ref Help. by lostjimmy (Chaplain) on Apr 22, 2009 at 17:32 UTC
loosing [sic] the value of $header. I'm not really sure what you mean. If you still need the header, you should be `unshift`ing it onto `$array_ref_b`, just like you were doing in your original code. That's assuming that what you really want in the end is something along the lines of `[ $header, $array_ref, $array_ref_b ]`. `unshift @$array_ref_b, @$array_ref; unshift @$array_ref_b, $header;` [download]	[reply] [d/l] [select]
Re^2: Array_ref into Array_ref Help. by Anonymous Monk on Apr 22, 2009 at 17:42 UTC
This is almost how I would like to have it, but I need here is, lets say the end result would be like: `a b c j k l d e f m n o g h i p q r` [download] Because from the data base the first element of the first array_ref is relative to first element of the second array_ref.	[reply] [d/l]
Re^3: Array_ref into Array_ref Help. by kennethk (Abbot) on Apr 22, 2009 at 17:49 UTC
Do you mean you want to assemble the arrays into multidimensional structures? You should probably read up on arrays of arrays in perllol. The short answer is you could do it using `my @AoA = ($array_ref, $array_ref_b);`	[reply] [d/l]
Re^3: Array_ref into Array_ref Help. by lostjimmy (Chaplain) on Apr 22, 2009 at 17:56 UTC
What have you tried? `my $ref_a = [ [ qw/a b c/ ], [ qw/d e f/ ], [ qw/g h i/ ], ]; my $ref_b = [ [ qw/j k l/ ], [ qw/m n o/ ], [ qw/p q r/ ], ]; for my $i (0..$#$ref_a) { push @{$ref_a->[$i]}, @{$ref_b->[$i]}; }` [download]	[reply] [d/l]
Re^4: Array_ref into Array_ref Help. by Anonymous Monk on Apr 22, 2009 at 18:08 UTC
Re^5: Array_ref into Array_ref Help. by lostjimmy (Chaplain) on Apr 22, 2009 at 19:32 UTC
Some notes below your chosen depth have not been shown here
Re: Array_ref into Array_ref Help. by Bloodnok (Vicar) on Apr 22, 2009 at 16:37 UTC
TIMTOWTDI ... If you're on a *NIX box, use join(1) - which will, amongst a myriad of other stuff, join 2 files on a common field A user level that continues to overstate my experience :-))	[reply]