Bulk UnBlacklist emails

ℹ️ Support
#1

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: )

#2

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
Blacklist subscriber vs blacklist email - a summary
#3

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.

#4

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
Is it possible to use the invite plugin several times? (with the same users)
#5

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

#6

Today I tried it again and something went wrong… a lot of php warnings and browser hanged up…
As I have no to much programming knowledge the brute force way to solve it was to wipe out the php_user_blacklist & php_user_blacklist_data tables… /-:

PD: that did NOT solved it… users remains blacklisted… damn

#7

Hello, if you are using phpList 3.3.5 or later you should be able to do that in the following steps.

  1. Export the list of your subscribers email addresses, including the info if they are blacklisted or not. You will need that to re-import them later
  2. Go to “Subscribers”>“Manage Subscribers”> “Reconcile Subscribers”.
    Then click “Delete all blacklisted subscribers”.

Hope this helps,
Suela

#8

Thanks Suela! yes, that should work… will try that way…

BTW, here the php error if someone knows what to change in the php codes up there…:
[25-Aug-2020 08:58:20 America/Chicago] PHP Warning: feof() expects parameter 1 to be resource, bool given in /home/username/public_html/unblacklist.php on line 14
[25-Aug-2020 08:58:20 America/Chicago] PHP Warning: fgets() expects parameter 1 to be resource, bool given in /home/username/public_html/unblacklist.php on line 15

30 seconds of it, generates an errorlog file of near 115mb lol…

Those lines seems to be:
while(!feof($file)) {
$myline=fgets($file);

#9

@luuuciano it appears that your input file is not being found. Try changing that to be the full file-system path to the file.

$file = fopen("unblacklist.txt","r"); // Input file.
1 Like