wanewsletter_duplicate_mails.pl

• NAME
• DESCRIPTION
• COPYRIGHT

NAME

wanewsletter_duplicate_mails.pl

DESCRIPTION

WANewsletter <http://freshmeat.net/projects/wanewsletter/> cleanup script.

Remove duplicate email entries in the registered users table (wa_abonnes) and keep the last inserted.

COPYRIGHT

Copyright 2000-2007 Etienne LEMEE <coding AT etilem DOT net>

This piece of code is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

#!/usr/bin/perl -w

use strict;
use Mysql;

my ($host, $user, $pass, $base) = qw (
127.0.0.1
root
CHANGEME
newsletter
);

my (%mail, %seen);

my $dbh = Mysql->connect($host, $base, $user, $pass);

my $sth = $dbh->query("select abo_id, abo_email from wa_abonnes");

while (my %h = $sth->fetchhash) {
    my $m = lc $h{abo_email};
    $seen{$m}++;
    push @{$mail{$m}}, $h{abo_id};
}

for my $m (sort keys %mail) {
    my @res = @{$mail{$m}};
    @res = sort {$a<=>$b} @res;
    if ($seen{$m} > 1) {
        printf "===\n%s : %s\n", $m, join(",", @res);
        while (my $d = shift @res) {
            last unless (@res);
            printf "deleting 'abo_id' (%d) from table wa_abonnes\n", $d;
            $dbh->query(sprintf "delete from wa_abonnes where abo_id=%d", $d);
        }
    }
}