I had another PHPR user email me the other day and ask how was the best way to go about adding a table for US states that already had all of the states already listed. I had to think for a minute because what I usually do is build a table for states and leave it blank so that the admin of the site can add states as he or she enters in content. The only thing I typically do is to set it so that the states are listed alphabetically. Sergey posted an entry on the Xlinesoft forums though that shows how you can just use this sql statement to create the table with statements in it already, I am posting it here so that I can just copy / paste it in if this ever comes up again…
1: CREATE TABLE IF NOT EXISTS states (
2: id INT NOT NULL auto_increment,
3: name CHAR(40) NOT NULL,
4: abbrev CHAR(2) NOT NULL,
5: PRIMARY KEY (id)
6: );
7:
8: INSERT INTO states VALUES (NULL, 'Alaska', 'AK');
9: INSERT INTO states VALUES (NULL, 'Alabama', 'AL');
10: INSERT INTO states VALUES (NULL, 'American Samoa', 'AS');
11: INSERT INTO states VALUES (NULL, 'Arizona', 'AZ');
12: INSERT INTO states VALUES (NULL, 'Arkansas', 'AR');
13: INSERT INTO states VALUES (NULL, 'California', 'CA');
14: INSERT INTO states VALUES (NULL, 'Colorado', 'CO');
15: INSERT INTO states VALUES (NULL, 'Connecticut', 'CT');
16: INSERT INTO states VALUES (NULL, 'Delaware', 'DE');
17: INSERT INTO states VALUES (NULL, 'District of Columbia', 'DC');
18: INSERT INTO states VALUES (NULL, 'Federated States of Micronesia', 'FM');
19: INSERT INTO states VALUES (NULL, 'Florida', 'FL');
20: INSERT INTO states VALUES (NULL, 'Georgia', 'GA');
21: INSERT INTO states VALUES (NULL, 'Guam', 'GU');
22: INSERT INTO states VALUES (NULL, 'Hawaii', 'HI');
23: INSERT INTO states VALUES (NULL, 'Idaho', 'ID');
24: INSERT INTO states VALUES (NULL, 'Illinois', 'IL');
25: INSERT INTO states VALUES (NULL, 'Indiana', 'IN');
26: INSERT INTO states VALUES (NULL, 'Iowa', 'IA');
27: INSERT INTO states VALUES (NULL, 'Kansas', 'KS');
28: INSERT INTO states VALUES (NULL, 'Kentucky', 'KY');
29: INSERT INTO states VALUES (NULL, 'Louisiana', 'LA');
30: INSERT INTO states VALUES (NULL, 'Maine', 'ME');
31: INSERT INTO states VALUES (NULL, 'Marshall Islands', 'MH');
32: INSERT INTO states VALUES (NULL, 'Maryland', 'MD');
33: INSERT INTO states VALUES (NULL, 'Massachusetts', 'MA');
34: INSERT INTO states VALUES (NULL, 'Michigan', 'MI');
35: INSERT INTO states VALUES (NULL, 'Minnesota', 'MN');
36: INSERT INTO states VALUES (NULL, 'Mississippi', 'MS');
37: INSERT INTO states VALUES (NULL, 'Missouri', 'MO');
38: INSERT INTO states VALUES (NULL, 'Montana', 'MT');
39: INSERT INTO states VALUES (NULL, 'Nebraska', 'NE');
40: INSERT INTO states VALUES (NULL, 'Nevada', 'NV');
41: INSERT INTO states VALUES (NULL, 'New Hampshire', 'NH');
42: INSERT INTO states VALUES (NULL, 'New Jersey', 'NJ');
43: INSERT INTO states VALUES (NULL, 'New Mexico', 'NM');
44: INSERT INTO states VALUES (NULL, 'New York', 'NY');
45: INSERT INTO states VALUES (NULL, 'North Carolina', 'NC');
46: INSERT INTO states VALUES (NULL, 'North Dakota', 'ND');
47: INSERT INTO states VALUES (NULL, 'Northern Mariana Islands', 'MP');
48: INSERT INTO states VALUES (NULL, 'Ohio', 'OH');
49: INSERT INTO states VALUES (NULL, 'Oklahoma', 'OK');
50: INSERT INTO states VALUES (NULL, 'Oregon', 'OR');
51: INSERT INTO states VALUES (NULL, 'Palau', 'PW');
52: INSERT INTO states VALUES (NULL, 'Pennsylvania', 'PA');
53: INSERT INTO states VALUES (NULL, 'Puerto Rico', 'PR');
54: INSERT INTO states VALUES (NULL, 'Rhode Island', 'RI');
55: INSERT INTO states VALUES (NULL, 'South Carolina', 'SC');
56: INSERT INTO states VALUES (NULL, 'South Dakota', 'SD');
57: INSERT INTO states VALUES (NULL, 'Tennessee', 'TN');
58: INSERT INTO states VALUES (NULL, 'Texas', 'TX');
59: INSERT INTO states VALUES (NULL, 'Utah', 'UT');
60: INSERT INTO states VALUES (NULL, 'Vermont', 'VT');
61: INSERT INTO states VALUES (NULL, 'Virgin Islands', 'VI');
62: INSERT INTO states VALUES (NULL, 'Virginia', 'VA');
63: INSERT INTO states VALUES (NULL, 'Washington', 'WA');
64: INSERT INTO states VALUES (NULL, 'West Virginia', 'WV');
65: INSERT INTO states VALUES (NULL, 'Wisconsin', 'WI');
66: INSERT INTO states VALUES (NULL, 'Wyoming', 'WY');
67: INSERT INTO states VALUES (NULL, 'Armed Forces Africa', 'AE');
68: INSERT INTO states VALUES (NULL, 'Armed Forces Americas (except Canada)', 'AA');
69: INSERT INTO states VALUES (NULL, 'Armed Forces Canada', 'AE');
70: INSERT INTO states VALUES (NULL, 'Armed Forces Europe', 'AE');
71: INSERT INTO states VALUES (NULL, 'Armed Forces Middle East', 'AE');
72: INSERT INTO states VALUES (NULL, 'Armed Forces Pacific', 'AP');
73:
74: If you need Canada provinces as well:
75:
76: INSERT INTO states VALUES (NULL, 'Alberta', 'AB');
77: INSERT INTO states VALUES (NULL, 'British Columbia', 'BC');
78: INSERT INTO states VALUES (NULL, 'Manitoba', 'MB');
79: INSERT INTO states VALUES (NULL, 'New Brunswick', 'NB');
80: INSERT INTO states VALUES (NULL, 'Newfoundland and Labrador', 'NL');
81: INSERT INTO states VALUES (NULL, 'Northwest Territories', 'NT');
82: INSERT INTO states VALUES (NULL, 'Nova Scotia', 'NS');
83: INSERT INTO states VALUES (NULL, 'Nunavut', 'NU');
84: INSERT INTO states VALUES (NULL, 'Ontario', 'ON');
85: INSERT INTO states VALUES (NULL, 'Prince Edward Island', 'PE');
86: INSERT INTO states VALUES (NULL, 'Quebec', 'QC');
87: INSERT INTO states VALUES (NULL, 'Saskatchewan', 'SK');
88: INSERT INTO states VALUES (NULL, 'Yukon', 'YT');
Questions or Comments?