Database Maintenance Script - Delete High Bouncecount Users

💻 Development
1

This script will allow you to delete users that are not blacklisted and have a high bouncecount.
I know PHPList already has the ability to do this. HOWEVER, for some unknown reason, it doesn’t get them all.
I do not recommend using this script unless you have decent filters in place for bounce processing.
Make a backup of your DB before playing with this for the first time.

Edit lines 5-10 with your info.
Upload the file to your server.
Use a browser to visit the URL of the script and it will execute.

<?php

// ----- DELETES users with a high bouncecount that are not blacklisted. --------

$servername = "mydomain.com";        //ENTER YOUR DOMAIN NAME
$username = "mydbusername";            //ENTER YOUR DATABASE USERNAME
$password = "mydbpassword";            //ENTER YOUR DATABASE PASSWORD
$dbname = "mydbname";                //ENTER YOUR DATABASE NAME
$dbprefix = "phplist";                //REPLACE WITH YOUR DATABASE PREFIX IF DIFFERENT THAN DEFAULT
$threshold = "10";                //ENTER THE AMOUNT OF BOUNCES THAT USERS WILL BE DELETED FOR HAVING
$conn = mysqli_connect($servername, $username, $password, $dbname);

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
};

$query = "SELECT id, email FROM ".$dbprefix."_user_user WHERE bouncecount >= '".$threshold."' AND blacklisted = '0';";
$result = mysqli_query($conn, $query);
if($result === FALSE) {
    echo "$query";
    die("Connection failed: " . mysqli_connect_error());
} else {
    $cnt = 0;
    while($row = mysqli_fetch_array($result)){
        mysqli_query($conn, "DELETE FROM ".$dbprefix."_user_user WHERE id='$row[id]';");
        mysqli_query($conn, "DELETE FROM ".$dbprefix."_user_blacklist WHERE email='$row[email]';");
        mysqli_query($conn, "DELETE FROM ".$dbprefix."_user_blacklist_data WHERE email='$row[email]';");
        mysqli_query($conn, "DELETE FROM ".$dbprefix."_user_message_bounce WHERE user='$row[id]';");
        mysqli_query($conn, "DELETE FROM ".$dbprefix."_user_message_forward WHERE user='$row[id]';");
        mysqli_query($conn, "DELETE FROM ".$dbprefix."_user_user_history WHERE user='$row[id]';");
        mysqli_query($conn, "DELETE FROM ".$dbprefix."_listuser WHERE userid='$row[id]';");
        $cnt = $cnt + 1;
        echo "$row[email]<br>";
    }
}
mysqli_close($conn);
echo "$cnt Addy's expunged for bouncecount over $threshold that are not blacklisted";

?>
2 Likes