Creating a States Table in MySQL

It is common to add a drop-down select box that is populated with USA states or Canadian provinces to PHP applications. There are basically three ways to do this. You can hard-code all of the Select box selections into your application, you can add the states to a MySQL database manually, or you can use the handy code on this page to create a States table in MySQL in just a few seconds.

The easiest way to do this is to use phpMyAdmin, which can be found in cPanel. phpMyAdmin is easy to use and is the most popular toll to use for setting up or modifying MySQL databases. It is included in all cPanel installations for hosting that includes the MySQL database, but can sometimes be hard to find.

First, look for the MySQL Databases icon. After clicking on that icon, many hosting configurations will take you to a MySQL Account Maintenance page. This is not phpMyAdmin. if you scroll down to the bottom of this page, you will find a link that says, “phpMyAdmin”. Son of a gun. You found it. I have no idea as to the reason for hiding a link to a useful tool like phpMyAdmin.

Once you have entered phpMyAdmin you need to either create a new database or select a database that you would like to add the States table to. Once you have your database created or selected, click on the SQL tab at the top of the phpMyAdmin page. You then need to cut-and-paste the following code in the textbox on the SQL page and click the Go button.


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

This SQL code creates a table named “states” and populates it with the 50 states and the District of Columbia. If you want to add Canadian provinces to the list, cut-and-paste the following in the SQL textbox and click Go once again.


INSERT INTO states VALUES ('AB','Alberta');
INSERT INTO states VALUES ('BC','British Columbia');
INSERT INTO states VALUES ('MB','Manitoba');
INSERT INTO states VALUES ('NL','Newfoundland');
INSERT INTO states VALUES ('NB','New Brunswick');
INSERT INTO states VALUES ('NS','Nova Scotia');
INSERT INTO states VALUES ('NT','Northwest Territories');
INSERT INTO states VALUES ('NU','Nunavut');
INSERT INTO states VALUES ('ON','Ontario');
INSERT INTO states VALUES ('PE','Prince Edward Island');
INSERT INTO states VALUES ('QC','Quebec');
INSERT INTO states VALUES ('SK','Saskatchewan');
INSERT INTO states VALUES ('YT','Yukon Territory');

Your next question is probably, “How do we use this to create the select box using PHP? That is also easy.

The following PHP code assumes that you have already set up a database connection.


<select name="state">
<option value="0">- state not selected -
<?php
$sql = "SELECT state_code, state_name FROM states ORDER BY state_code";
$rs = mysql_query($sql);
 
while($row = mysql_fetch_array($rs))
{
echo "<option value=\"".$row['state_code']."\">".$row['state_code']." - ".$row['state_name']."\n ";
}
?>
</select>

This block of PHP code reads the states table in the MySQL database and sorts the results by the state_code column. As it iterates through the table rows it produces the following HTML output. The first option tag is set up to let a user know that a selection has not been made. This becomes the default selection with a value of zero. You can test for this value before accepting the user’s selections. If the value is zero, you know that a selection has not been made and the form Submission should be rejected.

– state not selected –AK – AlaskaAL – AlabamaAR – ArkansasAZ – ArizonaCA – CaliforniaCO – ColoradoCT – ConnecticutDC – District of ColumbiaDE – DelawareFL – FloridaGA – GeorgiaHI – HawaiiIA – IowaID – IdahoIL – IllinoisIN – IndianaKS – KansasKY – KentuckyLA – LouisianaMA – MassachusettsMD – MarylandME – MaineMI – MichiganMN – MinnesotaMO – MissouriMS – MississippiMT – MontanaNC – North CarolinaND – North DakotaNE – NebraskaNH – New HampshireNJ – New JerseyNM – New MexicoNV – NevadaNY – New YorkOH – OhioOK – OklahomaOR – OregonPA – PennsylvaniaRI – Rhode IslandSC – South CarolinaSD – South DakotaTN – TennesseeTX – TexasUT – UtahVA – VirginiaVT – VermontWA – WashingtonWI – WisconsinWV – West VirginiaWY – Wyoming