News:

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

Main Menu

[TIP] How to Merge one SMF Forum with Another using MySQL

Started by jkdove, August 25, 2009, 03:28:17 PM

Previous topic - Next topic

jkdove

I was searching long and hard, through rain and snow, demons and hell fire, uphill, backwards, for a way to accomplish a way to take one forum and merge it with another forum.   And by that, I don't mean a board, I mean an entire forum database with boards, posts, users, etc...

The following guide was compiled using bits here and there from here and there.  It was written it its final form by Beau Simenson, whom I wish to thank massively for making this possible.   It is fairly straight forward in how it works, doesn't require a master level of technical proficiency.  I assure you that while I am by no means a complete beginner in the ways of administrating SMF forums,  I am VERY new to using these tools and know next to nothing of the MySQL language.   What I'm getting at it is that if I can do it, you can do it. 

For the example below, you'll need to understand the terms Old and Primary.   Old is the OLD database you want to export FROM.   Primary is your Current Database you want to import TO.

I can assure you this works perfectly if you follow each step by reading it carefully and doing precisely what it says.  I used this method three times and it has worked perfectly each time.   

Good Luck, Better Skill and Enjoy your newly merged forums!







One of the tabs on the top of PhpMyAdmin is "SQL" ... use that for Section I and Section II!


Section I

I did the following SQL queries on the Primary database to find out what the max ID was plus some fudge factor so that we could make sure that this will work even if 5 more users have been added or 15 more posts have been made. (MAX(ID_MSG) +100 means this should work even up to 100 or so new messages) If too much time passes, you'll want to run these queries again otherwise you might start running into collisions.

Since you are actually looking for the results here, you'd have to make each of these requests one at a time.

/* Will result in: 91 */
SELECT MAX(ID_BOARD) +10 AS max_id_board FROM smf_boards;

/* Will result in: 33 */
SELECT MAX(ID_CAT) +20 AS max_id_cat FROM smf_categories;

/* Will result in:  377 */
SELECT MAX(ID_MEMBER) +50 AS max_id_member FROM smf_members;

/* Will result in:  37340 */
SELECT MAX(ID_MSG) +100 AS max_id_msg FROM smf_messages;

/* Will result in:  4236 */
SELECT MAX(ID_TOPIC) +30 AS max_id_msg FROM smf_topics;


Section II

The part you are going to have to do is the following. You can paste this entire block of SQL into the textbox for the SQL tool in phpmyadmin for the Old database. These commands are pretty basic. Basically, the first command ( UPDATE smf_boards SET ID_BOARD = ID_BOARD+91; ) is saying "go into the smf_boards table and whatever the ID is, add 91 to it." The reason we are adding 91 to it is because 91 is 10 more than whatever the Primary database has. This means that if you had a board ID of 23 in the Old database, that board is now going to have an ID of 114 (23 + 91). Later on, you'll see there is an update to set the ID_BOARD for smf_topics having the same thing done to it. This is because whatever topic used to belong to board ID 23 now needs to belong to board ID 114. 

Run the following in the SQL window.  Hopefully you won't get any errors:

/* Foreign key checks need to be off! */
SET FOREIGN_KEY_CHECKS=0;
UPDATE smf_boards SET ID_BOARD = ID_BOARD+91;
UPDATE smf_boards SET ID_LAST_MSG = ID_LAST_MSG+37340;
UPDATE smf_boards SET ID_MSG_UPDATED = ID_MSG_UPDATED+37340;
UPDATE smf_boards SET ID_CAT = ID_CAT+33;
UPDATE smf_members SET ID_MEMBER = ID_MEMBER+377;
UPDATE smf_members SET ID_MSG_LAST_VISIT = ID_MSG_LAST_VISIT+37340;
UPDATE smf_topics SET ID_TOPIC = ID_TOPIC+4236;
UPDATE smf_topics SET ID_BOARD = ID_BOARD+91;
UPDATE smf_topics SET ID_MEMBER_STARTED = ID_MEMBER_STARTED+377;
UPDATE smf_topics SET ID_MEMBER_UPDATED = ID_MEMBER_UPDATED+377;
UPDATE smf_topics SET ID_FIRST_MSG = ID_FIRST_MSG+37340;
UPDATE smf_topics SET ID_LAST_MSG = ID_LAST_MSG+37340;
UPDATE smf_messages SET ID_MSG = ID_MSG+37340;
UPDATE smf_messages SET ID_TOPIC = ID_TOPIC+4236;
UPDATE smf_messages SET ID_BOARD = ID_BOARD+91;
UPDATE smf_messages SET ID_MEMBER = ID_MEMBER+377;
UPDATE smf_messages SET ID_MSG_MODIFIED = ID_MSG_MODIFIED+37340;
UPDATE smf_categories SET ID_CAT = ID_CAT+33;




Section III

First, check your work! Log into the OLD database forums:

It is important that you log into the forums at the root! You've changed your ID's around so two things will happen... 1) you will likely need to log in again (your member ID changed, so your session will have been lost) and 2) If your old URL was say htttp://www.yourwesbsite.com/index.php?board=23.0 that URL will probably break because that board will now be found at htttp://www.yourwesbsite.com/index.php?board=114.0

Section IV

So, if it is all good... you can export! So, click on the "Export" tab for the Old database. Click "Unselect all" and then choose by hand:

smf_boards
smf_categories
smf_members
smf_messages
smf_topics

Then, there are a bunch of checkboxes that have some defaults set. Be sure to manually change the following

Disable foreign key checks: CHECKED
This will make sure that you can insert the rows without any integrity violations

Structure: NOT CHECKED
This will effectively make your export able to be imported cleanly into an existing database (means it won't try to include any table creation stuff)

I'd suggest using either zip or tar.gz compression.


Section V

You should be able to use this export to import into your Primary database through MySQL's IMPORT function.

There are no more steps. You are good to go. :)

charpress

In searching for a way to merge forums, I was kind of surprised that SMF doesn't have a merge function. That is sort of basic.

Antechinus

If it's that basic, write it for us. Seriously. Go for it. :)

SAFAD

Maybe Little Soft Won't Get Users Get Mad From Your Harche antenchinus
SO
I Bet That You Don't Even Know Coding charpress
this isn't basic
this is advanced
it might be a little requird but not that much
Best Regards
Sadaoui "SAFAD" Abderrahim - Lead Developer @ Electron Inc.

Antechinus

Oh I know how advanced it is, but I don't know anything about Charpress's coding abilities. If he can do it then there's no reason why he shouldn't. It may be basic for him.

Arantor

It isn't basic at all. It's something I'm actively frightened at the prospect of ever having to attempt.

Antechinus

I'd love to have a go at it actually but it would be one hell of a task. I mean in theory the procedures required are quite simple. It is the sheer number of them and the way they interconnect that presents problems. I've merged databases manually and would love to have an automated way of doing it because the manual version is extremely time-consuming.

Windy

I figure the main issue would be dealing with the conflicts and other information that would be non-standard to a default installation of smf (how would you deal with themes/packages, members that share the same name etc.), the rest of it would just be time consuming.
All unsolicited PM's will be ignored.  Any support requests should go in their topics.

My Mods


Image Zoom Tag
Image Quote Removal
Color Picker
Additional Polls
Simple Awards System

Mods are only updated to the latest same major version of smf on request.

Antechinus


SAFAD

this tip doesn't work :(
when you apply this
the old forum looses all categories !!
Best Regards
Sadaoui "SAFAD" Abderrahim - Lead Developer @ Electron Inc.

jkdove

Quote from: SAFAD on July 12, 2010, 10:39:31 AM
this tip doesn't work :(
when you apply this
the old forum looses all categories !!

I doubt this person will respond as I do realize the date of the post. 

I'm simply saying that if you follow the instructions, STEP BY STEP, it works exactly as I described.   Best I can think is that he missed a step somewhere. 

hostingfaq

I just imported 2 smf dbs into a third one and it worked great !!

Thanks a lot! Just made my project possible so I really appreciate every letter and second you spent writting this up.

Cheers!

hostingfaq

Quote from: hostingfaq on June 16, 2011, 08:11:45 PM
I just imported 2 smf dbs into a third one: hxxp:www.hostingfaq.es [nonactive] , and it worked great !!

Thanks a lot! Just made my project possible so I really appreciate every letter and second you spent writting this up.

Cheers!

RasmusDJ

Quote from: hostingfaq on June 16, 2011, 08:11:45 PM
I just imported 2 smf dbs into a third one and it worked great !!

Thanks a lot! Just made my project possible so I really appreciate every letter and second you spent writting this up.

Cheers!

Did you just follow it step by step in post #1?

and one what version of SMf did you do it?

LiveWire

I hope they work as I write quick tools based on this technique. If it works out, maybe someone else can run with it and make it fancy like take parameters, etc.

So far: boardcount.php

<?php
//written by Steve Bishop, based on documentation by Beau Simenson and php.net [nofollow] sample code
//Don't judge, it was mainly for a one time use by me

$link = mysql_connect('server, probably localhost for you', 'username', 'password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
$db_selected = mysql_select_db('database name', $link);
if (!$db_selected) {
    die (mysql_error());
}

echo 'Connected to database<BR>';
$query = "SELECT MAX(ID_BOARD) +10 AS max_id_board FROM smf_boards";
$result = mysql_query($query);

// Check result
if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    die($message);
}

while ($row = mysql_fetch_assoc($result)) {
    echo '<BR>max_id_board: ' . $row['max_id_board'];
}

// Free the resources associated with the result set
mysql_free_result($result);


$query = "SELECT MAX(ID_CAT) +20 AS max_id_cat FROM smf_categories";
$result = mysql_query($query);

// Check result
if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    die($message);
}

while ($row = mysql_fetch_assoc($result)) {
    echo '<BR>max_id_cat: ' . $row['max_id_cat'];
}

// Free the resources associated with the result set
mysql_free_result($result);


$query = "SELECT MAX(ID_MEMBER) +50 AS max_id_member FROM smf_members";
$result = mysql_query($query);

// Check result
if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    die($message);
}

while ($row = mysql_fetch_assoc($result)) {
    echo '<BR>max_id_member: ' . $row['max_id_member'];
}

// Free the resources associated with the result set
mysql_free_result($result);


$query = "SELECT MAX(ID_MSG) +100 AS max_id_msg FROM smf_messages";
$result = mysql_query($query);

// Check result
if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    die($message);
}

while ($row = mysql_fetch_assoc($result)) {
    echo '<BR>max_id_msg: ' . $row['max_id_msg'];
}

// Free the resources associated with the result set
mysql_free_result($result);


$query = "SELECT MAX(ID_TOPIC) +30 AS max_id_topic FROM smf_topics";
$result = mysql_query($query);

// Check result
if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    die($message);
}

while ($row = mysql_fetch_assoc($result)) {
    echo '<BR>max_id_topic: ' . $row['max_id_topic'];
}

// Free the resources associated with the result set
mysql_free_result($result);

mysql_close($link);
?>

LiveWire

Quote from: SAFAD on July 12, 2010, 10:39:31 AM
this tip doesn't work :(
when you apply this
the old forum looses all categories !!

Cat ID has max value of 255. If the query makes more than 1 greater than that, it tries to make those all 255. The boards get changed to all point at the first category that the result was greater than 255.

It is best to add 'order by [field you are updating] desc' to the end of the update queries. Otherwise you may get an error if adding 200 to 5 and and old 205 exists. With the change, 205 will get changed to 405 first.

LiveWire

Here are the queries I ran to update posts of members that were in both boards. I then deleted the inserted duplicate members.

UPDATE smf_messages as m,
(SELECT `member_name`, min(`id_member`) old_id, max(`id_member`) new_id FROM `smf_members` group by `member_name`
having count(`member_name`) > 1) as i
SET m.ID [nofollow]_MEMBER = i.old_id where m.ID [nofollow]_MEMBER=i.new_id;

UPDATE smf_topics as t,
(SELECT `member_name`, min(`id_member`) old_id, max(`id_member`) new_id FROM `smf_members` group by `member_name`
having count(`member_name`) > 1) as i
SET t.ID [nofollow]_MEMBER_STARTED = i.old_id where t.ID [nofollow]_MEMBER_STARTED = i.new [nofollow]_id;


UPDATE smf_topics as t,
(SELECT `member_name`, min(`id_member`) old_id, max(`id_member`) new_id FROM `smf_members` group by `member_name`
having count(`member_name`) > 1) as i
SET t.ID [nofollow]_MEMBER_UPDATED = i.old_id where t.ID [nofollow]_MEMBER_STARTED = i.new [nofollow]_id;

Polls were left out. You have to do the same thing as the other fields, bump up id_poll in polls, poll_choices and topics and include the first two in the export.

Tamagochi

Hi,

This work for me. Thanks a lot. You can check it here: http://www.summarios.com/foro/

By the way I traduce the post to spanish, couse in spanish I didn't found something to do this. I include some apretiations from my process. You can find here in spanish:

http://www.creatupropiaweb.net/foros/como-mezclar-dos-foros-smf-usando-mysql/

Thanks again for your help.

Best Regards
Tamagochi
Version Foro SMF 2.0 RC3
Ganar Dinero en Internet
Tienda de Summarios


Vranx

I used the info in this thread to merge two forums. I did it a little different then the instructions. I wanted the smaller board's forum topics to be merged into the other forums existing boards. I also needed to merge some of the members. I managed to get the forums merged, however I have one problem. When you go into a board the threads are in date order, but one forums are on top of the other. In other words, I can see the threads from the first forum just fine in date order, then on page 6 the threads from the second forum start. Is there a way to get them all in date order together?

OnThePike

Hi.

Was wondering if you found a resolution to this sorting problem? It seems to me there's no effective way to list topics by date. Instead, they are listed by TOPIC_ID. My thought is now to completely revert and use the "primary" source as the "old" source and vice-versa. This would seem to ensure the topic id remains numerically constant. I just shudder at the notion of beginning this process all over again from scratch.

Before I do so, I'm wondering if you had a resolution. Or if there's a way to insert a new row into smf_topics that would pull the original post date and reply date in which to select, then use one of the "sort mods" and modify those with that new table data.

Thanks for any input.

Advertisement: