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

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.