is there an authentic list or database table for states(usa) and provinces(canada)?

blackhorse

Is there an authentic list or database table for states(usa) and provinces(canada)?

I need it to make a drop down menu for the state/province information for a e-commerce site which only do business in USA and Canada?

Scriptmaker

Don't know what you mean by authentic, but when I go looking to populate a drop down menu with states/provinces I just look at the post offices' "find an office" pages.

http://www.canadapost.ca/tools/pol/bin/search_ns-e.asp?searchType=quick

http://www.switchboard.com/usps.1355/dir/6_0/index.htm?mem=1355

blackhorse

Yeah. That will help. I will use these two lists. I was looking for a table create sql script or something like that that I can grab directly to create a mysql table. The list you have there will do. I will write my own script then.

blackhorse

zencart has a countries table create sql. I will check to see if they have canadian and usa states/provinces create sql etc.

nemonoman

http://www.usps.com/ncsc/lookups/abbr_state.txt

http://geonames.nrcan.gc.ca/info/prov_abr_e.php

blackhorse

to save other guys input time, i put my sql here based on the two lists above.

i used countries flag to tell the difference between usa states and canada provinces, and use the table name "provinces".

insert into provinces (name, abbreviation, countries_flag) values ('Alberta', 'AB', 1), ('British Columbia', 'BC', 1),('Manitoba', 'MB', 1),('New Brunswick', 'NB', 1),('Newfoundland and Labrador', 'NL', 1),('Northwest Territories', 'NT', 1),('Nova Scotia', 'NS', 1),('Nunavut', 'NU', 1),('Ontario', 'ON', 1),('Prince Edward Island', 'PE', 1),('Quebec', 'QC', 1),('Saskatchewan', 'SK', 1),('Yukon Territory', 'YT', 1);

insert into provinces (name, abbreviation, countries_flag) values ('ALABAMA', 'AL', 0), ('ALASKA', 'AK', 0), ('AMERICAN SAMOA', 'AS', 0),('ARIZONA', 'AZ', 0), ('ARKANSAS', 'AR', 0),('CALIFORNIA', 'CA', 0), ('COLORADO', 'CO', 0),('CONNECTICUT', 'CT', 0), ('DELAWARE', 'DE', 0),('DISTRICT OF COLUMBIA ', 'DC', 0), ('FEDERATED STATES OF MICRONESIA', 'FM', 0),('FLORIDA', 'FL', 0), ('GEORGIA', 'GA', 0),('GUAM', 'GU', 0), ('HAWAII', 'HI', 0),('IDAHO', 'ID', 0), ('ILLINOIS', 'IL', 0),('INDIANA', 'IN', 0), ('IOWA', 'IA', 0),('KANSAS', 'KS', 0), ('KENTUCKY', 'KY', 0),('LOUISIANA', 'LA', 0), ('MAINE', 'ME', 0),('MARSHALL ISLANDS', 'MH', 0), ('MARYLAND', 'MD', 0),('MASSACHUSETTS', 'MA', 0), ('MICHIGAN', 'MI', 0),('MINNESOTA', 'MN', 0), ('MISSISSIPPI', 'MS', 0),('MISSOURI', 'MO', 0), ('MONTANA', 'MT', 0),('NEBRASKA', 'NE', 0), ('NEVADA', 'NV', 0),('NEW HAMPSHIRE', 'NH', 0), ('NEW JERSEY', 'NJ', 0),('NEW MEXICO', 'NM', 0), ('NEW YORK', 'NY', 0),('NORTH CAROLINA', 'NC', 0), ('NORTH DAKOTA', 'ND', 0),('NORTHERN MARIANA ISLANDS', 'MP', 0), ('OHIO', 'OH', 0),('OKLAHOMA', 'OK', 0), ('OREGON', 'OR', 0),('PALAU', 'PW', 0), ('PENNSYLVANIA', 'PA', 0),('PUERTO RICO', 'PR', 0), ('RHODE ISLAND', 'RI', 0),('SOUTH CAROLINA', 'SC', 0), ('SOUTH DAKOTA', 'SD', 0),('TENNESSEE', 'TN', 0), ('TEXAS', 'TX', 0),('UTAH', 'UT', 0), ('VERMONT', 'VT', 0),('VIRGIN ISLANDS', 'VI', 0), ('VIRGINIA', 'VA', 0),('WASHINGTON', 'WA', 0), ('WEST VIRGINIA', 'WV', 0),('WISCONSIN', 'WI', 0), ('WYOMING', 'WY', 0);