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

Hello Monks,

I need your help yet again. The below code is mostly re-used. The sub-routine (not listed) is used by a dozen scripts and works great. The query itself works fine if I try it out on a single database.

However when I run this the results are not even close to accurate. Can anyone see any glaring errors? I thought maybe the space that was in the hash keys was the issue so I fixed that but still dosen't work. Been tinkering with this for well over 2 hours..and it's starting to erk me. This should be simple..but it's not.

What it's doing is getting a number of times a user takes something ..the key is a date/hour concat so in essence I want the number of times a user took something for each date/hour (for those date/hours that have takes). There should be a max of 24 lines per host (so 24*3 which is 3 days, 24 hours in each day). Im getting 8 lines with 200ish results..should be A LOT more..both in terms of lines and the #'s returned (I'd expect them to be between 100's and 10,000's). 


#!/usr/bin/perl

use strict;
use warnings;
use DBD::mysql;

######################
#### CONFIG VARS #####
######################

my $output_file = 'Takes_by_hour.txt';

######################
#### PRIVATE VARS ####
######################


my $start_date = '2009-10-04';
my $end_date = '2009-10-06';

######################
#### MAIN BODY #######
######################

open OUTPUT, ">$output_file";
select(OUTPUT); $| = 1;
select(STDOUT); $| = 1;
        
while (my ($host, $default_db) = each(%hosts)) {
    my %take_counts = ();
    
    #get database urls for host
    my @databases = get_databases($host, $default_db, $db_user, $db_pa
+ss);
    @databases = sort(@databases);

    #process data on each database
    foreach my $database (@databases) {
        print join ("/", $host, $database) . "\n";
        
        #Get List of Instructor with active/current courses
        my $dbh = DBI->connect("DBI:mysql:database=$database:host=$hos
+t",$db_user,$db_pass,{RaiseError=>1})|| die "$DBI::errstr\n";
        my $sql = "SELECT DATE_FORMAT(ar.StartedAt,'%b%d-%k') AS DateT
+aken, COUNT(ar.UserID) 
               FROM Assignments AS a JOIN AssignmentResults AS ar ON a
+.ID=ar.AssignmentID 
               WHERE DATE(ar.StartedAt) BETWEEN '$start_date' AND '$en
+d_date' GROUP BY DateTaken";
        my $sth = $dbh->prepare ( $sql );
        $sth->execute();

        while (my @row = $sth->fetchrow_array()) {
            if (exists $take_counts{$row[0]}) { 
                $take_counts{$row[0]} += $row[1];
            }
            else { 
                $take_counts{$row[0]} = $row[1];
            }
        }
        $sth->finish();
        $dbh->disconnect();
    }
    foreach (my ($key, $value) = each (%take_counts)) {
        print OUTPUT "$host\t$key\t$value\n";
    }

}

close OUTPUT;

[download]

Thanks again for any help that you can provide.

Kevin M

Replies are listed 'Best First'.
Re: Problem with script? Might be in hash? Help!
by almut (Canon) on Oct 09, 2009 at 11:52 UTC

    I'd wager your problems have to do with this construct: 

    foreach (my ($key, $value) = each (%take_counts)) {
    print OUTPUT "$host\t$key\t$value\n";
}

    [download]

    Compare the following 

    my %take_counts = (
     foo => 1,
     bar => 2,
     baz => 3,
);

print "while:\n";

while (my ($key, $value) = each (%take_counts)) {
    print "\t$key\t$value\n";
}

print "foreach:\n";

foreach (my ($key, $value) = each (%take_counts)) {
    print "\t$key\t$value\n";
}

__END__
while:
    bar    2
    baz    3
    foo    1
foreach:
    bar    2
    bar    2

    [download]

    I supect you meant to get what while (...) would give you...

    (The foreach loop iterates over the two elements $key and $value that are being returned by calling each %take_counts once...)

    ___

    Other than that (though nothing to do with your immediate problem), the usage of {RaiseError=>1} in combination with || die doesn't make much sense: 

    my $dbh = DBI->connect("DBI:mysql:database=$database:host=$host",$db_u
+ser,$db_pass,{RaiseError=>1})|| die "$DBI::errstr\n";

    [download]

    because in case of an error, when RaiseError is set, the connect call would already have died by itself before it gets to execute the || die "$DBI::errstr\n" (i.e. it does not return).   In other words, use either one of those instead 

    my $dbh = DBI->connect("DBI:mysql:database=$database:host=$host",$db_u
+ser,$db_pass) || die "$DBI::errstr\n";

# or
my $dbh = DBI->connect("DBI:mysql:database=$database:host=$host",$db_u
+ser,$db_pass,{RaiseError=>1});

    [download]
[reply]
[d/l]
[select]
      See also Re: Trouble using XML::LibXML for another discussion of the hash iteration problem. (Oh, wait... Since it's the same problem from the same OPer with almost the same code statement, and since the OPer didn't pay any attention to the previous post, it's unlikely any attention will be paid now, so nevermind...)
[reply]
        Thanks much Almut. That worked, switching it to a while loop. Pretty dumb on my part, but that's why you guys are monks! and for the Anonymous poster, I don't even remember post that...but I do read your posts and the correction to that specific issue wasn't changing the loop but moving it outside the firstr loop and using parse_string..so I sort of spaced the while/for part. Thansk again.
[reply]
Re: Problem with script? Might be in hash? Help!
by Anonymous Monk on Oct 09, 2009 at 08:51 UTC
[reply]

Back to Seekers of Perl Wisdom