Database-driven version of United States dropdown data

Some time ago I created a brief blog entry that shows an HTML select/option block for displaying all the states in the United States in a dropdown list (combo box). After a few requests I'm now providing that same information here in a database table format.

Specifically, the next block of (free) sample code can be used to create a states database table in a MySQL database:

CREATE TABLE states (
  id int unsigned auto_increment not null,
  abbreviation char(2) not null,
  full_name varchar(20) not null,
  primary key(id)
) ENGINE=InnoDB;

(That ENGINE=InnoDB code may not be required for all MySQL databases. I've just been working with a MySQL Cluster lately, and it is needed there.)

After you're created your database table, the following block of code contains all the SQL INSERT statements you need to populate that database table. Note that these statements are less database-specific than the CREATE TABLE syntax shown above, so these should work with any database.

INSERT INTO states (abbreviation, full_name) VALUES ('AL', 'Alabama');
INSERT INTO states (abbreviation, full_name) VALUES ('AK', 'Alaska');
INSERT INTO states (abbreviation, full_name) VALUES ('AZ', 'Arizona');
INSERT INTO states (abbreviation, full_name) VALUES ('AR', 'Arkansas');
INSERT INTO states (abbreviation, full_name) VALUES ('CA', 'California');
INSERT INTO states (abbreviation, full_name) VALUES ('CO', 'Colorado');
INSERT INTO states (abbreviation, full_name) VALUES ('CT', 'Connecticut');
INSERT INTO states (abbreviation, full_name) VALUES ('DE', 'Delaware');
INSERT INTO states (abbreviation, full_name) VALUES ('DC', 'District of Columbia');
INSERT INTO states (abbreviation, full_name) VALUES ('FL', 'Florida');
INSERT INTO states (abbreviation, full_name) VALUES ('GA', 'Georgia');
INSERT INTO states (abbreviation, full_name) VALUES ('HI', 'Hawaii');
INSERT INTO states (abbreviation, full_name) VALUES ('ID', 'Idaho');
INSERT INTO states (abbreviation, full_name) VALUES ('IL', 'Illinois');
INSERT INTO states (abbreviation, full_name) VALUES ('IN', 'Indiana');
INSERT INTO states (abbreviation, full_name) VALUES ('IA', 'Iowa');
INSERT INTO states (abbreviation, full_name) VALUES ('KS', 'Kansas');
INSERT INTO states (abbreviation, full_name) VALUES ('KY', 'Kentucky');
INSERT INTO states (abbreviation, full_name) VALUES ('LA', 'Louisiana');
INSERT INTO states (abbreviation, full_name) VALUES ('ME', 'Maine');
INSERT INTO states (abbreviation, full_name) VALUES ('MD', 'Maryland');
INSERT INTO states (abbreviation, full_name) VALUES ('MA', 'Massachusetts');
INSERT INTO states (abbreviation, full_name) VALUES ('MI', 'Michigan');
INSERT INTO states (abbreviation, full_name) VALUES ('MN', 'Minnesota');
INSERT INTO states (abbreviation, full_name) VALUES ('MS', 'Mississippi');
INSERT INTO states (abbreviation, full_name) VALUES ('MO', 'Missouri');
INSERT INTO states (abbreviation, full_name) VALUES ('MT', 'Montana');
INSERT INTO states (abbreviation, full_name) VALUES ('NE', 'Nebraska');
INSERT INTO states (abbreviation, full_name) VALUES ('NV', 'Nevada');
INSERT INTO states (abbreviation, full_name) VALUES ('NH', 'New Hampshire');
INSERT INTO states (abbreviation, full_name) VALUES ('NJ', 'New Jersey');
INSERT INTO states (abbreviation, full_name) VALUES ('NM', 'New Mexico');
INSERT INTO states (abbreviation, full_name) VALUES ('NY', 'New York');
INSERT INTO states (abbreviation, full_name) VALUES ('NC', 'North Carolina');
INSERT INTO states (abbreviation, full_name) VALUES ('ND', 'North Dakota');
INSERT INTO states (abbreviation, full_name) VALUES ('OH', 'Ohio');
INSERT INTO states (abbreviation, full_name) VALUES ('OK', 'Oklahoma');
INSERT INTO states (abbreviation, full_name) VALUES ('OR', 'Oregon');
INSERT INTO states (abbreviation, full_name) VALUES ('PA', 'Pennsylvania');
INSERT INTO states (abbreviation, full_name) VALUES ('RI', 'Rhode Island');
INSERT INTO states (abbreviation, full_name) VALUES ('SC', 'South Carolina');
INSERT INTO states (abbreviation, full_name) VALUES ('SD', 'South Dakota');
INSERT INTO states (abbreviation, full_name) VALUES ('TN', 'Tennessee');
INSERT INTO states (abbreviation, full_name) VALUES ('TX', 'Texas');
INSERT INTO states (abbreviation, full_name) VALUES ('UT', 'Utah');
INSERT INTO states (abbreviation, full_name) VALUES ('VT', 'Vermont');
INSERT INTO states (abbreviation, full_name) VALUES ('VA', 'Virginia');
INSERT INTO states (abbreviation, full_name) VALUES ('WA', 'Washington');
INSERT INTO states (abbreviation, full_name) VALUES ('WV', 'West Virginia');
INSERT INTO states (abbreviation, full_name) VALUES ('WI', 'Wisconsin');
INSERT INTO states (abbreviation, full_name) VALUES ('WY', 'Wyoming');

Of course you can modify the CREATE TABLE syntax shown above to work with your database of choice. I just chose MySQL because I've been working with it a lot lately. Once you've created your database table you can easily populate your own HTML SELECT/OPTION widget, just like I showed in my earlier example. The only difference is that you'll get the information out of the database, instead of hard-coding it like that example showed.

Finally, here's a SQL query that you can use to retrieve these records from the database:

SELECT abbreviation, full_name FROM states;

Whether you're using Java/JSP, JSF, Struts, PHP, Rails on the front end and MySQL, PostgreSQL, or any other database on the backend, that simple SQL statement should work just fine.