[wp-hackers] Clearing out bot users

[Ryan Bilesky]

well you could use that query to select the user id's then use two delete
queries one for user table based off the id, one for user meta based off of
user id.  So exactly simple single query but it works.  Also if you intend
to use this in a plugin on not directly on the database you could use
wp_delete_user() which deletes the user and user meta for the user id, you
pass.

On Tue, Feb 15, 2011 at 2:56 PM, Kevin Newman <CaptainN at unfocus.com> wrote:

> Converting that to a single Query that can delete all usermeta rows seems
> difficult for my shallow understanding of SQL.
>
> I'll spend a few more than 5 minutes on that another time. :-)
>
> Kevin N.
>
>
>
> On 2/15/11 5:06 PM, Otto wrote:
>
>> 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
>