Check out my new site: FreeDaBeat.com!

PHPBB SQL Query to Remove Orphaned Posts/Topics (users)

Anything you like.

PHPBB SQL Query to Remove Orphaned Posts/Topics (users)

Postby gaiaadmin » Wed Nov 16, 2011 5:38 am

If any of you have ever tried to prune a large number of users AND their posts from your PHPBB forum, you've noticed that this doesn't work very well. If your query takes too long, or times out, which it will, you're going to be left with a load of posts and topics that are by users that no longer exist. AND you will have no way to remove these posts.

You will also end up with a $@!#load of posts that show in the listing but do not actually exist, again with no way to delete them. This happened to me. I had over 20,000 spam posts from 2000 spam users. PHPBB's prune function was so effective that it only left me with about 40,000 records to clean up once I was done! AND a massively broken forum.

So, I wrote these extremely dangerous, unforgiving mySQL querys to clean up all posts, topics, words and other records left over with no user associated with them. Check it out, but use at your own risk. This deletes data directly from your database. It will delete all posts that do not have an existing user.

-- Delete topcis and posts that have no user associated with them.
-- Change "select *" to "delete" to actually delete records.

-- Query 1
select * from phpbb_search_wordmatch where post_id in
(SELECT post_id
FROM `phpbb_posts`
WHERE topic_id
IN (
SELECT topic_id
FROM phpbb_topics
WHERE `topic_poster` NOT
IN (
SELECT user_id
FROM phpbb_users
)
)
)

-- Query 2
SELECT *
FROM `phpbb_topics`
WHERE `topic_poster` NOT
IN (
SELECT user_id
FROM phpbb_users
)

-- Query 3
SELECT *
FROM `phpbb_posts`
WHERE topic_id
IN (
SELECT topic_id
FROM phpbb_topics
WHERE `topic_poster` NOT
IN (
SELECT user_id
FROM phpbb_users
)
)
gaiaadmin
Site Admin
 
Posts: 25
Joined: Mon Dec 13, 2010 8:02 pm

Return to Off Topic Discussion

Who is online

Users browsing this forum: No registered users and 7 guests

cron