UBB.Developers Forums UBB.threads 7 Development How Do I? How do I archive old posts?

How do I archive old posts so I can save them off and maybe even have some kind of searchable table or something.

Make a new table, say w3t_PostsOld, with exactly same definition as w3t_Posts.
Do queries:
INSERT INTO w3t_PostsOld SELECT * from w3t_Posts where B_Number<N;
DELETE FROM w3t_Posts where B_Number<N;
You may run this from cron to assure regular archiving (remember to optimize w3t_Posts after deleting old posts).
You can use B_Posted instead of B_Number to move posts to archive based on date they were posted.
Ie. to archive posts older that 30 days you should do following query:
INSERT INTO w3t_PostsOld SELECT * from w3t_Posts where (UNIX_TIMESTAMP()-B_Posted)>2592000;
DELETE FROM w3t_Posts where (UNIX_TIMESTAMP()-B_Posted)>2592000;

Then you must create some addititional files (by making changed copies of original files) to do searching in w3t_PostsOld (archiveshowflat.php, archivedosearch.php, archivesearch.php, archiveshowthreaded.php) and additional option in menu: "Search archive".
You can also create archiveaddpost.php and archivenewreply.php which will allow your users to reply to posts in archive and post these replies into w3t_Posts.
I can post here my archive* files, but they have searching changed to use fulltext indexes from mysql 4.0.1 (because in other way searching is toooooo ssssssllllllooooowwwww with large number of posts /I have about 500k/).
But if anyone interested let me know.

btw. I tried to do that with MERGE table type.
I created w3t_PostsOld the way I wrote earlier, then w3t_PostsAll of type MERGE (merges w3t_Posts and w3t_PostsOld).
The bonus is you do not have to create additional files, just change all references for w3t_Posts to w3t_PostsAll in dosearch.php.
The minus is that searching is much slower than in both tables separately.
But I didn't try with full text index, so maybe...

Actually, that gives me some ideas for future site improvements. I don't really have the trouble right now, but I imagine I will eventually.