XF 2.1 Clearing all members' location data

[Wildcat Media]

We are installing an add-on where members will select their location from an auto-complete list. But prior to that, I want to clear out all users' location data. (We want to purge all the "joke" entries members have filled their locations with.) I realize it's a database query I need to do, but is there more than one table or field I need to be concerned with?

 

[Mr Lucky]

It should just be location in xf_user_profile

 

[Wildcat Media]

I would hope so. I tested deleting a few from the database and it seemed to work OK.

I've just seen instances (in XF and elsewhere) where the data isn't where I'd expect it to be, or stored in more than one location, which is why I thought I'd see about getting the official word on it.

 

[Paul B]

I've just seen instances (in XF ...) where the data ... stored in more than one location

I would be curious to know what those instances are.

The point of a relational database is to only store the data once.

 

[Wildcat Media]

Exactly, and that's how I would set one up myself. (In fact, I've done this in the past myself when developing a catalog site.)

I think it's not so much that the data is stored in more than one location, but not knowing the correct location to pull it from. One convoluted example is trying to change some of the site data in WordPress, when I configured it to work as a network (one WP installation powering many sites). It took me a few tries (many years ago) to see that some of the data was being stored in a location I hadn't expected it to be--some were in tables for the network sites, while other data still resided on the main installation.

Just chalk it up to being wary of things, and being bitten in the past...

 

[Mr Lucky]

I tried to remove all at once:

UPDATE xf_user_profile SET location = Null;

Simulate query showed success, but the actual query didn't:

Error

SQL query:

UPDATE xf_user_profile SET location = Null

MySQL said: Documentation
#1048 - Column 'location' cannot be null

 

[Paul B]

Null is not the same as an empty string ("") - you can set the value to empty, but not null as the schema doesn't permit it.

 

[Wildcat Media]

That's what I was thinking:

UPDATE xf_user_profile SET location = '';