[marky]

Over the years I’ve managed to collect quite a few “spam” users that haven’t posted anything, but have spammy usernames and websites. Is there a way to quickly delete all the users that haven’t posted anything?

I have thousand of such users (no kidding!), so I really don’t want to go and delete them manually.

[chrishajer]

Hopefully, you’ve added the Human Test plugin to prevent spam registrations.

https://bbpress.org/plugins/topic/human-test/

I don’t know of a way to mass moderate users in bbPress. If you’re integrated with WordPress, I believe there is a mass edit for users available there.

If you’re not integrated, I think you need to use some SQL with the database directly. If you need that, please post the MySQL version you are using (since subqueries were added in MySQL 4.1, and the solution would be different if you’re using MySQL prior to 4.1)

I’ve been playing with the SQL to do this, but my MySQL version is 4.0.27, which does not support subqueries.

[marky]

Thanks for the speedy reply. I just installed the Human Test plugin today, thankfully. I wish I knew about it sooner; perhaps I wouldn’t have this mess to clean up now.

At any rate, I’m not integrated—it’s a stand-alone forum. I’m running MySQL 4.1.16, according to phpMyAdmin.

[chrishajer]

OK, so you can’t use the WordPress user management functions. With MySQL 4.1.* you can use subqueries. Are you comfortable making a database backup, and then are you comfortable with a tool like phpMyAdmin? I ask because I will explain how I would do it to take care of the problem one time. I wouldn’t necessarily call it the best way or the only way, just the way I would do it with things I would check beforehand.

Might as well make a backup of the bbPress database now.

[_ck_]

This will show in you in PHPMYADMIN all users older than at least a month who have never posted:

SELECT ID,user_login,user_registered FROM wp_users

LEFT JOIN bb_posts ON ID=poster_id

WHERE user_registered<DATE_SUB(CURDATE(),INTERVAL 30 DAY)

AND poster_id is NULL

[marky]

Thanks again for the swift response. I am somewhat familiar and comfortable with phpMyAdmin and MySQL. I’m not at all familiar with the bbPress schema, though.

I regularly backup the database anyway, but I’ll certainly make a special backup just before running any manual modifications just in case.

_ck_, thanks for the query. Could I delete all the users returned by that query? I notice there’s also a table of user meta data; could I delete those based on the IDs returned by that query as well? Again, I’m not familiar with the bbPress schema, so I don’t know if there are any dependencies or foreign keys to worry about.

[_ck_]

We could make another query to delete usermeta where the id doesn’t appear in wp_users.

I’m not crazy about deleting rows entirely but I guess it could not hurt if the user id # never gets reused, which mysql should not do.

Definitely do a backup first.

This will show all “orphaned” usermeta:

SELECT user_id,meta_key, umeta_id FROM wp_usermeta

LEFT JOIN wp_users ON user_id=ID

WHERE ID is NULL

so delete the wp_users rows first using the first query and then delete the orphaned usermeta

[marky]

Just wanted to send a quick “thank you!”

The queries worked perfectly, as far as I can tell. (I had to change some of the table names from wp_ to bb_, since I’m not using integration, but other than that it all worked flawlessly.)

Some of my spam users went as far back as 6 years from when I imported everything to bbPress from phpBB, so it was definitely time for some housecleaning.

Thanks again for the assistance.

[mcwebdesignro]

You can use this plugin: https://wordpress.org/plugins/no-posts-user-delete/