After the first few 5:00 a.m. end-of-the-night “ohmigod I need a list of states NOW!” moments that I had when I started coding, I figured I’d dump a generic table and use that for future reference. This is the basic table–it can be modified as needed. It includes Washington DC and the three Armed Forces abbreviations: AA, AE, and AP, as well as Puerto Rico.
Copy and paste the contents of this box either into your SQL command window if you’re using the command line or into the big text box on the “SQL” tab in phpMyAdmin.
CREATE TABLE IF NOT EXISTS `state_t` (
`state_id` int(3) NOT NULL auto_increment COMMENT 'PK: Unique Identifier',
`state_name` varchar(30) NOT NULL default '' COMMENT 'State Name',
`state_abbr` varchar(3) default NULL COMMENT 'State Abbreviation',
PRIMARY KEY (`state_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
--
-- Dumping data for table `state_t`
--
INSERT INTO `state_t` (`state_id`, `state_name`, `state_abbr`) VALUES
(1, 'Alabama', 'AL'),
(2, 'Alaska', 'AK'),
(3, 'Arizona', 'AZ'),
(4, 'Arkansas', 'AR'),
(5, 'California', 'CA'),
(6, 'Colorado', 'CO'),
(7, 'Connecticut', 'CT'),
(8, 'Delaware', 'DE'),
(9, 'District of Columbia', 'DC'),
(10, 'Florida', 'FL'),
(11, 'Georgia', 'GA'),
(12, 'Hawaii', 'HI'),
(13, 'Idaho', 'ID'),
(14, 'Illinois', 'IL'),
(15, 'Indiana', 'IN'),
(16, 'Iowa', 'IA'),
(17, 'Kansas', 'KS'),
(18, 'Kentucky', 'KY'),
(19, 'Louisiana', 'LA'),
(20, 'Maine', 'ME'),
(21, 'Maryland', 'MD'),
(22, 'Massachusetts', 'MA'),
(23, 'Michigan', 'MI'),
(24, 'Minnesota', 'MN'),
(25, 'Mississippi', 'MS'),
(26, 'Missouri', 'MO'),
(27, 'Montana', 'MT'),
(28, 'Nebraska', 'NE'),
(29, 'Nevada', 'NV'),
(30, 'New Hampshire', 'NH'),
(31, 'New Jersey', 'NJ'),
(32, 'New Mexico', 'NM'),
(33, 'New York', 'NY'),
(34, 'North Carolina', 'NC'),
(35, 'North Dakota', 'ND'),
(36, 'Ohio', 'OH'),
(37, 'Oklahoma', 'OK'),
(38, 'Oregon', 'OR'),
(39, 'Pennsylvania', 'PA'),
(40, 'Puerto Rico', 'PR'),
(41, 'Rhode Island', 'RI'),
(42, 'South Carolina', 'SC'),
(43, 'South Dakota', 'SD'),
(44, 'Tennessee', 'TN'),
(45, 'Texas', 'TX'),
(46, 'Utah', 'UT'),
(47, 'Vermont', 'VT'),
(48, 'Virginia', 'VA'),
(49, 'Washington', 'WA'),
(50, 'West Virginia', 'WV'),
(51, 'Wisconsin', 'WI'),
(52, 'Wyoming', 'WY'),
(53, 'Armed Forces - Americas', 'AA'),
(54, 'Armed Forces - Europe', 'AE'),
(55, 'Armed Forces - Pacific', 'AP');
I’m going to assume, at this point, that you already have a connection to your database, and that it’s named $conn. If it isn’t named $conn, you should change the following function accordingly. This uses PDO, but doesn’t use a prepared statement, because you (should) have full control over the data going into the query. Note that I assign the individual array elements from the fetch to variables because I prefer easy-to-read code. You can adjust this as necessary.
function stateSelectBox($conn) {
echo "<select name='state'>\n";
echo "<option value=''>--<option>\n";
$result = $conn->query('SELECT * FROM `state_t` WHERE 1 ORDER BY `state_name` ASC');
while ($x = $result->fetch()) {
$stateID = $x['state_id'];
$stateName = $x['state_name'];
echo "<option value='$stateID'>$stateName</option>\n";
}
echo "</select>\n";
}