Bulk delete posts

[nizarala]

I am new to this community, and I don't know much about programming. I searched for hours, but could not find an answer to my question in this forum. Maybe I am using the wrong keywords, so forgive me if this has already been answered somewhere else.

I have been running a forum for my undergraduate course since 2011. I only moderate it while the class is happening, and I never had any major problem with spam. This Fall, while I was not monitoring it, it was attacked by spammers. I deleted all the spam members and added more security for new members, so that I am not having any new spam. Now I want to delete the posts made by the spammers. How can I bulk delete them? One thing that may help: any post in the year of 2014 is a spam and can be automatically deleted, so any script that allowed me to simply delete all posts in 2014 would be immensely helpful.

Thank you!

[Gwenwyfar]

You can activate the "quickmod" feature on your profile settings, and set it to checkbox, then a checkbox will appear on the side of posts and topics to do anything in bulk with them.

Not sure this is just me not understanding how it works or if something broke the feature, but I never found how to use it on posts though, when I select multiple posts no button for "delete all of them" appears 

[Deaks]

If you go to Admin -> Maintenance -> Forum Maintenance -> Topics

You can delete topics that were created in whatever time frame, im guessing by what you said its only spam on your forum and few related posts?

[nizarala]

Thanks for the replies!

Fortytwo, like you I have the checkboxes, but when I click on them no "delete" button shows up. I can only find a "Remove" option she I click on an individual topic.

Runic, I go to that location, but I see no option to remove topics in a specific time frame. The only option I see is to remove topics that have no posts in the last X days. I cannot use this function. The forum has over a thousand legitimate posts from previous semesters. They have no new posts on them, but it is important to keep the archives. The students on each semester can benefit from the previous discussions.

[Kindred]

No, there is no option to bulk delete posts from a specific timeframe.

Th checkboxes are really the only way to bulk delete...

[Gwenwyfar]

For multiple topic moderating (including deleting) you will have the option to do that on the action select list in the bottom of the topic list.

The checkboxes on posts are what I don't know how works (if it works)

[Sir Osis of Liver]

You can do it with a MySQL query in phpmyadmin.

Code Select Expand



DELETE FROM smf_messages WHERE poster_time BETWEEN 1388534401 AND 1420070399



This will delete all messages posted between Jan 1, 2014 00:01 and Dec 31, 2014 23:59.  If they are all spam, shouldn't cause any crosslink problems.  BACKUP YOUR DATABASE BEFORE YOU TRY THIS!!!!!

[nizarala]

Thanks Fortytwo and Kindred, I got it!  It is not the most efficient way, but it beats doing it one by one.

Krash, this seems like it would completely solve my problem. Forgive my ignorance, but I have no idea how to go about running this. Could you perhaps walk me through the steps? Thank you!

[Kindred]

The thing is, deleting messages directly in the database like that leaves things misconnected and uncleaned-up...

[Arantor]

The thing is, deleting messages directly in the database like that leaves things misconnected and uncleaned-up...

...and even running the maintenance won't get them all either.

[Sir Osis of Liver]

You access phpmyadmin in your host cpanel, MySQL database section.  Do a backup by exporting the db to your local computer, then do it again, make sure both files are same size.  If your host has a db backup feature, do that as well.  Once you're sure you have a good backup, find the query button in phpmyadmin, copy the query into the box, and run it.  It will delete all 2014 posts from smf_messages table.

If your db is large, and you have a crappy host, backup may timeout before it's complete without throwing error.  Empty your error log before you start.  You can also export just the one table.  Am assuming you have a default db setup, and table prefix is smf_.

[Sir Osis of Liver]

The thing is, deleting messages directly in the database like that leaves things misconnected and uncleaned-up...

Yes, but if they're all spam, shouldn't leave any loose ends.  OP has already deleted the members.  Can't think of any other way to do it.

[Arantor]

Of course it will leave loose ends. At a bare minimum, the topics and boards tables will have broken relations.

[Hj Ahmad Rasyid Hj Ismail]

Will assigning all that posts to one created member and deleting this member after that, be easier?

[Sir Osis of Liver]

You mean like this?

Code Select Expand



UPDATE smf_messages SET id_member=#### WHERE poster_time BETWEEN 1388534401 AND 1420070399



#### is new member id.  Then you could delete the member and remove all posts.

[Arantor]

It still wouldn't fix all the broken relations.

[Sir Osis of Liver]

Well, is there a way to do it?

[Arantor]

Yes, do ALL the interrelated tables manually. Or just... not do any of it manually in the first place and use things like quick moderation. SMF was never designed to do it in this level of bulk.

[Sir Osis of Liver]

I like ahrasis's suggestion.  If nothing legitimate happened during the specified date/time range, how much damage could it cause?  Would it make a difference if the spammers posted in existing topics or started new topics?

[nizarala]

Thanks to all of you!

Krash, the spam is only in 2014. Since I did not teach the course in 2014 there is no legitimate post in that period, so I can safely delete all posts made in 2014. I did not see any spam posts inside legitimate topics.

Ahrasis, it would be great if I was able to do this. How do I go about assigning all these posts to one member? I looked around the forum and it was not obvious to me.

[kat]

For future reference, should you need to remove spamtards, again...

When you remove a member, I believe you get the option to remove their posts, too.

I checked the "Members" section of the wiki, though, and couldn't find details. So, I could be wrong (I haven't needed to remove a member, on my fora, for yonks).

[Illori]

I checked the "Members" section of the wiki, though, and couldn't find details. So, I could be wrong (I haven't needed to remove a member, on my fora, for yonks).

it would be from their profile, but we are missing that on the wiki.

http://wiki.simplemachines.org/smf/Profile#Delete_This_Account should be the page it may appear on in the future. so you can not bulk remove members and their posts at the same time.

[kat]

I meant as he deleted each member, individually.

But, I see your point.

Ta!

[Hj Ahmad Rasyid Hj Ismail]

For future reference, should you need to remove spamtards, again...

As I recall while deleting member's post is default feature, there is a mod to "purge" member with post(s).

Link to Mod

...

Enhancements to purge inactive members

...

DESCRIPTION

Adds enhancements in "Administration Center > Forum Maintenance > Members > Remove Inactive Members":

• Options to remove members who have not activated their account after email change, have not approved their account, have not approved the deletion of their account or have not parental approval of their account.
• Option to remove members according to date registered.
• Maximum number of posts that members should have to be eliminated. Default value is 0.
• Delete posts of removed members. Possible values are: None, All posts, Topics and posts. Default value is None.
• Maximum number of processed members to delete members. This is in order not to exceed the system resources. Default value is 4,000.

Without this MOD the forum removes all inactive members, with or without posts, and does not delete posts of removed members (changes to guest authors). If their number is very high may fail for lack of resources and not removed anything.

If this is not so suitable for current situation, perhaps it might be useful in the future to delete spam members &/ posts in bulk.

[Sir Osis of Liver]

How do I go about assigning all these posts to one member?

Code Select Expand



UPDATE smf_messages SET id_member=#### WHERE poster_time BETWEEN 1388534401 AND 1420070399



#### is new member id.  Then you could delete the member and remove all posts.

You have to register a new member, then run the query with new member id replacing ####.

[Illori]

cant you use the admin option to reassign posts to the new user?

[Arantor]

That is also buggy

[Sir Osis of Liver]

Don't think so.  There's an option to attribute guest posts to a member, but don't see any way to attribute one member's posts to another.  Wouldn't be useful here anyway, as op has already deleted multiple members.  Also no way to specify a date/time range.

[Illori]

Don't think so.  There's an option to attribute guest posts to a member, but don't see any way to attribute one member's posts to another.

even though the name says it is for guests, it also works for members.

why do they need to define a range of time if the posts were made by the same person(s) ?

[Sir Osis of Liver]

If you're going to assign each spammer's posts to the same new member in order to delete them, you may as well just delete the spam accounts and remove their posts, it's less work.  In this case, the spam accounts have already been deleted, you'd have to pick through the entire forum looking for spam posts and reassign by username one at a time.

[Illori]

right you want to reassign the posts, but your query has a timestamp in it. why does the timestamp even matter? as long as the posts were not made in an account that got hacked they want to delete any/all of the posts no matter what time they were made.

[Kindred]

no, Krash -- reassigning posts is done in the maintenance utility...  not by post - so, you don't need to search for each individual post.

[Sir Osis of Liver]

right you want to reassign the posts, but your query has a timestamp in it. why does the timestamp even matter? as long as the posts were not made in an account that got hacked they want to delete any/all of the posts no matter what time they were made.

The original request was for a method to bulk delete all posts made in 2014, as the forum was inactive for the entire year, and all 2014 posts are spam.

no, Krash -- reassigning posts is done in the maintenance utility...  not by post - so, you don't need to search for each individual post.

I know, but you would have to search for each individual member to reassign.

[Kindred]

right....   delete all posts form 2014.

Unfortunately, deleting them directly in the database is problematic, because it leaves loose ends hanging all over the place.

bulk deleting threads is easy with checkbox moderation.

deleting posts out of threads is easy for one or two, but gets tiresome if there are bunches

[Sir Osis of Liver]

But if you run the query to reassign all 2014 posts to a new member, then delete the member and remove all posts, does that leave as many loose ends?  And would the loose ends matter if spam posts are all in spam topics, not in legitimate topics?

[Kindred]

I don't think it would leave "as many" loose ends... but would still leave - at least the topics table with dangling bits...

[nizarala]

Thanks again to all of you for the help.

I think I should clarify the situation. The spammers created hundreds of new members, and spread the comments across about half of them. So, there are   over a hundred spam members with only a few posts each. I saw the option to remove a member and all their topics, and I used it, but it quickly became clear that doing this for all the spammers was going to take a huge amount of time. Although I had stopped the spammers from  creating new members, they were still posting using the members they had already created, and the rate at which they posted was too high. This is why I had to take fast measures, and what I though to do was to order the members by the date they joined, and then bulk delete them by page, about 50 at a time. Now I don't have any new spam, but I am left with thousands of topics created by hundreds of "guest" members (Note: only members are allowed to post, but now that I deleted the spammers, their posts show as if made by a guest member).

I cannot use any of the purge options in the panel shown by ahrasis. I had seen those when I was searching for solutions, but he first option would delete all the legitimate members. The second option could have been useful before I deleted the spam members, but if I used it I would also be deleting legitimate members who never posted. The only safe unifying characteristic os both the topics and spam members is that they were all created in 2014 (actually the attack was in December). There are no legitimate members or posts in 2014.

I'm inclined to run Krash's query. Is it safe after all? I don't want to break the forum, because I know I don't have much skill to fix it later. Thanks!!!

[Gwenwyfar]

You could change the page display limit to a very high number, then just go around through select all to delete them. Maybe even search (you can change the limit of that too), so you could just search for 2014 posts and go on delete all.

[nizarala]

Fortytwo, this completely solved my problem! Thank you, thank you!!!

So, I went to Admin>Manage Posts and Topics>Topic Settings and increased the "Number of topics per page in the message index to 300. This left me with just a few pages per board. I deleted the bulk, and the remaining few I just deleted manually.

Thanks to all of you. I am glad this could be fixed without the need to run any query. I was a bit afraid of breaking something and not being capable of fixing it.

[Kindred]

good call, fortytwo!

[Hj Ahmad Rasyid Hj Ismail]

Smart solution. +1 Fortytwo.

[Steve]

[kat]

+1 Fortytwo.

43.

[muratbora]

I noticed since 25 December untill today only spam topics was opened and 400 pages...I need to delete them as bulk,
I want to use PHPMYADMIN for this;  DELETE FROM smf_messages WHERE poster_time BETWEEN 1388534401 AND 1420070399

But I did not understand how to enter BETWEEN ..... AND ......

can you help me please ?

[Kindred]

You should not delete messages directly in phpmyadmin - you will screw things up if you do..


The instructions are not to delete the messages directly in the database, but rather to reassign those messages to a single user and then use the INTERNAL FORUM FUNCTION to delete the user and all messages.