I'd been putting this off, because it involves some horrible database hacks to maintain the correct values for the existing users, but I finally got up the guts and got it done. So far as I can tell, it has worked correctly, but if anyone finds that their region has changed underneath them, this might be why. If this has happened to you, please let me know (and look out the window just to re-assure yourself of where you are).
People that formerly had a region of "Australia - Other" now have a region of "Australia - Unspecified". For anyone who falls into this category, please update your region to more accurately reflect your actual location.
For anyone who's interested, the changes were performed by changing the values in the phpBB admin interface, and then running some SQL statements to update the database. Formulating the actual update statements were not the hard part but formulating the SQL to figure out what to change, and by how much, and to check the current status (before and after each update statement was run) was a bit tricky. Here's an example:
- Code: Select all
SELECT name, value
FROM users
LEFT JOIN fields_data ON users.user_id = fields_data.user_id
LEFT JOIN fields_lang ON fields_data.region_id = (fields_lang.option_id + 1)
WHERE field_id = 5
AND region_id = 12
For those that know SQL, check out the last join condition. Notice the "+1"? This difference between the same key field values in different tables had me really confused for a while when I first looked at this a couple of years ago. Why on earth would you have a key zero-based in one table, and the same key one-based in another table? Sure makes it very confusing to do anything with the data until you figure this out, and still a little confusing even afterwards.
