Change State to use ISO Codes

[kwright]

I need to change our store to use the ISO US State codes instead of the complete spelling of the State.

The address book-drop down doesn't matter either way, I just need the ISO codes to be in the final order info!

Our order fulfillment software uses the ISO codes and chokes when importing the full spelling of the States.

Thanks

[fairestcape]

If you are familiar with phpMyAdmin, you could do this:

BACKUP YOUR DATABASE FIRST!

Log into phpMyAdmin and open the relevant database

Navigate to the zones table (left-hand menu) and click on the ICON in that menu (not the word).

Click EXPORT at the top of the screen.

When the export options come up, make sure to select UPDATE as the export type.

It will return a list something like this (I show the first two lines)

UPDATE `zones` SET `zone_id` = 1,`zone_country_id` = 223,`zone_code` = 'AL',`zone_name` = 'Alabama' WHERE  `zones`.`zone_id` = 1;
UPDATE `zones` SET `zone_id` = 2,`zone_country_id` = 223,`zone_code` = 'AK',`zone_name` = 'Alaska' WHERE  `zones`.`zone_id` = 2;

(Now, bear in mind that you may have different zone_country_id numbers and different zone_id numbers. As you have exported from your own unique database, these numbers will be genuine for your database, so do not alter them.)

COPY the exported data to your computer clipboard.

Open a PLAIN TEXT (or SQL) editor and PASTE the copied lines.

Then make the following changes (compare the SQL above with the changes below)

UPDATE `zones` SET `zone_id` = 1,`zone_country_id` = 223,`zone_code` = 'AL',`zone_name` = 'AL' WHERE  `zones`.`zone_id` = 1;
UPDATE `zones` SET `zone_id` = 2,`zone_country_id` = 223,`zone_code` = 'AK',`zone_name` = 'AK' WHERE  `zones`.`zone_id` = 2;

When all the lines are edited (by changing the COUNTRY NAME TEXT as shown above to the ID TEXT), highlight and copy all the lines, making sure each record has a ; at the end of the line.

Go back to phpMyAdmin screen and Click zones icon (left menu) again .

At top of right hand pane, click SQL.

Delete any text put in the window by default so you have a clear window.


PASTE copied lines into window.

Click GO button.

Zones will now be named the same as their ISO 2-digit ID's.

Bear in mind that this is a "site-wide" change - your states will no longer be displayed as the full name - but by their ISO ID.

[kwright]

Thanks fairestcape,

So then the zone name is just a label and not used for any program flow?

Could I just do an sql:

UPDATE zones SET zone_name = zone_code WHERE 1;

[DrByte]

kwright, yes, that's much simpler.
Although to focus on US states specifically I'd suggest you change your WHERE clause to: WHERE zone_country_id = 223;