#!/usr/bin/perl use strict; use warnings; use DBD::mysql; ###################### #### CONFIG VARS ##### ###################### #Specify start and end dates to filter data my $start_date = '2008-12-25'; my $end_date = '2009-04-01'; my $output_file = 'Hordis_Item_Overview.txt'; ###################### #### PRIVATE VARS #### ###################### my $db_user = 'xxxxx'; my $db_pass = 'xxxxx'; ###################### #### MAIN BODY ####### ###################### open OUTPUT, ">$output_file"; my $dbh = DBI->connect("DBI:mysql:database=xxxx:host=xxxxx",$db_user,$ +db_pass,{RaiseError=>1})|| die "$DBI::errstr\n"; #Hash to hold ItemUID and Item Name my %ItemUID= (); #Get ItemUID and Item Name (ItemUID is unique to each Test.IData) { my $sql = "SELECT t.IData FROM Tests AS t JOIN Assignments AS a ON a.A +ssignmentTestID=t.ID JOIN Users AS u ON u.ID=t.UserID WHERE u.Institu +tionID='004452' AND t.IData REGEXP 'ch01.03.MultipleChoice 049';"; my $sth = $dbh->prepare ( $sql ); $sth->execute(); while (my @row = $sth->fetchrow_array()) { my $idata = $row[0]; while ($idata =~ /<item([^>]+)/gi) { my $elements = $1; my $src; my $uid; if ($elements =~ /src=\"\w+\/([^"]+)/i) { $src = $1 } if ($elements =~ /uid=\"([^"]+)/i) { $uid = $1 } if (not exists $ItemUID{$uid}) { $ItemUID{$uid} = $src } } } $sth->finish(); } #Hold Unique Item Names to use against ItemUID hash @hvalues=(); while (my ($key, $value) = each $ItemUID) { push #Get Item Specific Info for Each Student/Assignment { while (my ($key, $value) = each %ItemUID) { print "Processing $key/$value\n"; my $sql = "SELECT s.Name, CONCAT(u.LastName,', ',u.FirstName), a.N +ame, TRUNCATE((MAX(air.TimeSpent)/60),0), TRUNCATE((MIN(air.TimeSpent +)/60),0), SUM(air.TimesTaken), AVG(air.Score), air.FeedbackAttempts, +air.HintsGiven FROM Sections AS s JOIN Assignments AS a ON s.ID=a.SectionID JO +IN Users AS u ON u.ID=s.OwnerID JOIN AssignmentResults AS ar ON a.ID= +ar.AssignmentID JOIN AssignmentItemResults AS air ON ar.ID=air.Assign +mentResultsID WHERE air.ItemUID IN '$key' GROUP BY s.Name ORDER BY s.Name, a. +Name, u.LastName"; my $sth = $dbh->prepare ( $sql ); $sth->execute(); while (my @row = $sth->fetchrow_array()) { print OUTPUT "$row[0]\t$row[1]\t$row[2]\t$row[3]\t$row[4]\t$ro +w[5]\t$row[6]\t$row[7]\n"; } $sth->finish(); } } $dbh->disconnect(); close OUTPUT; ###################### #### PRIVATE SUBS #### ######################
In reply to Creating Array from Hash that pulls only Unique values while iterating over each Unique Value by kdmurphy001
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |