News:

SMF 2.1.4 has been released! Take it for a spin! Read more.

Main Menu

Merging forums

Started by waremock, August 13, 2008, 05:36:01 AM

Previous topic - Next topic

waremock

I have multiple forum that share the same user table. I would like to merge the forums back to one forum.

What would be the best way to do this?

lordtron

I don't think there is an easy way of doing this.

Cause you have Boards, Topics, Messages, Posts, Stats, you name it.
If you try to merge it then one will overtake the other.

VainSoftGames.com - New Design To Gaming

waremock

All I really want to bring over from the other boards are the posts.

ReVoKe

i don't think its possible with out one taking over the other

waremock

Quote from: ReVoKe on August 13, 2008, 10:09:46 PM
i don't think its possible with out one taking over the other

Ok it's possible, I just had to do it all with some sql queries.

I had to change some idexes around to make it work.


Would have been easier if there were relationships setup in the database. I'm not sure how to do that in mysql.

greyknight17

I'm sure it's possible, but it won't be an easy task. You will need to change the auto increment numbers for all your posts and other things accordingly so there are no conflicts.

waremock

That's what I had to do, I also had to do a temp table for the topics which contained the topic id and the new auto increment field. Once I had that I ran a query to change the topic id to the new auto increment and then change it in the messages table using the new topic id.


I also had to purge a few of the log tables, once I did this I recounted the statistics and ran the find errors query.


This worked great and I was able to merge the boards.

If anyone would like to write some type of merge program I could give you the queries I used to do the merge.


I did have a few of the post end up with the wrong topics but I was able to fix this by splitting the post.


It was a lot of work but I did not loose any of the topics.

greyknight17

Yep, it's definitely not a simple task to do. We have had some users who were trying to come up with a way to do this (be it a mod or some post made in the forums here). I haven't heard any updates on them yet...As daunting as it is, it's definitely do-able as you have witnessed :)

Glad you were able to get it working in the end.

Topic marked as solved.

thefley

Quote from: waremock on August 31, 2008, 05:25:41 PM
That's what I had to do, I also had to do a temp table for the topics which contained the topic id and the new auto increment field. Once I had that I ran a query to change the topic id to the new auto increment and then change it in the messages table using the new topic id.


I also had to purge a few of the log tables, once I did this I recounted the statistics and ran the find errors query.


This worked great and I was able to merge the boards.

If anyone would like to write some type of merge program I could give you the queries I used to do the merge.


I did have a few of the post end up with the wrong topics but I was able to fix this by splitting the post.


It was a lot of work but I did not loose any of the topics.

this is what I need someone to do for me!

www.greatplainsriders.com

waremock

How many forums do you want to merge?

K3TK3TK3T

i need this also, 2 forums

waremock

I can show you how in a few days, all the information is in my work computer.

How many posts, topics and boards do you have in each forum?

K3TK3TK3T

nevermind about it, thanks though! :}

Antechinus

#13
Quote from: waremock on November 15, 2008, 02:28:11 PM
I can show you how in a few days, all the information is in my work computer.

How many posts, topics and boards do you have in each forum?
I also would like to do this. I'm capable of doing the work myself as long as I have the instructions. I can experiment with the backups on my local host so if I get it wrong the first time there's no drama.

Current stats are:

1st forum: 13,202 Posts in 322 Topics on 24 Boards.
2nd forum: 86,298 Posts in 3,224 Topics on 24 boards.

One catch is that some of the topics and posts are the same on both forums. There's a story behind this one ;D.
Just to complicate matters further, what I would really like to do if it is feasible is to extract only some of the content from the second forum and merge it with the first forum. If this is over the top loony I'll happily go with a straight merger, but I really would prefer the ability to select which content gets merged.

waremock

I've been looking for one of the queries I used and unfortunately I'm unable to find it. But basically this is what I did. Hopefully you have access to your data. Also make sure that both forums are running the same version of SMF.

First, Backup, Backup, Backup

Second I emptied smf_log_topics table on both forums.

Fortunately for me. I was sharing the same user table for all my forums. So I did not have to mess around with the members. But If you have duplicate members in both forums you will have to do a little more work at the end.

On forum #1 you will have to reindex your messages table.

I ran the following query to do this.



set @counter = 0;
UPDATE `smf_messages` SET `id_msg` = (@counter := @counter + 1)


After this is done look at the last record in the table and write down that number.


Then run the same query on the other database for forum #2 but make sure on set @counter = 0 you change the "0" to the number you just wrote down.

This will re-index the messages table on forum #2 to start where the messages table from forum #1 left off.

Next we are going to do the same thing with smf_topics but first we need to create a temporary table.

This temp table, which you will name smf_topics_temp, will be a copy of smf_topics. You will need to add one extra field named id_topic_new to this temporary table.

Once you add this new field you will need to run this query

set @counter = 0;
UPDATE `smf_topics_temp` SET `id_topic_new` = (@counter := @counter + 1)


Next run this query on smf_topics

set @counter = 0;
UPDATE `smf_topics` SET `id_topic` = (@counter := @counter + 1)


Next look at the last index number of smf_topics and write this number down because you will need it when you do the same thing on the other forum.




I will stop here to ask you if you want to continue because it is going to get a little complicated and confusing from here on. Also I need to redo the query I used on this next step, unless I find it.

But basically we are going to use the temp table you created to change the topics Id in the messages table to match the new topic ID we just created.



Antechinus

Oh I'm up for it alright. Like I said I can mess it up as often as I like on my local host before going for the real thing.

And yes, there are duplicate members. Basically the 2nd forum is a restored backup which was set up as a temporary archive after a lot of content was mistakenly deleted from the first forum. So memberlists and a lot of other things are all duplicated.

Deprecated

I'm sure it could be done, but I doubt anybody would volunteer to do the work for free, and few would do it for hobbyist wages. Also, it would require both forums to be shut down during the merge, due to the difficulties of hitting a moving target. Or you would just have to accept that any posts/PMs would be lost after the pre-merge backups were made and the combined forum returned to online.

Antechinus

I'm not asking anyone to do it for free. I'm saying I'll do it myself as long as I can get the information I need. Shutting down the forums while it's in progress is no problem either. That would be a given anyway.

Advertisement: