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

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

Discussion in 'XenForo Questions and Support' started by CyclingTribe, Oct 27, 2011.

  1. CyclingTribe

    CyclingTribe Well-Known Member

    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
     
  2. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Run this query on your database:

    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.
     
    Pauly, Alfa1, Andrej and 1 other person like this.
  3. CyclingTribe

    CyclingTribe Well-Known Member

    Many thanks Jake :)
     
  4. Alfa1

    Alfa1 Well-Known Member

    @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?
     
  5. Alfa1

    Alfa1 Well-Known Member

    @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'
     
  6. Mouth

    Mouth Well-Known Member

    Wrong table on the UPDATE ...
    Code:
    UPDATE xf_user_profile 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 = '';
     
    Jake Bunce, Chris D and Alfa1 like this.
  7. Alfa1

    Alfa1 Well-Known Member

    Thanks!
     
  8. Alfa1

    Alfa1 Well-Known Member

    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┬┤;
     
  9. Mouth

    Mouth Well-Known Member

    Yes, but you've mixed your apostrophe types so mysql will probably throw back an error.

    Code:
    UPDATE xf_user_profile 
    SET location = 'Canada' 
    WHERE location = 'canada_2';
    
     
    Alfa1 likes this.
  10. Alfa1

    Alfa1 Well-Known Member

    Thanks! I have been able to fix it.
     
    Mouth likes this.

Share This Page