Hi, I need help with this script. I have 2 tables in the database, Registrations, and Alerts. People signup to get email alerts from our site and their email address is stuff ed into the Alerts table. This script takes the bad email addresses (that bounce) and the script deletes the emails from the alerts and registrations table. My problem is that we store the email addresses in a single field separated by ~~~ (3 tildas) in the alerts database. The script works but after it runs we sometimes end up with emails in a single string that are NOT separated by the ~~~. In looking at the script, it looks correct to me. Can someone find where I'm going wrong? 
#!/usr/bin/perl

print "Content-type: text/html\n\n";

######## REQUIRED FILES ###########
require "cgi-lib.pl" || die "Error loading cgi-lib.pl";
require "db-common.sub" || die "Error loading db-common.sub";

$alertstable = "alerts";
$regtable = "registrations";


&Conn_to_DB;



###### Connect to bad emails database and get all the emails
$SQL = "Select * from bademails ORDER BY ID DESC";
&Do_SQL;

while ($pointer = $sth->fetchrow_hashref) {
    $bademail = $pointer->{'email'};

    

    ###### Connect to alerts database and get the entries that match t
+he bademail list
    $SQL2 = "Select * from $alertstable where email LIKE '%$bademail%'
+";
    &Do_SQL2;

    while ($pointer2 = $sth2->fetchrow_hashref) {
    $item = $pointer2->{'jobtype'};
    $alerttype = $pointer2->{'alerttype'};
    $emaillist = $pointer2->{'email'};
    $ID = $pointer2->{'ID'};

    @emails = split /~~~/,$emaillist;
    @goodemails = ();
    foreach $address (@emails) {

    #print "$ID, $address<Br>\n";
 

   if ($address ne $bademail) {
        push @goodemails,$address;
        }


   } #end foreach


   $newlist=join('~~~',@goodemails);
   $SQL3 = "UPDATE $alertstable SET email='$newlist' WHERE jobtype='$i
+tem' and alerttype='$alerttype'";
   &Do_SQL3;
    print "updated $ID, $elist<Br>\n";

  } #end  while ($pointer2 = $sth2->fetchrow_hashref) {



    ###### Connect to reg database and set the alerts to nothing if it
+ matches a bademail
    $SQL3 = "UPDATE $regtable SET alerts='$alerts' WHERE email='$badem
+ail'";
    &Do_SQL3;
    print "Reset Reg. ID: $ID, $bademail<Br>\n";



    
  } # end while

$dbh->disconnect;
exit;

[download]

In reply to Help with script by htmanning

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • 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! —
  • 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:
    & &amp;
    < &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.