back to phpList.org

Bulk UnBlacklist emails

Continuing the discussion from UnBlacklist an email?:

Hi,
Scenario, I import 500 emails with foreign key, send the campaign, 60 bounce back,
my bounce rules = Add email address to the do-not-send list and delete bounce (not subscriber).
40 bounces are then corrected and returned to me for the next mailshot.

I can’t delete the 40 as they have already been sent a campaign.
I can update the email as they are based on a foreign key, but they remain blacklisted - despite the bounce ruling (as I understand it ?)

Is there a way I can update and un-blacklist these 40 subscribers other than on a case by case basis ?

TIA

(btw numbers above are for example purposes only :wink: )

I threw this together to try and help you un-blacklist many at once.
STEP 1
Create a plain text file called unblacklist.txt.
Add all of the email addys to that file (one per row).
These are the addys that will be removed from the blacklist.

STEP 2
Copy the following code to a new file and save it as unblacklist.php.
Edit lines 2-6 to have the same values as your PHPList config file.

STEP 3
Upload both files to your site and visit http://yoursite.com/unblacklist.php

<?php
$database_host = "myhost";                /* AVAILABLE IN YOUR CONFIG FILE */
$database_user = "myuser";                /* AVAILABLE IN YOUR CONFIG FILE */
$database_password = "mypassword";        /* AVAILABLE IN YOUR CONFIG FILE */
$database_name = "myDBname";            /* AVAILABLE IN YOUR CONFIG FILE */
$table_prefix = "phplist_";                /* AVAILABLE IN YOUR CONFIG FILE */
$conn = mysqli_connect($database_host, $database_user, $database_password, $database_name);
$good1 = $good2 = $good3 = 0;
$bad1 = $bad2 = $bad3 = 0;
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
$file = fopen("unblacklist.txt","r"); // Input file.
    while(!feof($file)) {
        $myline=fgets($file);
        $myline=rtrim($myline);
        $sql = "SELECT id FROM ".$table_prefix."user_user WHERE email = '".$myline."';";
        if (mysqli_query($conn, $sql)) {
            $result = mysqli_query($conn, $sql);
            $row = mysqli_fetch_array($result);
            $uid = $row['id'];
        } else {
            echo "Could not get userid for: ".$myline." : " . mysqli_error($conn)."<br />";
        };
        $sql = "UPDATE ".$table_prefix."user_user SET ".$table_prefix."user_user.blacklisted = 0 WHERE email = '".$myline."';";
        if (mysqli_query($conn, $sql)) {
            echo "$myline<br />";
            $good1++;
        } else {
            echo "$myline &nbsp; &nbsp; user_user.blacklisted = Error: " . mysqli_error($conn);
            $bad1++;
        };
        $sql = "DELETE FROM ".$table_prefix."user_blacklist WHERE email = '".$myline."';";
        if (mysqli_query($conn, $sql)) {
            $good2++;
        } else {
            echo " &nbsp; &nbsp; user_blacklist deletion = Error: " . mysqli_error($conn);
            $bad2++;
        };
        $sql = "DELETE FROM ".$table_prefix."user_blacklist_data WHERE email = '".$myline."';";
        if (mysqli_query($conn, $sql)) {
            $good3++;
        } else {
            echo " &nbsp; &nbsp; user_blacklist_data deletion = Error: " . mysqli_error($conn) ."<br />";
            $bad3++;
        };
        $sql = "DELETE FROM ".$table_prefix."user_user_history WHERE userid = '".$uid."' AND summary LIKE 'Added to blacklist%';";
        if (mysqli_query($conn, $sql)) {
            $good3++;
            echo "SUCCESS";
        } else {
            echo " &nbsp; &nbsp; user_user_history deletion = Error: " . mysqli_error($conn) ."<br />";
            $bad3++;
        };
    };
fclose($file);
mysqli_close($conn);
echo "$good1 user_user.blacklist set to 0 &nbsp; &nbsp; $bad1 failed<br />$good2 user_blacklist rows deleted &nbsp; &nbsp; $bad2 failed<br />$good3 user_blacklist_data rows deleted &nbsp; &nbsp; $bad3 failed";
?>

This should do what you want, but PLEASE backup your database before you do this.

1 Like

Wow,
thanks VERY much Chris,
This is REALLY appreciated, I will test it as soon as I get back to work on Tuesday, and post the results back here, I’m pretty sure I won’t be the only one to find this useful.

Big Thanks again.

This all said, you should be careful - to remove bounced subscribers from the blacklist indiscriminately could destroy your IP rating pdq.

If you are mailing dead emails, or worse people who have clicked unsubscribe (who are also on the blacklist), you will end up on every blocklist and spamlist there is :confused: Tread lightly! x

1 Like

Thanks a lot for sharing this!
I have used trying to reset the invite plugin https://github.com/phpList/phplist-plugin-invite
But it seems that I have to do something else (I can not send a new invitation to previously blacklisted->unblacklisted accounts… and moved to a “2nd invitation” list)
And I have no idea what to do now… lol