Simple Machines Forums (SMF) and Deleting Multiple Posts by One User

OK folks, I came up with something helpful and I thought I would pass it on.  Recently I had a spam bot (or maybe a real person, who knows) join the forums.  Went right through all of the security controls and verifications.  Then, said user went about posting almost 500 porn posts all over my site’s forums.  Posting topics, posts, pics and embedded videos, it was really bad and I was really mad.  First off I banned the user, no problem.  I figured I would do that so I could keep relevant info like IP and email addresses rather than just deleting him.  Now I had the arduous task of removing all those damn posts and topics.

I do know that you can delete a user and tell SMF to also delete all of those users topics posts as well, but that hasn’t worked in the past so I did it on my own. Here is what I did.

I dug around in the database at the MySQL prompt and determined what his user ID was, and what to key on to remove all of his posts and topics.  Here are a few simple queries that worked wonders for me and saved a lot of time.  I would think they would do the same for you, but remember that anything can happen so BACKUP YOUR DATABASE FIRST!

*Note: All of these queries use smf_ as the prefix to the tables, that is the default and of course will have to be changed if you used something different!

OK, so first we need to figure out the ID number of the offending user.  I simply did a query on all users, listing the user ID and name and then looked through until I found the one I was looking for, like this:

select ID_MEMBER, memberName from smf_members;

If you want, you can specify the username to just get that record, might depend on how many users you have.  Sometimes I just like to do things manually to be thorough.  It would look like this (naturally put the real username in there and keep the single quotes):

select ID_MEMBER, memberName from smf_members where memberName = 'spambotusername';

Once you have the user ID, you can then remove all of the posts and topics.  For this example, I will use the ID of 10 just to make it simple.  It will look like this (no single quotes here because it’s a number and not text):

delete from smf_messages where ID_MEMBER = 10;

Now you can delete all of the topics:

delete from smf_topics where ID_MEMBER_STARTED = 10;

And there you have it, all of the offending messages from that user are gone.  Of course, if you have many spammers you’ll have to repeat this process for each one.  I then recommend going to forum maintenance in the admin panel and running the various repair tools to clean things up in the database.  I hope this helps, and like I said before, backup your database and files before you start monkeying with anything.  I can’t be held responsible if something gets foobar’d.

Hey, why don’t you discuss this with other users over in the forums!

Enjoy!  ;D

3 thoughts on “Simple Machines Forums (SMF) and Deleting Multiple Posts by One User

  1. This looks cool so far, what’s up people?
    If it’s not just all bots here, let me know. I’m looking to network
    Oh, and yes I’m a real person LOL.


  2. Hey thanks for the appropiate sql I appreciate it as I am spam heavy and was looking for how to del these people and there topics as in one shot. You have to also remember to put the correct sql statement otherwise you will get an error. example: SELECT ID_MEMBER, ‘membername’
    FROM smf_members; other than that thanks mucho !

  3. I followed this a couple weeks ago, deleting all the posts by a certain person since the board did not ask me if I wanted to remove the posts for some reason (spammer posting 1700 posts in spanish) but now I am getting some wierdness. The posts are gone but the page counts are the same, even though the pages are not there. Also some topics have lets say pages 1,2,3,4,5 and pages 1 and 5 have the same two posts on them, while 2,3,4 are blank/empty.

Tell me what you are thinking?