troubleshooting Question

While loop not working, please help!

Avatar of mhouldridge
mhouldridge asked on
Scripting Languages
4 Comments1 Solution386 ViewsLast Modified:
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++;
        }
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros