Merge two phpbb3 forums

Converting from other board software? Good decision! Need help? Have a question about a convertor? Wish to offer a convertor package? Post here.
Anti-Spam Guide
{S'PORT}_Skillz
Registered User
Posts: 284
Joined: Fri Aug 23, 2002 5:56 am

Re: Merge two phpbb3 forums

Post by {S'PORT}_Skillz » Sat Jul 30, 2011 9:56 am

Well I did it and everything works except how can I mass move/merge members from the source db to the "Registered Users" group on the target db? Seems ALL members from the source db aren't in any groups, therefore they have no permissions. Since their are ~1500 members, I do not want to have to manually move each one, so is their some kind of script or something that I can use to forcefully move everyone to the Registered Users group who is registered?

I know the Groups | members | find member page has a way to select as many users as you wish, but you can only do 25 at a time and I'm not sure what will happen if I chose duplicates. Reason for this is their is no way to only show users who do not have a group. So I have to show all users, even the ones already in the group. Not to mention doing ~55 pages would take a while.

User avatar
brudertorgen
Registered User
Posts: 28
Joined: Wed Jan 07, 2009 2:47 pm
Location: Kleines Dorf bei Kiel

Re: Merge two phpbb3 forums

Post by brudertorgen » Mon Aug 08, 2011 1:05 pm

Hello {S'PORT}_Skillz,

so 1500 users are a 'small' forum ;) ?

Ok, then I handle with tiny forums, but the way is the same :)

As I mentioned I wanted that after the migration nobody ha permissions because many users were 'outdated'.

Here is one way you could go:
1. backup the table 'phpbb_user_group'
2. search for the group_id of the 'registered users' group:
--> SELECT group_id FROM `phpbb_groups` WHERE group_name ='REGISTERED'
and write down the number (I call it X)
3. delete all user memberships for this group (to avoid duplicates)
--> delete from `phpbb_user_group` where group_id = X
4. Give all users membership
--> insert into phpbb_user_group (group_id,user_id ,group_leader,user_pending) select X, user_id, 0, 0 from phpbb_users

This should be all :)

{S'PORT}_Skillz
Registered User
Posts: 284
Joined: Fri Aug 23, 2002 5:56 am

Re: Merge two phpbb3 forums

Post by {S'PORT}_Skillz » Mon Aug 15, 2011 8:54 pm

Another issue I have found.

Seems users who were duplicates and/or had a custom color with their user name that color got transferred with the account. However, I can not seem to figure out how to drop it. Two examples:

My name "Skillz" is shown in Orange (for the main board, thats the admins color) and White (this is the regular members color) throughout the board. Seems posts/topics created on the other forum are colored in white, where as posts on the new/main site are the correct color Orange.

One of the admins from the old board, who had a default color of Red, is showing up as Red on the posts that got transferred to the new site. He hasn't posted on the new site, so I can't determine if his posts are still red or white like they should be.

User avatar
L33t_Of_Lag
Registered User
Posts: 143
Joined: Sun Nov 08, 2009 3:12 am
Location: Australia

Re: Merge two phpbb3 forums

Post by L33t_Of_Lag » Fri Feb 03, 2012 1:01 am

{S'PORT}_Skillz wrote:how can I mass move/merge members from the source db to the "Registered Users" group on the target db?
Import Users Tool. Found here, http://www.phpbb.com/community/viewtopi ... &t=1494875

I use that to migrate people from one board to another. ALL users will be put in the registered users group as default. There are also many more options like importing birthdays and stuff aswell.

User avatar
threeJ
Registered User
Posts: 38
Joined: Wed Apr 18, 2012 6:53 am
Contact:

Re: Merge two phpbb3 forums

Post by threeJ » Fri May 18, 2012 6:47 am

I struggled a lot reading this tutorial and then tried to find an easier way to merge two phpBB3 boards. This tutorial works great and i have heard people doing it successfully but it just went above my head. If some body is facing any trouble with this might wanna check my tutorial on merging two phpBB3 boards with provided testing boards and images to help the novice. Check the tutorial here :D

jagIT
Registered User
Posts: 1
Joined: Fri May 18, 2012 10:24 am

Re: Merge two phpbb3 forums

Post by jagIT » Fri May 18, 2012 10:28 am

I only want to merge my users,
I have no duplicates in the table I copy from (just deleted the duplicates from it or was that bad?)

When I do the big INSERT INTO command to copy the users I have the error:
#1062 - Duplicate entry '1150' for key 'PRIMARY'
and only 1 user was imported.

User avatar
threeJ
Registered User
Posts: 38
Joined: Wed Apr 18, 2012 6:53 am
Contact:

Re: Merge two phpbb3 forums

Post by threeJ » Fri May 18, 2012 4:19 pm

jagIT wrote:I only want to merge my users,
I have no duplicates in the table I copy from (just deleted the duplicates from it or was that bad?)

When I do the big INSERT INTO command to copy the users I have the error:
#1062 - Duplicate entry '1150' for key 'PRIMARY'
and only 1 user was imported.
Are your boards on the same database ? Maybe then i could help :roll:

User avatar
D¡cky
Former Team Member
Posts: 11812
Joined: Tue Jan 25, 2005 8:38 pm
Location: New Hampshire, USA
Name: Richard Foote
Contact:

Re: Merge two phpbb3 forums

Post by D¡cky » Fri May 18, 2012 4:28 pm

jagIT wrote:I only want to merge my users,
I have no duplicates in the table I copy from (just deleted the duplicates from it or was that bad?)

When I do the big INSERT INTO command to copy the users I have the error:
#1062 - Duplicate entry '1150' for key 'PRIMARY'
and only 1 user was imported.
You need to remove the user_id from the import file. user_ids need to be unique as well as username_clean.
Have you hugged someone today?

User avatar
D¡cky
Former Team Member
Posts: 11812
Joined: Tue Jan 25, 2005 8:38 pm
Location: New Hampshire, USA
Name: Richard Foote
Contact:

Re: Merge two phpbb3 forums

Post by D¡cky » Fri May 18, 2012 4:30 pm

threeJ wrote:If some body is facing any trouble with this might wanna check my tutorial on merging two phpBB3 boards with provided testing boards and images to help the novice. Check the tutorial here :D
Guests at your board cannot view the attachments because of not enough cash.
Have you hugged someone today?

User avatar
threeJ
Registered User
Posts: 38
Joined: Wed Apr 18, 2012 6:53 am
Contact:

Re: Merge two phpbb3 forums

Post by threeJ » Fri May 18, 2012 7:24 pm

D¡cky wrote:
threeJ wrote:If some body is facing any trouble with this might wanna check my tutorial on merging two phpBB3 boards with provided testing boards and images to help the novice. Check the tutorial here :D
Guests at your board cannot view the attachments because of not enough cash.
Thanks for pointing me out about that. Fixed :)

User avatar
brudertorgen
Registered User
Posts: 28
Joined: Wed Jan 07, 2009 2:47 pm
Location: Kleines Dorf bei Kiel

Re: Merge two phpbb3 forums

Post by brudertorgen » Thu May 24, 2012 11:28 am

Hey, first of all I'm happy and sad that this topic ist still alive. Happy beacuse some guys still care, sad because it seems that the phpbb3 developers haven't developed a general solution yet ;)

@threeJ : you say, 'my' way is above your head. So is yours above mine :D

In a technical - database - way I can't see a real merge with correct id's in your solution and even can't see, why it should work at all, but I would be happy if anybody else could try it successfully. The easier it is the better it is for all :) .

Your solution's requirement of having twop forums in the same database is maybe not the most common scenario ?! But this should be solvable.

User avatar
threeJ
Registered User
Posts: 38
Joined: Wed Apr 18, 2012 6:53 am
Contact:

Re: Merge two phpbb3 forums

Post by threeJ » Thu May 24, 2012 1:14 pm

@brudertorgen Hey! I didnt mean any offence at all. I am not a geek for sure but i figure things out. What you suggested works for many and im sure if done the right way it will definitely work the purpose. But it was way above my level of knowledge about such technicality :D :oops:

The reason why i tried and figured out my own way to merge two boards and it has been running without a single glitch since weeks now so im happy :D . I just thought of sharing what i did for myself. And yea! Even both the boards of mine were in different databases, but i managed to merge the databases too :P Well didn't have the time to write about it though, or maybe was too lazy :P

But i truly feel that its hi time that a mod should be developed for this and also fix the permission PUZZLE in phpBB3!!

Oh and yea! I did mention that the process of merging i wrote is already there in the knowledge base here But it lacked few stuff and i just added those. 8-) :P

And yea! One thing i would love to ask is, How do you change the avatar upload directory and make it one so that avatar could be share too! and the user statistics. How to merge them, cause it shows different number for TOTAL USERS and OUR NEWEST MEMBER in both the board! any help with that ? :roll:

User avatar
brudertorgen
Registered User
Posts: 28
Joined: Wed Jan 07, 2009 2:47 pm
Location: Kleines Dorf bei Kiel

Re: Merge two phpbb3 forums

Post by brudertorgen » Mon May 28, 2012 10:23 am

As I understand it the way you described is a kind of sharing users etc in several boards. This is reached through modifying the php code.
What I tried was merging two databases into one. This is a completely different approach. The php code doesn't have to be modified (having to do with three tier design)

As the end user only sees someting in his web browser, these two different things have from the user perspective the same goal and - if working - are both completely correct.

What I wanted to have is one forum with all users and content (posts, threads, pms and so on) from several forums, and I only showed a way to do it (not all questions are answered, it should be only a guide). Investigating too much effort is senseless for me, because - if the forum developers completele change the database design - I would have to rewrite this guide (altough the way will be allways the same, because relational databases all work the same way).

But this only for pointing out the difference, no solution is better or worse, the primary thin is that it works.

For your question about sharing avatars ... you should maybe ask the author from the knowledge article 'smigit'.

I still think the best would be if the phpbb3 developers would code a merge machine, even if it could be problem with older forum versions, but this problem is equal for all.

Intoxicant
Registered User
Posts: 1
Joined: Fri Jun 01, 2012 8:29 pm

Re: Merge two phpbb3 forums

Post by Intoxicant » Fri Jun 01, 2012 8:37 pm

brudertorgen wrote:Now we have to work with a cursor (which doesn't make me happy..)
Enter the following code in phpmyadmin (but edit the line: set touser=touser + 133; . The 133 is the remembered value from before.

Code: Select all

delimiter ;;
CREATE PROCEDURE msgto()
BEGIN
  DECLARE userid MEDIUMINT(8);
  DECLARE useridold MEDIUMINT(8);
  DECLARE done INT DEFAULT 0;
  DECLARE myid MEDIUMINT(8);
  DECLARE touser int;
  DECLARE toadr text;
  DECLARE bccadr text;
  declare cur1 cursor for select msg_id, to_address, bcc_address from phpbb_privmsgs_2;
  open cur1;
  repeat
    fetch cur1 into myid, toadr, bccadr;
    if instr(toadr,':') = 0 and instr(toadr,'u_') = 1 THEN
      set touser=convert(right(toadr,length(toadr)-2), UNSIGNED);
      set touser=touser + 133;
      set useridold=0;
      select user_id, user_id_old into userid, useridold from phpbb_users_2 
        where user_id_old=touser; 
      if useridold >=1 then
       set touser=userid;
      end if;
    end if;
    insert into phpbb_privmsgs_3 values (myid, concat('u_', convert(touser, char)), bccadr);
  UNTIL done END REPEAT;
  close cur1;
end;;
This code is awfull and I'm ashamed that it's from me. But with no time and a crying baby more was'nt possible.
After the procedure is created, execute it:

call msgto
Hey!

Thanks a lot for this script, I've tried to run it several times now but it doesn't work. I've used phpmyadmin and Mysql Workbench and always get the message "0 rows affected" when calling "msgto". The two forums together have a total of slightly more than 5000 registered users and 300 of them are registered to both forums. I made all the required changes and I'm merging two forums with a total of about 60000 pms.

Thanks, Carsten

User avatar
Black Antitoon
Registered User
Posts: 219
Joined: Mon Dec 25, 2006 12:12 pm
Contact:

Re: Merge two phpbb3 forums

Post by Black Antitoon » Sun Jun 03, 2012 12:08 am

Hello. :)

Try running the script I am uploading. Rename the file to .php. Open it and put in your db host, name, user, password and the user offset (the number you got from the initial query "SELECT max( user_id ) FROM phpbb_users;").

Follow bruder's steps until he increases IDs. Then skip the following steps (the translation table, etc...) and try running this script instead. Then resume following his steps starting at "Merge:".

This has worked for me several months ago, but I'm not 100% sure I have provided you with the correct steps today (I should go through the process again). Just backup everything and have a try, if it works it should also perfectly fix PMs recipient. Please let me know if this worked for you.

Be sure to run the script once only. Also, it may be quite CPU-consuming, so, if this is a problem for your host, be sure to run it locally on your computer. ;)
Do you recognise my avatar? :)

Locked

Return to “[3.0.x] Convertors”