Support » Fixing WordPress » Best way to delete users?

  • Hi all,

    I have plenty of users registered that have either never posted or are spam related. What’s the best way for me to mass delete these users that have never posted.

    Through WordPress, I see the post count but is a pain to go through so many. Through phpMyAdmin, I can delete more in bulk but do not know the post count.

    Any other way to manage this?

    Thanks in advance…

Viewing 7 replies - 1 through 7 (of 7 total)
  • BE SURE TO BACK UP YOUR DATABASE FIRST!!

    To select users who have no posts or comments, you can use this:

    SELECT *
    FROM <code>wp_users</code>
    WHERE ID NOT IN
    (SELECT post_author FROM wp_posts
    UNION SELECT user_id FROM wp_comments)

    Then, replace ‘SELECT *’ with ‘DELETE’ to actually delete the records.
    You will then have some ‘orphan’ records in wp_usermeta. Select them with this:

    SELECT *
    FROM <code>wp_usermeta</code>
    WHERE user_id NOT IN
    (SELECT ID FROM wp_users)

    Then, replace ‘SELECT *’ with ‘DELETE’ to actually delete the records.

    vtxyzzy,

    Thank you much for the reply.

    Have you tried this before? I was kind of hoping not to run these kind of queries against the database. Afraid something will go wrong.

    I guess it seems too simple. With that said, will this be relatively safe to run?

    Thanks again

    I ran this against my test database before I posted the answer.

    That being said, BE SURE TO BACK UP YOUR DATABASE FIRST!!! And, be sure you can restore from your backup.

    vtxyzzy,

    I get an error when running the SELECT query to test first.

    Here is the error…

    ERROR: Unknown Punctuation String @ 29
    STR: </
    SQL: SELECT *
    FROM wp_users
    WHERE ID NOT IN
    (SELECT post_author FROM wp_posts
    UNION SELECT user_id FROM wp_comments)

    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘wp_users
    WHERE ID NOT IN
    (SELECT post_author FROM wp_posts
    UNION’ at line 2

    Sorry about that – MySql put backticks around the wp_users name. Take them out.

    But there are no backticks in the code. Am I missing something here?

    What’s the correct code for it then?

    Thanks again

    Double confusion!! MySQL puts in backticks. WordPress.org converts them to < code > and </ code >.

    Correct syntax is this (nothing around wp_users):

    FROM wp_users
Viewing 7 replies - 1 through 7 (of 7 total)
  • The topic ‘Best way to delete users?’ is closed to new replies.