MySQL List of States & PHP Function for Select Box

After the first few 5:00am “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.

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 textbox 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(50) NOT NULL default '' COMMENT 'State Name',
  `state_abbr` varchar(10) default NULL COMMENT 'State Abbreviation',
  PRIMARY KEY  (`state_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=55 ;

--
-- 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, 'Rhode Island', 'RI'),
(41, 'South Carolina', 'SC'),
(42, 'South Dakota', 'SD'),
(43, 'Tennessee', 'TN'),
(44, 'Texas', 'TX'),
(45, 'Utah', 'UT'),
(46, 'Vermont', 'VT'),
(47, 'Virginia', 'VA'),
(48, 'Washington', 'WA'),
(49, 'West Virginia', 'WV'),
(50, 'Wisconsin', 'WI'),
(51, 'Wyoming', 'WY'),
(52, 'Armed Forces - Americas', 'AA'),
(53, 'Armed Forces - Europe', 'AE'),
(54, 'Armed Forces - Pacific', 'AP');

Create variables and an array as below. You may wish to change the variable names for security.

$dbname	= "mydatabase_name";        // Database name
$dbuser	= "my_username";            // Database username
$dbpass	= "password";               // Database password
$dbhost	= "localhost";              // Database host

$dbvariables = Array (              // Array to hold db variables
		"dbhost" => $dbhost,
		"dbuser" => $dbuser,
		"dbpass" => $dbpass,
		"dbname" => $dbname
		);

Place the following function in an appropriate location.

function state_select($dbvariables) {
?>
<select name="state">
<option value="">--</option>
<?php
   dbConnect($dbvariables);                   // Connect to MySQL
   mysql_select_db($dbvariables['dbname']);   // Select database

   $query = 'SELECT * from `state_t` WHERE 1 ORDER BY `state_name` ASC';
   $result = mysql_query($query) or die(mysql_error());

   while ($x = mysql_fetch_array($result, MYSQL_ASSOC)) {

      $state_id   = $x['state_id'];
      $state_name = $x['state_name'];
?>
<option value="<?php echo $state_id; ?>"><?php echo $state_name; ?></option>
<?php
	}
?>
</select>
<?php
}

Search Help: Visitors seeking this information might use the following search strings: List of states in MySQL database format, MySQL List of States, MySQL state list, php MySQL list of states, php list state function.