Prune WordPress Users Bash Script For SlickStack

gistfile1.txt

#!/bin/bash

## source ss-config ##
source /var/www/ss-config

## source ss-functions ##
source /var/www/ss-functions

## BELOW THIS RELIES ON SS-CONFIG AND SS-FUNCTIONS


USER_BATCH_LIMIT=10  ## adjust batch size if needed

function delete_fake_users {
    read -p "enter email filter (e.g., '@spam.com' or 'spammy'): " EMAIL_FILTER

    while true; do
        USERS=$(ss_mysql_user "${DB_NAME}" --batch --silent --skip-column-names --execute="
            SELECT u.ID, u.user_login, u.user_email 
            FROM wp_users u
            WHERE u.user_email LIKE '%${EMAIL_FILTER}%'
            AND NOT EXISTS (SELECT 1 FROM wp_posts WHERE post_author = u.ID)
            AND NOT EXISTS (SELECT 1 FROM wp_comments WHERE user_id = u.ID)
            AND NOT EXISTS (
                SELECT 1 FROM wp_usermeta WHERE user_id = u.ID 
                AND meta_key IN ('first_name', 'last_name', 'description') 
                AND meta_value != ''
            )
            LIMIT ${USER_BATCH_LIMIT};
        ")

        if [[ -z "$USERS" ]]; then
            echo "no more fake users found matching '${EMAIL_FILTER}'."
            break
        fi

        echo "users to delete:"
        echo "$USERS" | awk -F'\t' '{printf "ID: %s, Username: %s, Email: %s\n", $1, $2, $3}'

        read -p "delete these users? (y/n) " CONFIRM
        [[ "$CONFIRM" != "y" ]] && break

        while IFS=$'\t' read -r USER_ID USERNAME EMAIL; do
            ss_mysql_user "${DB_NAME}" --execute="
                DELETE FROM wp_users WHERE ID = ${USER_ID};
                DELETE FROM wp_usermeta WHERE user_id = ${USER_ID};
            "

            # verification step
            CHECK=$(ss_mysql_user "${DB_NAME}" --batch --silent --skip-column-names --execute="
                SELECT COUNT(*) FROM wp_users WHERE ID = ${USER_ID};
            ")

            if [[ "$CHECK" -eq 0 ]]; then
                echo "successfully deleted user ID: ${USER_ID}, Username: ${USERNAME}, Email: ${EMAIL}"
            else
                echo "failed to delete user ID: ${USER_ID}, Username: ${USERNAME}, Email: ${EMAIL}"
            fi
        done <<< "$USERS"

        echo "batch completed."
    done
}

delete_fake_users