Thread

After moving a wordpress blog to EE I noticed a lot of spam comments and members.

I’ve deleted many members, but what’s a good way of deleting orphaned comments? Thanks!

Are there any guest comments or where they all made members (now ex-members)?

All ex members, now deleted.

We can probably do this with a sql query.
Is it safe to assume the logic of:
IF user exists (registered member) - leave all their comments alone otherwise delete all?

yes! that would be exactly it. Thanks for the help so far lebisol.

Ok first rule BACKUP your database.
Second rule of fight club is? 😊

With that backup stashed away go to phpMyAdmin and paste this into SQL tab:

DELETE FROM exp_comments
WHERE NOT EXISTS
(SELECT * FROM exp_members
WHERE exp_members.member_id = exp_comments.author_id);

Edit Note: just realized that ‘recent_comment_date’,‘comment_total’ & ‘trackback_total’ are stored along with an entry (in exp_weblog_titles table) and would need to be updated as well….for the stats sake.

lebisol this is awesome, thanks so much. I just realized I deleted a little too many things, so now I have to start from scratch again.

*sigh*

Thanks for your help!

No problem, keep the backup handy and starting from scratch is not the end of the wold as long as you have the original content. I have not tried much with php and scripts but I am sure this can be made into smoother transaction. Sounds like a good project but I would need to learn some EE-php tricks.
For now, raw sql works.

Come to think of it…while sql is faster you can still do this through CP Home ›  Edit ›  Comments .
Any author that does not have their name as a link is considered ‘guest/nonmember’.
Depending how you imported entries and comments you still might have to re-set the count of comments.
What about track backs/pings?
Let me know…