UBB.Developers Forums Community Development Programming MySQL and Server Configuration Need easy syntax

I want to delete every post/thread from my test forum, how can I do that all at once with one command?

Not sure if it can be done with one command.

You will have to know the Keyword of the board you wish to "flush". If you don't know it, then use the:

SELECT Bo_Title, Bo_Keyword
FROM w3t_Boards

That will give you the list of all your boards and you can write down the keyword of the board you wish to flush. If we assume that the keyword is "TestBoard" then to empty all the posts you can use

DELETE
FROM w3t_Posts
WHERE B_Board = "TestBoard"

Finally to reset the counters for your board you need to issue the:

UPDATE w3t_Boards
SET Bo_Total = 0, Bo_Last = 0, Bo_Threads = 0, Bo_LastNumber = 0, Bo_lastMain = 0
WHERE Bo_Keyword = "TestBoard"

I hope this helps.

Sweet, worked perfect!

Nice to hear that.

OK, here's another one, how about if I wanted to delete all post/threads in a certain forum that were older than 30 days?

It is the same query with an additional where clause.

WHERE B_Board = "TestBoard" AND Bo_Posted < UNIX_TIMESTAMP("2003-01-01 00:00:00")

The above will delete all posts from the TestBoard prior to the 1/1/03 at 00:00:00

Careful. What about posts with replies?

Woops, I got this when trying it on my "partsforsale" forum:

Here are the results of your command: "WHERE B_Board = "partsforsale" AND Bo_Posted < UNIX_TIMESTAMP("2003-01-01 00:00:00")".
SQL ERROR: Unable to do_query: WHERE B_Board = "partsforsale" AND Bo_Posted < UNIX_TIMESTAMP("2003-01-01 00:00:00")
You have an error in your SQL syntax near 'WHERE B_Board = "partsforsale" AND Bo_Posted < UNIX_TIMESTAMP("2003-01-01 00:00:' at line 1 row(s) affected by your query.

Dave is right. I forgot the posts with replies. In effect you can delete posts that have been posted before the time you have specified but are "alive" since someone is posting to them. If we run this query here, then the IIP thread (and a lot more) would be rendered useless.

Luckily you only pasted the WHERE clause and not the DELETE.

Let me think a bit about this

I thought about this a bit more. There is no easy way to get what you want (for the posts before a certain date). The reason being is (like Dave said) that you endanger yourself of breaking the threads by deleting a post that might have been replied to.

You need to write some sort of script that will retrieve every post before the certain date and then subselect to see if there are any replies to it, then check the date of those replies and delete appropriately.

Sorry but this cannot be done with a simple DELETE statement.

Maybe coloradok5 didn't express clearly what he really wanted to do. The built-in thread expire feature may do what he wants.

Yeah, and if you don't want to fool with the whole cron thing.. you can set the forum to expire after x days.... and run doexpire.php from your browser every once in a while.