A long over due update to this thread. The original MySQL queries would delete a lot of valid users with valid posts. Here is a refined Query that will delete only users that have no posts, have not validated their email, have not accepted the board rules, are over 50 days old and have no private messages.
The reason for all the checks for no this and no that, are from members that signed up back in the early days of this forum when a lot of features didn't exist. Our board dates back to 2000.
Here is the query to find bogus users:
SELECT USER_ID
FROM ubbt_USERS
WHERE USER_ID in (
SELECT u.USER_ID FROM ubbt_USERS as u
JOIN ubbt_USER_DATA AS ud
ON ud.USER_ID = u.USER_ID
JOIN ubbt_USER_PROFILE as uup
ON u.USER_ID=uup.USER_ID
LEFT JOIN ubbt_PRIVATE_MESSAGE_USERS AS upmu
ON upmu.USER_ID = u.USER_ID
LEFT JOIN ubbt_PRIVATE_MESSAGE_TOPICS AS upmt
ON upmt.TOPIC_ID = upmu.TOPIC_ID
AND upmt.USER_ID = u.USER_ID
LEFT JOIN ubbt_PRIVATE_MESSAGE_POSTS AS upmp
ON upmp.TOPIC_ID = upmu.TOPIC_ID
AND upmp.USER_ID = u.USER_ID
WHERE USER_TOTAL_POSTS=0
AND upmu.USER_ID IS NULL
AND upmt.USER_ID IS NULL
AND upmu.USER_ID IS NULL
AND USER_LAST_VISIT_TIME < unix_timestamp(now()) - (60*60*24*50)
AND USER_REGISTERED_ON=USER_LAST_VISIT_TIME
);
And the query to delete those users:
DELETE u, ud, uup
FROM ubbt_USERS as u
JOIN ubbt_USER_DATA AS ud
ON ud.USER_ID = u.USER_ID
JOIN ubbt_USER_PROFILE as uup
ON u.USER_ID=uup.USER_ID
LEFT JOIN ubbt_PRIVATE_MESSAGE_USERS AS upmu
ON upmu.USER_ID = u.USER_ID
LEFT JOIN ubbt_PRIVATE_MESSAGE_TOPICS AS upmt
ON upmt.TOPIC_ID = upmu.TOPIC_ID
AND upmt.USER_ID = u.USER_ID
LEFT JOIN ubbt_PRIVATE_MESSAGE_POSTS AS upmp
ON upmp.TOPIC_ID = upmu.TOPIC_ID
AND upmp.USER_ID = u.USER_ID
WHERE USER_TOTAL_POSTS=0
AND upmu.USER_ID IS NULL
AND upmt.USER_ID IS NULL
AND upmu.USER_ID IS NULL
AND USER_LAST_VISIT_TIME < unix_timestamp(now()) - (60*60*24*50)
AND USER_REGISTERED_ON=USER_LAST_VISIT_TIME;
)
Previous Topic
Index
