How to delete users without posts

Question

I have about 7800 spam users and only 45 real users. Is there a way to delete users without posts?

Also is there a way to delete questions without answers?

Comments

Update: I had to shut my site down due to spam, so I started a new one.

Answer 1

Method A: Plugin

I found one useful plugin to process bulk action such as block, delete, or send a verification email.

https://github.com/q2a-projects/Q2A-User-Manager

Once enabled the plugin, go to http://example.com/admin/users_management. Here you can access user data in searchable table format.

Method B: phpMyAdmin

• First, take the full backup of your database. 
• Now, open phpMyAdmin. 
• Run following SQL query. It's a dry run to verify the output result that has to be deleted.
• Remember, you should replace "qa" with your table prefix & "0" points with minimum points set for new (or inactive) users.

SELECT * FROM `qa_users`
WHERE userid IN (
    SELECT userid
    FROM qa_userpoints
    WHERE points=0
)

Once verify the output table, you can initiate the actual delete command.

DELETE FROM `qa_users`
WHERE userid IN (
    SELECT userid
    FROM qa_userpoints
    WHERE points=0
)

Now, you need to fire the final query in phpMyAdmin

WHERE userid IN (
    SELECT userid
    FROM qa_userpoints
    WHERE points=0
)

Done..! I have deleted 13k inactive users (no question, answer, comment, etc.).

Comments

Beware that deleting users with 0 points may delete more than just users with no posts, depending on what you assign points for (for example, on my site I implement a StackOverflow-like model where the act of posting something does not give you points, only voting/accepting answers does).

---

Thanks for your answers, but you're a little bit late. I had to shut my old site down because of the spam users. However, I will remember your answer because I started a new community, and I will work on keeping it spam-free ;)

Answer 2

If you're familiar with SQL you can delete users with no posts directly from the database:

DELETE u.*
FROM qa_users u
WHERE userid IN (
  SELECT userid
  FROM (
    SELECT t1.userid
    FROM qa_users t1
      LEFT OUTER JOIN qa_posts t2 ON t1.userid = t2.userid
    WHERE t1.userid > 1
    GROUP BY t2.userid
    HAVING COUNT(t2.userid) = 0
  ) x
);

The WHERE clause t1.userid > 1 is there to prevent the admin account from being deleted (you need to adjust that clause if you have more/other admin IDs).

Similarly you can delete questions with no answers like this:

DELETE q.*
FROM qa_posts q
WHERE postid IN (
  SELECT postid
  FROM (
    SELECT t1.postid
    FROM qa_posts t1
      LEFT OUTER JOIN qa_posts t2 ON t1.postid = t2.parentid
    WHERE t1.type = 'Q'
    GROUP BY t2.postid
    HAVING COUNT(t2.postid) = 0
  ) x
);

The weird double sub-select in both queries is there because for some reason MySQL will throw an error if you try deleting from a single sub-select.