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

[Bellinis]

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!

 

[Jake Bunce]

Run this query on your database:

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.

 

[Bellinis]

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

 

[Paul B]

Presumably you just edited the phrases?

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

 

[Bellinis]

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

 

[Jake Bunce]

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

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

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.

 

[Bellinis]

Worked perfectly! Thanks again

 

[fredrikse]

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:



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?

 

[Jake Bunce]

Is this query correct?

Yes.

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?

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

 

[fredrikse]

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

SELECT IF(ufv.field_value = '', '', IF(ufv.field_value = 'Man', 'male', 'female'))

 

[Jake Bunce]

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

SELECT IF(ufv.field_value = '', '', IF(ufv.field_value = 'Man', 'male', 'female'))

If the field is empty then no gender. Else if the field is 'Man' then the gender is male. Else the gender is female.

 

[Sadiq6210]

Run this query on your database:

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.

Genius !

Thanks Jake