Hi Monks!

What I am trying to accomplish here is to get data from one table first using fetchall_arrayref(); manipulate these values on the array_ref and push these values to a new array_ref, after that do the same with another table query results and at last combined these two array_refs into one, add the values from $header to be the header of the column in this report, please let me know if you understand this, I have comments on the code explaining as well, I would really appreciate any feed back on this!

# Start first sql...

my $dbh = DBI->connect...

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

           ";
           
my $sth = $dbh->prepare($sql, { RaiseError => 1 });
   $sth->execute() or die "$!\n";

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

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

my ($acc_num, $date, $user1,$user2,$email,$user4,$user5,$user6,$countr
+y) =@$vals;

#Now edit data here before saving it:::
$data=~s/2000/2009/g;
$user=~s/joe/Joe/g;

#get edited data to  the new array_ref::: Is this possible, is it how 
+it is done?
push @{$new_array_ref}, $date, $user1,$user2,$user3,$user4,$user5,$use
+r6,$user7;

push @ar_acc_num ,$acc_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, print for test
print Dumper ($new_array_ref);

#@ar_acc_num -> here I have all the acc numbers I need to go after on 
+the second table:::

# 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,
                  user3,
                  phone,
                  city,
                  state,
                  user7,
                  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 value of 2009 */
           AND    acc IN ($values)
           ";

my $sth_b = $dbh_b->prepare($sql_b, { RaiseError => 1 });
   $sth_b->execute() or die "$!\n";

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

my $new_array_ref_b;

foreach my $vals_b (@$array_ref_b){

my ($add, $acc, $date,$user3,$phone,$city,$state,$user7,$reg) =@$vals_
+b;

#Now edit data here before saving it:::
$city=~s/new york/New York/g;
$reg=~s/usa/USA/g;

#get edited data to  the new array_ref::: Is this possible, or how can
+ this be done?
push @{$new_array_ref_b}, $add, $acc, $date,$user3,$phone,$city,$state
+,$user7,$reg;

}

# print for test
print Dumper ($new_array_ref_b);



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",
          
                  ];

# Here is another question and problem I am having, as you can see I n
+eed to rearrange the columns to mach the order on $header, and 
# $email, $country are coming from the first query and $user3 and $use
+r7 fro mthe second, I need to reorganize the data to have some consis
+tency 
# when processing the rest of the code to be printed on this report. H
+ow can this be done?

# Here I need to add @{ $new_array_ref } to  @{ $new_array_ref_b } to 
+contain all the data I need to print a report and
# make sure that if data from the first table query has more data than
+ the results from the second table dont generate any error, if it is 
+it will
# insert a '' to the value and it will print nice on the report

for my $i (0..$#$new_array_ref) {

    push @{$new_array_ref->[$i]}, @{ ref($new_array_ref_b->[$i]) eq 'A
+RRAY' ?
                             $new_array_ref_b->[$i] : [(' ') x 3]   };
    
}



# This will add some data for the table header when printing this repo
+rt that will be done in pdf later
unshift @{ $new_array_ref }, $header;

##Now if all its done OK go here:

main_table($pdf, \@{$new_array_ref});
$pdf->saveas();


sub main_table {

    my $pdf  = shift;
    my $data = shift;
  
do more stuff......



}
[download]