While loop not working, please help!

Experts,

The following while loop is not working, and I cant figure out why.  The first part goes through fine, but then stops when finished and does not go on to the next day. Here is the script;

$backdays = 1;


        while ($backdays <= 14){

                $total = 0;
              $index = 0;

              use DBI;
              $driver = "mysql";
              $database = "message";
              $hostname = "localhost";
              $dbuser = "root";
              $dbpassword = "password";
              $dsn = "DBI:$driver:database=$database;host=$hostname";
              $dbh = DBI->connect($dsn, $dbuser, $dbpassword);

              ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime(time-($backdays*24*60*60));
            $mon = $mon + 1;
            $year = $year + 1900;
            $mon = sprintf("%02d", $mon);
            $mday = sprintf("%02d", $mday);
            $yesterday = $year . "-" . $mon . "-" . $mday;

                print "\nGETTING TOTALS FOR $yesterday \n\n";


                      foreach $email(@emailaddress){

                      @domainparts = split(/\@/,$emailaddress[$index]);
                      $domainpart[$index] = $domainparts[1];
                      $domainpart[$index]=~ y/\./\_/d;

                      # GET TOTAL NUMBER OF RECEIVED EMAILS
                      $data = $dbh->prepare("SELECT COUNT(emailid) FROM `$domainpart[$index]` WHERE `emailto` = '$emailaddress[$index]' AND `direction` = 'i' AND `archivestatus` = 'a' AND date = '$yesterday'");
                      $data->execute();
                      $row = $data->fetchrow_hashref();
                      $received[$index] = $row->{'COUNT(emailid)'};
                      if ($received[$index] == ""){
                      $received[$index] = 0;
                      }
                      print "U: $emailaddress[$index] | D: $yesterday | ";
                      print "Gen: $received[$index] | ";
                      $data->finish();

                      # GET TOTAL NUMBER OF  SENT EMAILS
                      $data2 = $dbh->prepare("SELECT COUNT(emailid) FROM `$domainpart[$index]` WHERE `emailfrom` = '$emailaddress[$index]' AND `direction` = 'o' AND `archivestatus` = 'a' AND date = '$yesterday'");
                      $data2->execute();
                      $row2 = $data2->fetchrow_hashref();
                      $sent[$index] = $row2->{'COUNT(emailid)'};
                      if ($sent[$index] == ""){
                      $sent[$index] = 0;
                      }
                      print "S: $sent[$index] | ";
                      $data2->finish();

                      # GET TOTAL NUMBER OF VIRUSES
                      $data3 = $dbh->prepare("SELECT COUNT(emailid) FROM `$domainpart[$index]` WHERE `emailto` = '$emailaddress[$index]' AND `direction` = 'i' AND `held` = 'v' AND date = '$yesterday'");
                      $data3->execute();
                      $row3 = $data3->fetchrow_hashref();
                      $viruses[$index] = $row3->{'COUNT(emailid)'};
                      if ($viruses[$index] == ""){
                      $viruses[$index] = 0;
                      }
                      print "V: $viruses[$index] | ";
                      $data3->finish();

                      # GET TOTAL NUMBER OF SPAM
                      $data4 = $dbh->prepare("SELECT COUNT(emailid) FROM `$domainpart[$index]` WHERE ((`emailto` = '$emailaddress[$index]' AND `direction` = 'i' AND held = 'y' AND date = '$yesterday') OR (`emailfrom` = '$emailaddress[$index]' AND `direction` = 'o' AND held = 'y' AND date = '$yesterday'))");
                      $data4->execute();
                      $row4 = $data4->fetchrow_hashref();
                      $spam[$index] = $row4->{'COUNT(emailid)'};
                      if ($spam[$index] == ""){
                      $spam[$index] = 0;
                      }
                      print "SPM: $spam[$index] | ";
                      $data4->finish();

                      # CALCULATE ARCHIVE SIZE FOR USER
                      $data5 = $dbh->prepare("SELECT SUM(size) FROM `$domainpart[$index]` WHERE archivestatus = 'a' AND ((emailto = '$emailaddress[$index]' AND direction = 'i' AND date = '$yesterday') OR (emailfrom = '$emailaddress[$index]' AND direction = 'o' AND date = '$yesterday'))");
                      $data5->execute();
                      $row5 = $data5->fetchrow_hashref();
                      $userarchivesize[$index] = $row5->{'SUM(size)'};
                      if ($userarchivesize[$index] == ""){
                      $userarchivesize[$index] = 0;
                      }
                      print "A: $userarchivesize[$index] \n\n";
                      $data5->finish();
                      $index++;
                      $total++;
                        }

                      print "Task complete: $total users processed \n";


#------------------------------------------------------------------------------- INSERT $sent ARRAY ITEMS TO DATABASE

              use DBI;
              $driver = "mysql";
              $database = "statistics";
              $hostname = "localhost";
              $dbuser = "root";
              $dbpassword = "password";
              $dsn = "DBI:$driver:database=$database;host=$hostname";
              $dbh = DBI->connect($dsn, $dbuser, $dbpassword);

                    $counter = 0;
                      foreach $email(@emailaddress){
                      @domainparts2 = split(/\@/,$emailaddress[$index]);
                      $domainpart2[$index] = $domainparts2[1];
                      my $data6 = $dbh->prepare("INSERT INTO `statsmain` (`emailaddress`, `memberof`, `domain`, `received`, `sent`, `viruses`, `spam`, `archivespace`, `archivelimit`, `date`) VALUES ('$emailaddress[$counter]', '$memberof[$counter]', '$domainpart2[$counter]', $received[$counter], $sent[$counter], $viruses[$counter], $spam[$counter], $userarchivesize[$counter], $memberoflimit[$counter], '$yesterday')");
                      $data6->execute();
                      $counter++;
                      }

              print "\n\n finished $yesterday \n\n";
              $data6->finish();

        $#emailaddress = -1;
        $#userarchivesize = -1;
        $#domainparts2 = -1;
        $#domainparts = -1;
        $#domainpart = -1;
        $#spam = -1;
        $#viruses = -1;
        $#sent = -1;
        $#received = -1;
        $#total = -1;
      $backdays++;
        }
mhouldridgeAsked:
Who is Participating?
 
ozoCommented:
> The last statement that completes is the insert statement
Does that mean that the

$counter++;
print "\n\n finished $yesterday \n\n";
$data6->finish();

do not execute?
I would expect the $data6->finish(); to fail
and to print an error message under use CGI::Carp qw(fatalsToBrowser);

because $data6 is not defined
It was only defined within the scope of the foreach loop
0
 
ozoCommented:
What's the last statement that successfully executes?
Do you get any error messages with
 use CGI::Carp qw(fatalsToBrowser);

0
 
mhouldridgeAuthor Commented:
The last statement that completes is the insert statement and the values are added to the database.

use CGI::Carp does not work.
0
 
FishMongerCommented:
>> use CGI::Carp does not work.

What does that mean?  Is the module installed?  Do you get a compilation error?  Do you get any error messages?  Have you checked the web server error log?

Have you tried:
 use CGI::Carp qw(fatalsToBrowser warningsToBroswer);
 warningsToBrowser(1);


Do you have warnings enabled and are you using the strict pragma?
use warnings;
use strict;

Why are you trying to load the DBI module in each iteration of the loop?

I'd think these lines would be a problem:
        $#emailaddress = -1;
        $#userarchivesize = -1;
        $#domainparts2 = -1;
        $#domainparts = -1;
        $#domainpart = -1;
        $#spam = -1;
        $#viruses = -1;
        $#sent = -1;
        $#received = -1;
        $#total = -1;

$#emailaddress returns the index number of the last element of the @emailaddress array and is normally used like this (somewhat verbose):
my $number_of_emailadress_indexes = $#emailaddress;

Retrieving your $yesterday date could be simplified to this:

use POSIX 'strftine';  # declared outside of the loop
$yesterday = strftime("%Y-%m-%d", localtime(time - $backdays*24*60*60));
print "\nGETTING TOTALS FOR $yesterday \n\n";
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.