This post is in continuation of Deleting Blog Posts from Database. If your VBulletin forum is massively spammed with some users having thousands of posts, this will help you.
VBulletin admin panel provides a way to delete posts from a user but it’s not too effective when the number of posts is huge like 10K+ posts. I have analyzed the VB database and come up with some queries to delete posts from the database side.
Run below query to find out top spammers:
Copyselect userid, count(*) as num from post where userid not in (10,20) group by userid order by num desc limit 20; select postusername, count(*) as num from thread where postusername not in ('Pankaj') group by postusername order by num desc limit 20;
Note that I have left trusted users so that they won’t be part of the result.
Run below query to find out the user name:
Copyselect username from user where userid=48942;
Check the user posts on the website to make sure that it’s a spammer. Once confirmed run following queries to delete all the user posts.
Copydelete from subscribethread where userid=11429; delete from post where userid=11429; delete from thread where postusername = 'SPAMMER';
- Make sure to use DB queries only in case of huge spam posts.
- Update counters once you have deleted all the spam posts.
- Always keep Tables backup before running these queries.
Note that I have used these queries and till now not found any adverse effect. However, chances are that I might have left some data in other tables. If you know about them, please let everybody know here by posting a comment.