vbulletin delete all posts containing phrasse

[postcd]

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

[kh99]

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.

[postcd]

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.

[kh99]

Originally Posted by postcd

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*

[S]And it seemed to work, but I only did one small test, so use at your own risk [/S](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.

[postcd]

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

[postcd]

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

[Seven Skins]

Try searching for userid = 0

e.g SELECT * FROM post WHERE userid='0'

[postcd]

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.

[CAG CheechDogg]

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

$word = array(  
'/news.google.com(?= )/',  
'/news.google.com(?= )/',
'/news.google.com(?= )/',  
);  

$changedword = 'what-ever-you-want-it-changed-to';  

$post['message'] = preg_replace($word, $changedword, $post['message']);

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 ....