Skip to:
Content
Pages
Categories
Search
Top
Bottom

Delete users with no posts

  • 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.

Viewing 8 replies - 1 through 8 (of 8 total)

  • chrishajer
    Participant

    @chrishajer

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

    http://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.

    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
    Participant

    @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_
    Participant

    @_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

    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_
    Participant

    @_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

    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
    Participant

    @mcwebdesignro

Viewing 8 replies - 1 through 8 (of 8 total)

You must be logged in to reply to this topic.