June 18, 2015

Deleting Blog Posts from VBulletin 4 Database

VBulletin 4 marked the launched of the Blog feature, which has been significantly improved in VBulletin 5. The Blog feature of VBulletin 4 does not support extensive features in the admin control panel, and to a surprise to many, it does not sport a mass blog deleting option. When you have over 1,000 spam blog entries in our database, it becomes next to impossible to remove them from the admin control panel. That said all your data is ultimately available on the MySQL tables that the VBulletin framework deploys. Hence, you can start deleting blog entries right from the database.


How to Delete Blog Posts

It is likely that out of the many spam blog posts that your VBulletin website has, there will be some good posts in there as well that you want to keep. Hence, we will take a look into the database using the phpMyAdmin. Once we’re done verifying different data tables, we will be able to implement queries for deleting spam blog entries. Here is an example of queries that you can use: 1. — deleting blog entries in VBulletin 4 Blog 2. delete from blog where blogid > 18; 3. delete from blog_text where blogid > 18; 4. delete from blog_subscribeentry where blogid > 18; 5. delete from blog_user where bloguserid not in (6,14); 6. delete from blog_userstats where userid > 1000; Let’s overview why you should remove the entries from the table: BLOG: The primary table containing all the basic blog details with each row corresponding to a blog post. Your queries can be based on the username, blogid, and other relevant factors. BLOG_TEXT: The table containing blog data where spam entries can eat up most of your free space. Hence, you should remove the spam entries from this table as well, using the same terms you used to delete entries from the BLOG table. BLOG_SUBSCRIBEENTRY: The table containing subscriber’s entries on each blog post. Deleting such entries from your database also frees up significant space. BLOG_USER, BLOG_USERSTATS: The tables containing users’ information that uses the blog feature. If you intend to delete specific users from your database then you should delete entries from this table as well.

The Outcome

To delete mass blog posts manually that are exceeding 1000 entries, it can take you a day or two. However, through the phpMyAdmin framework, you can easily remove batch blog entries within a couple of hours and save loads of database space at the same time. This may also improve the functioning of Webmasters Forum. As a note of advice, follow these guidelines to make sure you remove spam entries from your database safely and efficiently: 16. Backup all the tables you will be writing queries in before removing data.
17. Run the Update Counters as soon as you delete your entries.
18. Block and delete all spam users from your database.
19. For those running VBulletin 4 on dedicated servers, run the commands using terminal. Chances are that the phpMyAdmin may lag if you are entering queries that can take over 15 minutes.
20. Once all the deletions have been made, run the Flush Tables command after which, you should run the Update Counters.