[wp-hackers] Clearing out bot users

[Otto]

Make sure you delete their usermeta rows too.

Also, *BACKUP FIRST*. Obviously. :)

-Otto



On Tue, Feb 15, 2011 at 3:55 PM, Kevin Newman <CaptainN at unfocus.com> wrote:
> Hey,
>
> I'd like to clear out all those thousands of bot users. I came up with this
> SQL statement to list all the spam users:
>
> SELECT * FROM wp_users AS u
> JOIN wp_usermeta AS mv ON mv.user_id = u.ID
> WHERE mv.meta_key = 'wp_capabilities' AND mv.meta_value LIKE '%subscriber%'
> AND ( SELECT count(*) FROM wp_comments AS c WHERE c.user_id = u.ID ) <= 0
> AND ( SELECT count(*) FROM wp_posts AS p WHERE p.post_author = u.ID ) <= 0
>
> This could be converted into a delete statement to nuke any user that has no
> posts or comments and is a subscriber.
>
> Did I miss anything?
>
> Thanks,
>
> Kevin N.
>
>
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>