1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

XF 1.3 Importing User Groups and User Group Members

Discussion in 'Installation, Upgrade, and Import Support' started by Floyd R Turbo, Aug 18, 2014.

  1. Floyd R Turbo

    Floyd R Turbo Well-Known Member

    I am working on a DNN > XF conversion. I have managed to get all of the forum content and member information ported over, but not user groups.

    I have the MSSQL database and was able to create a master list of what users are part of which usergroup in that database, so I will be able to re-create the xf_user_group_relation table manually and then import it into mySQL via phpMyAdmin. I have noticed that in the xf_user table, the column secondary_group_ids seems to correlate to the list in the relation table.

    Will I need to synchronize both of these or can I just import the relation table and then run a cron or rebuilt an index to do that?

    Is there another way to do this instead of via SQL?

    Thanks in advance.
  2. Floyd R Turbo

    Floyd R Turbo Well-Known Member

    Any XF database experts out there?
  3. Floyd R Turbo

    Floyd R Turbo Well-Known Member

    Still looking for an answer to this one, anyone???

    I haven't done any trial & error yet...hoping to hear from someone who has done this already possibly, or at least someone who knows how xf_user/secondary_group_ids relates or interacts with xf_user_group_relation

    @Kier ?
    @Mike ?
  4. Floyd R Turbo

    Floyd R Turbo Well-Known Member

    Well I set up the user groups and built the xf_user_group_relation table based on the source database and imported it. I set Registered as primary group for everyone except unconfirmed accounts, etc.

    The result is no change in user group assignments. So it appears that the secondary groups column in xf_user is the one that matters. So do I need to build both so that they match?

    In MySQL the text in the secondary group field looks weird, like is someone is part of groups 3 & 4 it shows 332c34, but when I export it, it comes across (in excel) as 3,4. If I set things up in excel I think I would have to save it as text delimited with a different delimiter, and then import that appropriately.

    I use DigDB with Excel so I can easily rebuild the secondary group column however I need to. I really just need confirmation as to whether this is necessary, or if I am missing something / is there a simpler way.
    Last edited: Aug 21, 2014
  5. Floyd R Turbo

    Floyd R Turbo Well-Known Member

    Updating my question/issue...for anyone that might be able to answer it. 4 days and no response?

    The table xf_user appears to have columns that are "mirrors" of the xf_user_group_relation table, specifically, user_group_id and secondary_group_ids. It appears that user_group_id matches up with whatever is set as the primary group in the relation table, and secondary_group_ids lists all additional (non-primary) groups that each user is a member of.

    I'm guessing that there is a good reason why the information is duplicated in two places.

    So my question is, do I have to synchronize these columns between the two tables, or is there a way to cause the columns in xf_user to be "rebuilt" based on the values in xf_user_group_relation via ACP or otherwise?

    Then, if this is the case, the way the values of secondary_group_ids is displayed in phpmyadmin is a bit odd. For example if I have a user that has 2 (registered) and primary and 3 and 4 as secondary, this is displayed as 332c34. When I export this table and open in excel, it shows it as 3,4.

    In another XF install where I have many more user groups, this field for one users is 332c342c352c392c31302c31312c3132 which in excel shows up as 3,4,5,9,10,11,12, which maps out if you replace the 2c3 with a comma and eliminate the other 3's....but I don't know if this will happen automatically when I import a CSV excel file or not (CSV puts quotes around "3,4,5,9,10,11,12"...)

    Any insight on how to accomplish this other than I have outlined above (using DigDB to synch and re-create the secondary ID column and importing that via phpmyadmin) would be appreciated.
  6. Floyd R Turbo

    Floyd R Turbo Well-Known Member

    Interestingly enough, I went to check the xf_user_group_relation table this morning and notice that it has reverted back to what it was before I replaced it last night. So it appears that this table it built based on the values set in the 2 columns in xf_user.

    Now, if I knew how to cause this build process to occur (cron?) then I would only need to build the columns in xf_user....
  7. Floyd R Turbo

    Floyd R Turbo Well-Known Member

    As it turns out, rebuilding the user cache replaced the xf_user_group_relation table with the primary and secondary group IDs, presumably for use elsewhere.

    So I finally figured this out. And as a result, I am super-proud of my ability to avoid having to write some complex SQL script to do it.

    Essentially, what I did was take the formatted table that matched the structure of the relation table, and used an Excel VB code snippet I pulled off the web to transpose all secondary roles was associated with one user ID into the same row with said ID.

    Then I used DigDB to combine the columns and put commas between them all, then weed out the extra commas so that it matched the format.

    I then pasted these columns into the exported xf_user file and replaced the primary and secondary columns.

    Here's where it got tricky. I couldn't figure out how to force excel to put quotation marks around every single cell in the saved CSV file. A little googling told me you could do this with OpenOffice and possibly Access, but I found it easier to use notepad++. So in excel I replaced the comma in the secondary IDs column with a unique word (in this case, comma) and then opened that up in n++ and replaced all commas with "," and appended and prepended each line with " and then replaced all instances of the word "comma" with an actual comma.

    Import as CSV in phpmyadmin, rebuild user cache, success!
    ibrian likes this.

Share This Page