[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

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++;
        }
0
mhouldridge
Asked:
mhouldridge
  • 2
1 Solution
 
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
 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now