My Forums

I absolutely hate advertisement bots, and i wish that their inventors would be blessed with a flesh eating virus. I have been fighting a losing battle over the last two years to regain control over my forums, claiming it back from the shitty bots, and i think i have finally figured things out.

Below is the beginnings of a script that you can run on a semi regular basis and keep your PHPBB3 Databases cleaned of ridiculous bullshit landing on your forum. Just remember to log in from time to time to resynchronize your statistics.

CREATE TEMPORARY TABLE ShittyUsers(
    user_id int(11) NOT NULL
);

INSERT INTO ShittyUsers
    (user_id)
SELECT
    users.user_id
    FROM `bbwd23_users` as users
    LEFT JOIN `bbwd23_posts` as p ON poster_id = user_id
    WHERE
        user_type != 2
    GROUP BY user_id
    HAVING COUNT(p.post_id) = 0;

DELETE
    FROM `bbwd23_posts`
    WHERE
        poster_id IN (SELECT user_id FROM ShittyUsers)
            AND
        post_approved = 0;

DELETE
    FROM `bbwd23_posts`
    WHERE
        poster_id IN (SELECT user_id
	    FROM `bbwd23_users`
		WHERE
		    user_posts = 0
			AND
		    user_type != 2)
        AND
        post_approved = 0;

DELETE
    FROM `bbwd23_users`
    WHERE
        user_id IN (SELECT user_id FROM ShittyUsers)
        AND
        user_type != 2;

DROP TABLE ShittyUsers;

DELETE
    FROM `bbwd23_topics`
    WHERE topic_approved = 0;