how to prune thread without posts?

[cache]

Since I have deleted some posts, some threads no longer have any posts in them. For the admincp/Threads&Post/Prune, you can specify
at Least X Replies
at Most X Replies
at Least X Views
at Most X Views

but I don't see any options for prunning thread at most X Posts. So how do I delete any threads that have no post in them?
thanks.

[Marco van Herwaarden]

Replies = Posts.

[cache]

Thanks for your reply.

So does the "replies" include the original post or simply subsequence reply post?

Anyway, when I changed the "at most replies" to be 1, I deleted some thread, however still I have a lot of thread without any post. I regenerate the counters a few times, I could not delete all threads without single post.

A separate question:
I also delete all users without any posts, if I can delete any thread without any user, it can also help me.

[Dismounted]

Originally Posted by cache

So does the "replies" include the original post or simply subsequence reply post?

Replies do not include the first post, as that is not a "reply" (what's it replying to?).

Originally Posted by cache

I also delete all users without any posts, if I can delete any thread without any user, it can also help me.

Huh?

[Marco van Herwaarden]

How did this start in the first place. vBulletin by default never leaves a thread wtihout posts.

[cache]

This happened when backing up with the phymyadmin (I know we should have used the shell to backup). Since the post table is larger, some of the posts were timed out. Thus when the database is restored the largest table is incomplete, thus we end up with many threads with no posts.

[Marco van Herwaarden]

Then the solution should be to create a better backup. Can't you create a new backup?

[cache]

I thought I had a weekly automatic shell backup, however it backed the wrong database_name and overwrote the good data. The only good database was from a phpmyadmin experiment earlier, however many posts were not backed up in that phpmyadmin backup. I am willing to accept the fact that some posts were lost. I would like to just prune the empty thread.

any solutions?

[Marco van Herwaarden]

Not fully tested, so create a backup first:

[sql]delete thread
FROM `thread`
WHERE (SELECT COUNT(*) as postcount FROM post WHERE thread.threadid = post.threadid) = 0[/sql]

[cache]

Thanks for the code. First I need to access the query, When I clicked
Maintenance > Execute SQL Query
I got error message saying I don't have database access permission. Why can't I access the SQL Query? (At the admin panel /admincp I can see all the access listed, only when click SQL Query do I get the error message)