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

XF 1.1 After import two fields for gender - how to combine?

Discussion in 'XenForo Questions and Support' started by Bellinis, Oct 12, 2011.

  1. Bellinis

    Bellinis Active Member

    In vbulletin 3.8 we used a custom profile field for Gender. This was a 'Single-Selection Menu' 'and contained 'Man' or 'Vrouw'. Now in Xenforo this is a standard field. So after the import I now have two gender fields. The defauld Xenforo one and the old custom one.

    Is there some way/query to import my old gender data to the new one?

    Thanks!
     
    zooki likes this.
  2. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Run this query on your database:

    Code:
    UPDATE xf_user AS u
    SET u.gender = (
    	SELECT IF(ufv.field_value = '', '', IF(ufv.field_value = 'male', 'male', 'female'))
    	FROM xf_user_field_value AS ufv
    	WHERE ufv.user_id = u.user_id
    	AND ufv.field_id = 'my_identifier'
    );
    
    You need to change the two red pieces. male is the value for your imported profile field that defines the male gender. my_identifier is the field_id of your imported profile field which can be seen among the records in the xf_user_field_value table.

    This query assumes there are only 3 possible values for your imported field... empty, male, and female. Female results from the first two cases not being true. There is no transgender or any other values covered by this query. If you need to cover additional values then this query needs to be modified. Otherwise the extra values will all become females.
     
    adwade, sadiq6210, Dakis and 5 others like this.
  3. Bellinis

    Bellinis Active Member

    Thank you so much Jake!
    But how would it be in my case since I use 'Man' and 'Vrouw' for male/female? ;)
     
  4. Brogan

    Brogan XenForo Moderator Staff Member

    Presumably you just edited the phrases?

    If so, that won't affect the database tables.
     
  5. Bellinis

    Bellinis Active Member

    See the screenshot of my old vbdatabase. Field11 contains Man, Vrouw or is not specified. So it's hardcoded.
     

    Attached Files:

  6. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    If 'Man' is a field_value in the xf_user_field_value table then you need to specify that in the query, like so:

    Code:
    UPDATE xf_user AS u
    SET u.gender = (
    	SELECT IF(ufv.field_value = '', '', IF(ufv.field_value = 'Man', 'male', 'female'))
    	FROM xf_user_field_value AS ufv
    	WHERE ufv.user_id = u.user_id
    	AND ufv.field_id = 'my_identifier'
    );
    
    Man is now specified as the value for "male". You don't have to specify "Vrouw" as being female. The query assumes female if the field is not empty or Man.
     
    smimosmile likes this.
  7. Bellinis

    Bellinis Active Member

    Worked perfectly! Thanks again :)
     
  8. fredrikse

    fredrikse Active Member

    I'm going to do move gender data from a custom field to the standard field in XenForo.

    This is what my custom field info looks like in the XenForo database:

    fieldID.png

    Is this query correct?

    UPDATE xf_user AS u
    SET u.gender = (
    SELECT IF(ufv.field_value = '', '', IF(ufv.field_value = 'man', 'male', 'female'))
    FROM xf_user_field_value AS ufv
    WHERE ufv.user_id = u.user_id
    AND ufv.field_id = 'koen'
    );

    I don't really understand what the male part is doing? Why is only male ("man" in swedish) in field value for koen in the attached picture?
     
  9. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Yes.

    Don't change the 'male' and 'female' parts. Those are values used by XenForo.
     
  10. fredrikse

    fredrikse Active Member

    Thanks! It seems like it worked. I have to verify it some more. How is this expression evaluated?

    PHP:
    SELECT IF(ufv.field_value '''', IF(ufv.field_value 'Man''male''female'))
     
  11. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    If the field is empty then no gender. Else if the field is 'Man' then the gender is male. Else the gender is female.
     
    fredrikse likes this.
  12. sadiq6210

    sadiq6210 Active Member

    Genius !

    Thanks Jake
     

Share This Page