Register Members List Search Today's Posts Mark Forums Read

Reply
 
Thread Tools
  #1  
Old 09 Jun 2015, 14:34
postcd postcd is offline
 
Join Date: Feb 2012
Post vbulletin delete all posts containing phrasse

Hello,
as an vbulletin 4.2.x admin, how can i easilly delete all forum posts which contains certain phrasse like for example "API Payment." on entire forum, all users. When i search for that phrasse it shows threads, but i want to be able to somehow in bulk delete posts containing that phrasse. Thank you
Reply With Quote
  #2  
Old 09 Jun 2015, 16:00
kh99 kh99 is offline
 
Join Date: Aug 2009
Real name: Kevin
If you can find the posts you want via a search then you can delete them from the search results page. Use "Search Single Content Type" and select "Show Results as posts".

But I guess you can't search for phrases or words 3 letters or less. In that case I'm not sure if there's any way to do it in vbulletin, but you could use a query to change the database directly (which is dangerous - you at least want a backup before doing it). Maybe a good way to do it would be to insert rows in the moderation table, then use the existing post moderation to delete them. If you're interested I can post a query to do it.
Reply With Quote
  #3  
Old 10 Jun 2015, 09:19
postcd postcd is offline
 
Join Date: Feb 2012
Thanks alot kh99,
The vBulletin Search you recommended worked but search engine is very limitted, it do not index some words, dont allow too short ones.. If you could share the mysql quesry You talk about it would be very beneficial!

In case of vB search, one can see 25 posts per page and can tick them all and bulk delete them.

If one want to see like 200 posts per page, try to go to: AdminCP -> Settings -> Options -> Message Searching Options and modiffy "Maximum Search Results to Return" and "Search Results Posts Per Page" options

Mysql query You mentioned would allow faster SPAM removal in case one want to search numerous SPAM phrasses and want to delete more than 200 posts at a time (it appears search dont want to return more). If it is simple for you, it would be nice to know that mysql query which would move posts matching phrasse into moderation queue.

All moderated threads can be then deleted in bulk via: AdminCP / Threads & Posts / Prune , there is option to delete Moderated threads.

Last edited by postcd; 10 Jun 2015 at 13:54.
Reply With Quote
  #4  
Old 10 Jun 2015, 21:52
kh99 kh99 is offline
 
Join Date: Aug 2009
Real name: Kevin
Originally Posted by postcd View Post
Mysql query You mentioned would allow faster SPAM removal in case one want to search numerous SPAM phrasses and want to delete more than 200 posts at a time (it appears search dont want to return more). If it is simple for you, it would be nice to know that mysql query which would move posts matching phrasse into moderation queue.

All moderated threads can be then deleted in bulk via: AdminCP / Threads & Posts / Prune , there is option to delete Moderated threads.
I tried something like this:

*removed*

And it seemed to work, but I only did one small test, so use at your own risk (and always have a recent backup).

Edit: Oh, actually I just realized that if a post is the first in a thread, then the type needs to be 'thread' instead of 'reply', and that will take a more complicated query. I'm not sure what will happen if the type in the moderaton table is 'reply' but it's the first post.

Last edited by kh99; 10 Jun 2015 at 22:26. Reason: removed incorrect query
Reply With Quote
  #5  
Old 11 Feb 2016, 13:22
postcd postcd is offline
 
Join Date: Feb 2012
It seems that my search index do not index all posts or there is some search results cache that not got prunned, because there are still plenty of posts vbulleltin search cant find so i cant delete post based on the phrasse (example phrasse: "Memo: API"). Any solution please?

PS: my "Search Index Minimum Word Length" is 4 isnt that case?, but i dont want to have huge index

Last edited by postcd; 11 Feb 2016 at 13:38.
Reply With Quote
  #6  
Old 13 Nov 2017, 17:48
postcd postcd is offline
 
Join Date: Feb 2012
Question

So there is no other way to delete more than 200 posts in one go?
I need to delete 90724 posts having phrase news.google.com in them (they are leading thread posts, and some of these threads have replies)
Now the posts are made by one user but he no longer exist as he has a Guest status and message "Invalid User specified." appears.

I have another theory on how to solve this:
create new user, copy its userid and then run mysql query which will find all posts that i want to delete (contains certain phrasse) and then modify these mysql entries userid's to match new user userid, and then delete this user via admincp.
In MySQL db i see:

SELECT * FROM `post` WHERE `pagetext` LIKE '%news.google.com%'
i found 2 users which have matching posts:

username: username1, username2
userid: 479, 0
pagetext: %news.google.com%

username2 had userid 308

SELECT * FROM `thread` WHERE `postusername` LIKE 'username1' AND `postuserid` LIKE '308' AND `replycount` LIKE '0' AND `notes` LIKE 'Imported thread'
This found posts i want to delete.

UPDATE `thread` SET `postuserid` = '308' WHERE `postusername` LIKE 'username2' AND `postuserid` LIKE '308' AND `replycount` LIKE '0' AND `notes` LIKE 'Imported thread'
is wrong

Please any idea on right mysql query?

I found:
UPDATE table1 dest, (SELECT * FROM table2 where id=x) src
SET dest.col1 = src.col1 where dest.id=x ;
but does not work

Last edited by postcd; 14 Nov 2017 at 14:18.
Reply With Quote
  #7  
Old 14 Nov 2017, 09:52
Seven Skins's Avatar
Seven Skins Seven Skins is offline
 
Join Date: Sep 2008
Try searching for userid = 0

e.g SELECT * FROM post WHERE userid='0'
Reply With Quote
  #8  
Old 14 Nov 2017, 13:26
postcd postcd is offline
 
Join Date: Feb 2012
Yes, i find 182240 entries, but many are valid and i do not want to delete these, i can return only those i want to delete, but still, this may cause damage to the vbulletin db when i delete them? Instead in my previous post i mentioned theory to change just userid of these posts to a new user and then delete this user via admincp, but i do not know right mysql query. I assume query would have to be run both for post and thread table and maybe also other table/s in order these posts really change its owner.
Reply With Quote
  #9  
Old 15 Nov 2017, 03:57
CAG CheechDogg's Avatar
CAG CheechDogg CAG CheechDogg is offline
 
Join Date: Feb 2012
Try this ....

Add a new plugin ...

Product = vBulletin

Hook Location = postbit_display_complete

Title = choose something you can remember

Execution Order = 5

Plugin PHP Code


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.


This will change news.google.com to what ever you want it changed to instead of deleting thousands of posts and potentially messing something up ...


You can always disable this plugin and everything will go back to it's original state ....
Reply With Quote
Reply

Similar Threads
Thread Thread Starter Forum Replies Last Post
Profile Enhancements vBulletin Self Delete - Allow users to self-delete their profile Vossi vBulletin 4.x Add-ons 58 03 Jul 2017 14:39



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


New To Site? Need Help?

All times are GMT. The time now is 23:31.

Layout Options | Width: Wide Color: