Ok, so you might have seen my previous article when I provided my fellow world coding community with a SQL table structure and content for all the countries. Well, I am back again but this time I have one for all the states of North America (including Canada).

If you want to see my other SQL Table for countries then you can view it by clicking MySQL Table – Listing all countries.

Ok, so again we will create the table in your already existing database:

CREATE TABLE `state_prov_codes` (
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  `code` char(2) NOT NULL default '',
  `state_prov` text NOT NULL,
  `country` char(2) NOT NULL default 'US',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `code` (`code`)
) TYPE=MyISAM AUTO_INCREMENT=63 ;

Now, we need to again populate this table with the values for American and Canadian States:

INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
 VALUES (1, 'AL', 'Alabama', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (2, 'AK', 'Alaska', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (3, 'AB', 'Alberta', 'CA');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (4, 'AZ', 'Arizona', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (5, 'AR', 'Arkansas', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (6, 'BC', 'British Columbia', 'CA');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (7, 'CA', 'California', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (8, 'CO', 'Colorado', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (9, 'CT', 'Connecticut', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (10, 'DE', 'Delaware', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (11, 'DC', 'District of Columbia', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (12, 'FL', 'Florida', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (13, 'GA', 'Georgia', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (14, 'HI', 'Hawaii', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (15, 'ID', 'Idaho', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (16, 'IL', 'Illinois', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (17, 'IN', 'Indiana', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (18, 'IA', 'Iowa', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (19, 'KS', 'Kansas', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (20, 'KY', 'Kentucky', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (21, 'LA', 'Louisiana', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (22, 'ME', 'Maine', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (23, 'MB', 'Manitoba', 'CA');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (24, 'MD', 'Maryland', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (25, 'MA', 'Massachusetts', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (26, 'MI', 'Michigan', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (27, 'MN', 'Minnesota', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (28, 'MS', 'Mississippi', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (29, 'MO', 'Missouri', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (30, 'MT', 'Montana', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (31, 'NE', 'Nebraska', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (32, 'NV', 'Nevada', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (33, 'NB', 'New Brunswick', 'CA');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (34, 'NF', 'Newfoundland', 'CA');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (35, 'NH', 'New Hampshire', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (36, 'NJ', 'New Jersey', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (37, 'NM', 'New Mexico', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (38, 'NY', 'New York', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (39, 'NC', 'North Carolina', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (40, 'ND', 'North Dakota', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (41, 'NS', 'Nova Scotia', 'CA');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (42, 'OH', 'Ohio', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (43, 'OK', 'Oklahoma', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (44, 'ON', 'Ontario', 'CA');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (45, 'OR', 'Oregon', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (46, 'PA', 'Pennsylvania', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (47, 'PE', 'Prince Edward Island', 'CA');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (48, 'QC', 'Quebec', 'CA');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (49, 'RI', 'Rhode Island', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (50, 'SK', 'Saskatchewan', 'CA');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (51, 'SC', 'South Carolina', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (52, 'SD', 'South Dakota', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (53, 'TN', 'Tennessee', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (54, 'TX', 'Texas', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (55, 'UT', 'Utah', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (56, 'VT', 'Vermont', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (57, 'VA', 'Virginia', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (58, 'WA', 'Washington', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (59, 'WV', 'West Virginia', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (60, 'WI', 'Wisconsin', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (61, 'WY', 'Wyoming', 'US');
INSERT INTO `state_prov_codes` (`id`, `code`, `state_prov`, `country`)
VALUES (62, 'NA', 'None of These', 'NA');

There you have it folks. I hope this little MySQL Query helps you just as much as it helps me. Heres to better coding and faster payments.