• 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?

Bellinis

Active member
#1
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

XenForo moderator
Staff member
#2
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.
 

Jake Bunce

XenForo moderator
Staff member
#6
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:

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.
 

fredrikse

Active member
#8
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?
 
#10
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'))
 

Jake Bunce

XenForo moderator
Staff member
#11
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'))
If the field is empty then no gender. Else if the field is 'Man' then the gender is male. Else the gender is female.
 
#12
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.
Genius !

Thanks Jake