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?
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...
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):
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:
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.
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??
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 ?
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.
Hmmm still not getting anything to work. =(
Is there a way in mysql control center to show the results in realtime of your query?
Real time? You mean without re-executing it? I don't use the program, but I doubt it.
-[Unknown]
Quote from: Radianation on January 06, 2005, 10:19:12 PM
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??
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:
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:
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:
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...
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';
Quote from: [Unknown] on January 10, 2005, 03:21:48 AM
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?
MySQL Command Line Client ;).
-[Unknown]
*Gosh* (napoleon voice)
You're so l33t.
Guys, I still can't get this to work. I'm trying to do this command:
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.
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?
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
QuoteUPDATE 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
I can live without the user issue resolved, but the duplicate primary key is preventing this from moving forward.
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]
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>
Quote from: Radianation on January 30, 2005, 07:45:33 AM
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>
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]
Hmm.. Looks like a missing comma. checknig
Quote from: Radianation on January 31, 2005, 01:53:45 PM
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;
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. =(
Looks like I need to do the same type of process with the smf_topics first.
Yeah, this totally screwed up my website. Glad I just made another backup 10 minutes ago. See, the problem here is that the topics and message ID's conflict with existing topic and message ids on the new forum.
OMG I am not a lucky person. My backup restoration keeps timing out about 1/2 way through. I'm totally ******ed.
http://www.simplemachines.org/community/index.php?topic=18350.0
I say this basically *EVERY TIME* someone brings up the subject of combining forums. It's like 5000 times more complicated than people want to expect it to be.
-[Unknown]
Yeah, it really is. I appreciate the help of trying to make it work, but I think it's better left untouched. I thought that maybe a "stripped down" version would work. What frustrated was not the fact that it screwed everything up. I was upset because my backup was not restoring properly. I make it a point to do a backup before anything like this, especially after I lost a week's worth of posts last month from a stupid crash.
Once again, thank you everybody for trying to help me make this work. I highly advise not trying to do this unless you're a skilled SQL/PHP person. It's very complex.
Did you get your backup restored?
Yes I did. What I had to do to make it work was go in with word pad and isolate just the database I was trying to restore. I cut everything else out of the file and saved it as another name. Then I restored and it worked.
Glad you got it back up, [Unknown]s tools in the link I posted may help in future :).
Thank you. I was going to try those if the other method didn't work. I had already started on the restore when I saw your tools link, but those will surely come in handy. Thank you very much for the assistance.
try a little bit with the "INSERT IGNORE INTO" option and the "repair errors" option in the admin menü. that helped me.
Really... repair errors in mySQL or in SMF? Did you have the same problem I had before doing this?
I'm SCARED to try this again. lol
try it on a seperate box and with backups.
cant tell the way, because i was just playing around... and suddenly it worked.
I would think that by just adding like 10,000 to the ID and doing your method would work. Then the repair option would fix the ID fields or?