I'm trying to clean up code and make it more modular, by creating subroutines for code that will be reused.

I'm trying to put reusable database calls in a sub, and then pass out nrows,nfields and the array of array refs back to Main. I can't figure out how to access the AoArefs in Main.

Here's a simplied version of what I'm doing. I used alot of variable seeding for testing and to make things easier. I apologize for the length, but it's as small as I could make it.

If there's a cleaner, more elegant way to do what I want, please let me know. I'm open to anything.

#!/usr/local/bin/perl5 -w

use strict;
use DBI;
use CGI qw(:standard);

my $dbh = DBI->connect('DBI:mysql:tapebot:lid42','','')  
        || die "cannot connect to DB\n" .  DBI->errstr;

my $full = "\'F\'";
my %bighash = ();
my $sth;
my $nrows_max;
my $i = "2";
my %days = (
        '0' => 'Sunday',
        '1' => 'Monday',
        '2' => 'Tuesday',
        '3' => 'Wednesday',        
        '4' => 'Thursday',
        '5' => 'Friday',
        '6' => 'Saturday',
   ); 

my $aoa;
my $day_query = qq{
        SELECT last,keyno,btype
        FROM weekly
        WHERE btype =$full 
        AND day = ?
        ORDER by keyno
        };

my $day;
foreach $day (sort keys %days) {
($bighash{$day}{$aoa},$bighash{$day}{nrows},$bighash{$day}{nfields})
       = get_db_entries($sth,$day_query,$day);
}

$day = '5';
$i = '0';
print "\nnrows: ${ $bighash{$day}->{nrows} }\n";
print "\nnfields: ${ $bighash{$day}->{nfields} }\n";
print "aoa:  ${ $bighash{$day}->$aoa->[$i]->[2] } after\n";

$dbh->disconnect;

sub get_db_entries {
        my($s_handle,$qry,$d)= @_;
        my($nrows,$aoa,$nfields);
        $s_handle = $dbh->prepare($qry) 
          or die "Can't prepare statement: ". $dbh->errstr;;
        $nrows = $s_handle->execute($d) 
          or die "Can't execute statement: " . $dbh->errstr;
        print "\n sub nrows: $nrows\n";
        $aoa = $s_handle->fetchall_arrayref();
        $nfields = $s_handle->{NUM_OF_FIELDS};
        return(\$aoa,\$nrows,\$nfields);
}
[download]

Database entries look like below. There's anywhere from 14-27 entries per day:

mysql> select * from weekly where btype = 'F' and day = '0'
order by keyno limit 2;
+-------+-------+------------+-----+
| keyno | btype | last       | day |
+-------+-------+------------+-----+
|     8 | F     | 2003-09-28 |   0 |
|    58 | F     | 2003-09-28 |   0 |
+-------+-------+------------+-----+
2 rows in set (0.02 sec)
[download]

Here's what my output looks like when I run it: I get these for each day in %days as it iterates:

Use of uninitialized value in hash element at 
./hoAoA_ref_test.pl line 39.
Use of uninitialized value in hash element at 
./hoAoA_ref_test.pl line 39.
[download]

Then I get this at the end of the script:

Use of uninitialized value in method lookup at 
./hoAoA_ref_test.pl line 50.
Can't call method "" on unblessed reference at 
./hoAoA_ref_test.pl line 50.
[download]