Import SMF forum 1 to SMF forum 2 but stripped down

[NoRad]

I know merging to forums is kind of tricky, but what about a stripped down import/merge. For example, you know how if you delete a user but leave their posts it just has their name with no profile link or whatever? Is it possible to import forum 1 to forum 2 into SPECIFIC NEW child forums without bothering to import the users and link the posts? I just want the content there for reference purposes. Any ideas?

[Oldiesmann]

Yeah, that can be done. It's a lot simpler than trying to copy everything.

Here's how you would do that.

On your old forum (the one you'll be copying stuff from), create three new boards to act as the child boards and move the topics the way you want them. Make a note of the ID number of each of these boards (you'll need it in a minute).

Next, we update the database to make it look like everyone who posted anything in any of those boards was a guest...

Code Select Expand

UPDATE smf_messages SET ID_MEMBER = '-1' WHERE ID_BOARD == 'board1id' || ID_BOARD == 'board2id' || ID_BOARD == 'board3id';
UPDATE smf_topics SET ID_MEMBER_STARTED = '-1', ID_MEMBER_UPDATED = '-1' WHERE ID_BOARD == 'board1id' || ID_BOARD == 'board2id' || ID_BOARD == 'board3id';


Next, copy the posts from one board to the other (if the boards are on two different databases, you will need to make sure that the same user account has access to both databases and you will also have to put "databasename." at the beginning of the table names (ie mydatabase.smf_messages):

Code Select Expand

INSERT INTO othersmf_messages SELECT * FROM smf_messages WHERE ID_BOARD == 'board1id' || ID_BOARD == 'board2id' || ID_BOARD == 'board3id';
INSERT INTO othersmf_topics SELECT * FROM smf_topics WHERE ID_BOARD == 'board1id' || ID_BOARD == 'board2id' || ID_BOARD == 'board3id';

Now we update the board IDs for these messages and topics to match the new ones:

Code Select Expand

UPDATE othersmf_messages SET ID_BOARD = 'newboardid' WHERE ID_BOARD = 'board1id';
UPDATE othersmf_topics SET ID_BOARD = 'newboardid' WHERE ID_BOARD = 'board1id';
UPDATE othersmf_messages SET ID_BOARD = 'newboardid' WHERE ID_BOARD = 'board2id';
UPDATE othersmf_topics SET ID_BOARD = 'newboardid' WHERE ID_BOARD = 'board2id';
UPDATE othersmf_messages SET ID_BOARD = 'newboardid' WHERE ID_BOARD = 'board3id';
UPDATE othersmf_topics SET ID_BOARD = 'newboardid' WHERE ID_BOARD = 'board3id';

Finally, go into your admin center, click on "Forum Maintenance" and select "Recount board totals and statistics". This will update the rest of the stuff (latest message in each board, latest message in each topic, most recent topics, etc.).

That should work, but I recommend backing everything up first so you don't lose any important data.

[NoRad]

I've written down the id for the source boards I want to copy:

Source   Destination
2.0      25.0
8.0    20.0
17.0   26.0
19.0   27.0
6.0    24.0
27.0   28.0

and I modified the query and ran it with no results??

Code Select Expand

UPDATE smf_messages SET ID_MEMBER = '-1' WHERE ID_BOARD == '2.0' || ID_BOARD == '8.0' || ID_BOARD == '17.0' || ID_BOARD == '19.0' || ID_BOARD == '6.0' || ID_BOARD == '27.0';

UPDATE smf_topics SET ID_MEMBER_STARTED = '-1', ID_MEMBER_UPDATED = '-1' WHERE ID_BOARD == '2.0' || ID_BOARD == '8.0' || ID_BOARD == '17.0' || ID_BOARD == '19.0' || ID_BOARD == '6.0' || ID_BOARD == '27.0';

I think what I want to do is rename the boardid something unique before importing, that way when I run the update query after importing to set the correct id's I don't accidently move posts from other destination forums. Sound like an idea that will work?

Kind of a wishlist question now, but 2 or 3 of the top posters on the old site are members on the new site. If I have their member ID's handy for both sites, can I do a query to update the source with the destination ID_member and then if it's nota match set it to -1 ?

[NoRad]

This might be a fun little project for me to do in PHP, allowing other people the ability to import other forums for archival purposes.

[NoRad]

Hmmm still not getting anything to work. =(

Is there a way in mysql control center to show the results in realtime of your query?

[[Unknown]]

Real time?  You mean without re-executing it?  I don't use the program, but I doubt it.

-[Unknown]

[Oldiesmann]

I've written down the id for the source boards I want to copy:

Source   Destination
2.0 25.0
8.0    20.0
17.0   26.0
19.0   27.0
6.0    24.0
27.0   28.0

and I modified the query and ran it with no results??

Code Select Expand

UPDATE smf_messages SET ID_MEMBER = '-1' WHERE ID_BOARD == '2.0' || ID_BOARD == '8.0' || ID_BOARD == '17.0' || ID_BOARD == '19.0' || ID_BOARD == '6.0' || ID_BOARD == '27.0';

UPDATE smf_topics SET ID_MEMBER_STARTED = '-1', ID_MEMBER_UPDATED = '-1' WHERE ID_BOARD == '2.0' || ID_BOARD == '8.0' || ID_BOARD == '17.0' || ID_BOARD == '19.0' || ID_BOARD == '6.0' || ID_BOARD == '27.0';

I think what I want to do is rename the boardid something unique before importing, that way when I run the update query after importing to set the correct id's I don't accidently move posts from other destination forums. Sound like an idea that will work?

Kind of a wishlist question now, but 2 or 3 of the top posters on the old site are members on the new site. If I have their member ID's handy for both sites, can I do a query to update the source with the destination ID_member and then if it's nota match set it to -1 ?

The ".0" in the board ID is how SMF determines what page you're on in that board (same thing with topics), so the board ID is the number without the ".0" on it.

As far as board IDs - with the exception of board 27 (we can update that one first, thereby avoiding problems), do this:

Code Select Expand

UPDATE smf_boards SET ID_BOARD = ID_BOARD + 100 WHERE ID_BOARD == '20' || ID_BOARD == '24' || ID_BOARD == '25' || ID_BOARD == '26' || ID_BOARD == '28';
UPDATE smf_topics SET ID_BOARD = ID_BOARD + 100 WHERE ID_BOARD == '20' || ID_BOARD == '24' || ID_BOARD == '25' || ID_BOARD == '26' || ID_BOARD == '28';
UPDATE smf_messages SET ID_BOARD = ID_BOARD + 100 WHERE ID_BOARD == '20' || ID_BOARD == '24' || ID_BOARD == '25' || ID_BOARD == '26' || ID_BOARD == '28';

Also, if you have child boards in any of those boards:

Code Select Expand

UPDATE smf_boards SET ID_PARENT = ID_PARENT + 100 WHERE ID_BOARD == '20' || ID_BOARD == '24' || ID_BOARD == '25' || ID_BOARD == '26' || ID_BOARD == '28';

Then, you could just update stuff like this:

Code Select Expand

UPDATE smf_boards SET ID_BOARD = 28 WHERE ID_BOARD = 27;
UPDATE smf_boards SET ID_BOARD = 27 WHERE ID_BOARD = 19;
UPDATE smf_boards SET ID_BOARD = 26 WHERE ID_BOARD = 17;
UPDATE smf_boards SET ID_BOARD = 25 WHERE ID_BOARD = 2;
UPDATE smf_boards SET ID_BOARD = 24 WHERE ID_BOARD = 6;
UPDATE smf_boards SET ID_BOARD = 20 WHERE ID_BOARD = 8;
UPDATE smf_messages SET ID_BOARD = 28 WHERE ID_BOARD = 27;
UPDATE smf_messages SET ID_BOARD = 27 WHERE ID_BOARD = 19;
UPDATE smf_messages SET ID_BOARD = 26 WHERE ID_BOARD = 17;
UPDATE smf_messages SET ID_BOARD = 25 WHERE ID_BOARD = 2;
UPDATE smf_messages SET ID_BOARD = 24 WHERE ID_BOARD = 6;
UPDATE smf_messages SET ID_BOARD = 20 WHERE ID_BOARD = 8;
UPDATE smf_topics SET ID_BOARD = 28 WHERE ID_BOARD = 27;
UPDATE smf_topics SET ID_BOARD = 27 WHERE ID_BOARD = 19;
UPDATE smf_topics SET ID_BOARD = 26 WHERE ID_BOARD = 17;
UPDATE smf_topics SET ID_BOARD = 25 WHERE ID_BOARD = 2;
UPDATE smf_topics SET ID_BOARD = 24 WHERE ID_BOARD = 6;
UPDATE smf_topics SET ID_BOARD = 20 WHERE ID_BOARD = 8;

As far as members, yeah, you can do that...

Code Select Expand

UPDATE smf_messages SET ID_MEMBER = 'theirnewmemberid' WHERE ID_MEMBER = 'theiroldmemberid';
UPDATE smf_topics SET ID_MEMBER_STARTED = 'theirnewmemberid' WHERE ID_MEMBER_STARTED = 'theiroldmemberid';
UPDATE smf_topics SET ID_MEMBER_UPDATED = 'theirnewmemberid' WHERE ID_MEMBER_UPDATED = 'theiroldmemberid';

[NoRad]

Real time?  You mean without re-executing it?  I don't use the program, but I doubt it.

-[Unknown]

Well, I just wasn't seeing anything when I executed the query so I couldn't tell if anything was happening. What do you use?

[[Unknown]]

MySQL Command Line Client .

-[Unknown]

[NoRad]

*Gosh* (napoleon voice)

You're so l33t.

[NoRad]

Guys, I still can't get this to work. I'm trying to do this command:

Code Select Expand

INSERT INTO louipimps.smf_messages SELECT * FROM smf_messages WHERE ID_BOARD == '28' || ID_BOARD == '27' || ID_BOARD == '26' || ID_BOARD == '25' || ID_BOARD == '24' || ID_BOARD == '20';
INSERT INTO louipimps.smf_topics SELECT * FROM smf_topics WHERE ID_BOARD == '28' || ID_BOARD == '27' || ID_BOARD == '26' || ID_BOARD == '25' || ID_BOARD == '24' || ID_BOARD == '20';


It doesn't appear to be copying the information over. I've changed some user ID's and that worked fine. I ran the other code without trouble. I just can't seem to get anything to copy.

[NoRad]

When running from the command line I get a 1064 syntax error on that query. So I changed the == to =. Now it says I have duplicate key for 1. Do I need to empty out the new forums if people have posted in them?

...

update

I emptied out the new forums and that didn't matter. I see now that it doesn't like me updating the primary key with duplicates. How can I avoid this?

[NoRad]

And finally, one more thing I noticed. I was able to update about 10 users ID's for their new forum ID. When I go to run that one udpate for setting people as guests, I need an exception if their ID is one of those 10 then don't set them as a guest. I don't know how to do this with SQL. Checking.. if it were old asp it might look something like this...

If the userid = "1119" or "9125" then
<nothing>
else

UPDATE smf_messages SET ID_MEMBER = '-1' WHERE ID_BOARD == 'board1id' || ID_BOARD == 'board2id' || ID_BOARD == 'board3id';
UPDATE smf_topics SET ID_MEMBER_STARTED = '-1', ID_MEMBER_UPDATED = '-1' WHERE ID_BOARD == 'board1id' || ID_BOARD == 'board2id' || ID_BOARD == 'board3id';

end if

[NoRad]

I can live without the user issue resolved, but the duplicate primary key is preventing this from moving forward.

[[Unknown]]

Code Select Expand

UPDATE smf_messages
SET ID_MEMBER = 0
WHERE ID_BOARD IN (1, 2, 3)
   AND ID_MEMBER NOT IN (1119, 9125);

UPDATE smf_topics
SET ID_MEMBER_UPDATED = 0
WHERE ID_BOARD IN (1, 2, 3)
   AND ID_MEMBER_UPDATED NOT IN (1119, 9125);

UPDATE smf_topics
SET ID_MEMBER_STARTED = 0
WHERE ID_BOARD IN (1, 2, 3)
   AND ID_MEMBER_STARTED NOT IN (1119, 9125);

-[Unknown]

[NoRad]

Thanks Unknown, that worked.

I am at the final step and it's giving me this:

mysql> INSERT INTO louipimps.smf_messages SELECT * FROM tsl.smf_messages WHERE ID_BOARD = '28' || ID_BOARD = '27' || ID_BOARD = '26' || ID_BOARD = '25' || ID_BOARD = '24' || ID_BOARD = '20';
ERROR 1062: Duplicate entry '1' for key 1
mysql>

[[Unknown]]

Thanks Unknown, that worked.

I am at the final step and it's giving me this:

Code Select Expand

mysql> INSERT INTO louipimps.smf_messages SELECT * FROM tsl.smf_messages WHERE ID_BOARD = '28' || ID_BOARD = '27' || ID_BOARD = '26' || ID_BOARD = '25' || ID_BOARD = '24' || ID_BOARD = '20';
ERROR 1062: Duplicate entry '1' for key 1
mysql>

That query ain't gonna work.

INSERT INTO louipimps.smf_messages
   (ID_TOPIC, ID_BOARD, posterTime, ID_MEMBER subject, posterName, posterEmail, posterIP, smileysEnabled, modifiedTime, modifiedName, body, icon)
SELECT ID_TOPIC, ID_BOARD, posterTime, ID_MEMBER subject, posterName, posterEmail, posterIP, smileysEnabled, modifiedTime, modifiedName, body, icon
FROM tsl.smf_messages
WHERE ID_BOARD IN (20, 24, 25, 26, 27, 28)
ORDER BY posterTime;

-[Unknown]

[NoRad]

Hmm.. Looks like a missing comma. checknig

[Tristan Perry]

Hmmm

ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'subject, posterName, posterEmail, posterIP, smileysEnabled, mod

Try this:

INSERT INTO louipimps.smf_messages
   (ID_TOPIC, ID_BOARD, posterTime, ID_MEMBER subject, posterName, posterEmail, posterIP, smileysEnabled, modifiedTime, modifiedName, body, icon)
SELECT ID_TOPIC, ID_BOARD, posterTime, ID_MEMBER, subject, posterName, posterEmail, posterIP, smileysEnabled, modifiedTime, modifiedName, body, icon
FROM tsl.smf_messages
WHERE ID_BOARD IN (20, 24, 25, 26, 27, 28)
ORDER BY posterTime;

[NoRad]

added a comma and it worked.

Query OK, 8319 rows affected (0.81 sec)
Records: 8319  Duplicates: 0  Warnings: 0

Now, when I go to my board I see no new messages, so I find and repair errors. Problem... It put all of the posts in the salvaged area. =(