So you need to know what country a user is from because you deal with PayPal. How do you make sure you have the right information to pass on? PayPal offers:

• Country codes
• State/province codes
• A COUNTRYCODE parameter
• A STATE parameter

The country and state codes are in HTML tables, which means you can copy and paste directly to your favorite spreadsheet software (OpenOffice.org Calc, right?). The two problems with this are that:

1. PayPal put extra spaces in the table
2. PayPal capitalized every letter in the country names

A guy named Joe Zack created a MySQL procedure that would capitalize the first letter of each word and transform all the other letters to lower-case.

I saved the country and state codes as separate CSV files (using double quotes and commas as delimiters) and then opened them in another text editor to replace ’ ”’ with just ‘“’. This solved problem #1 and I could import them into their own tables. Joe’s stored procedure solved problem #2:

UPDATE countries SET name = CAP_FIRST(name);

After IDing each table, I also decided that it was a good idea to link the states/provinces with their respective countries. That was accomplished with a similar UPDATE query that used state IDs in the database (Canada has the first 13, the united States have the rest). This allows me to query states/provinces by country in order to create optgroups for the end user.

I’ll leave you to figure out the other details of how to use this information but will include a dump of my current database tables. Hope it is useful.