XF 1.1 Mass move "Location" info from imported custom field, to XF *stock* 'Location' field?

CTXMedia

Formerly CyclingTribe
#1
When I import from IP.Board it creates my custom fields, one of which is "Location".

XF has its own built-in Location field so how would I mass move the data from my custom field to the built-in XF one?

Thanks,
Shaun :D
 

Jake Bunce

XenForo moderator
Staff member
#2
Run this query on your database:

Rich (BB code):
UPDATE xf_user_profile AS up
SET up.location = (
	SELECT ufv.field_value
	FROM xf_user_field_value AS ufv
	WHERE ufv.user_id = up.user_id
	AND ufv.field_id = 'customfield'
);
You just need to enter the field_id of your custom location field.
 

Alfa1

Well-known member
#4
@Jake Bunce Several thousands of members have already filled in the default field on my site. Is there a way to copy the content of the custom field into the default field unless there already is a value in the default field?
 

Alfa1

Well-known member
#5
@Chris D
I tried using the query you provided for the gender field and adapted it to location field. The name of my custom user field is 'location'. I tried this query:

UPDATE xf_user AS u
SET u.location = (
SELECT ufv.field_value
FROM xf_user_field_value AS ufv
WHERE ufv.user_id = u.user_id
AND ufv.field_id = 'location'
)
WHERE u.location = '';

Unfortunately the result was:
Unknown column 'u.location' in 'where clause'
 

Alfa1

Well-known member
#8
Unfortunately the above added the drop down names instead of the values. So it shows canada_2 instead of Canada.
I had not realized this as it was quite some years ago I set this up. It should be easy to fix. Would this query be correct to fix it?

UPDATE xf_user_profile AS u
SET u.location = 'Canada'
WHERE u.location = ´canada_2´;
 

ProCom

Well-known member
#11
Hey guys, great discussion! I need to do similar with the following because we imported a "website" field into the custom fields. I'd like to have this overwrite the default XF Field: "homepage", but only where homepage is blank and where the user hasn't logged in for the past 365 days.

The "homepage" field is in the "xf_user_profile" table
The "website" field is in the "xf_user_field_value"

Basically I want to do something like:

UPDATE "homepage" with "website" WHERE "homepage" is blank AND WHERE user_id has logged in within the past 365 days

Any help would be greatly appreciated!!!
 

Mouth

Well-known member
#13
UPDATE "homepage" with "website" WHERE "homepage" is blank AND WHERE user_id has logged in within the past 365 days
This will do it ...

Code:
UPDATE xf_user_profile AS up
INNER JOIN xf_user AS u ON up.user_id = u.user_id
INNER JOIN xf_user_field_value AS ufv ON up.user_id = ufv.user_id
SET up.homepage = ufv.field_value
WHERE ufv.field_id = 'website'
AND ufv.field_value <> ''
AND u.last_activity >= unix_timestamp(now() - interval 365 day)
AND up.homepage = '';
 
Top