Generate <select> from MYSQL with PHP "USA States Drop Down"

Information for PHP. Hypertext Preprocessor
Post Reply
dzcadii
Posts: 94
Joined: Sun Mar 01, 2015 4:36 am

Generate <select> from MYSQL with PHP "USA States Drop Down"

Post by dzcadii » Sun Mar 01, 2015 6:25 pm

Simple way to generate a drop down (<select>) from MYSQL using PHP. In this example I am grabbing State. This way I wouldn't have to sit there and type all of them out every time I needed them.

//This is how I have the Database table set up. You will have to customize your connection settings.
//Simply copy this into your MYSQL "Query" window and execute it to generate all of the states in the USA

Code: Select all

-- --------------------------------------------------------

-- 
-- Table structure for table `states`
-- 

DROP TABLE IF EXISTS `states`;
CREATE TABLE IF NOT EXISTS `states` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `state` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=52 ;

INSERT INTO `states` (`id`, `state`) VALUES 
(1, 'Alabama'),
(2, 'Alaska'),
(3, 'Arizona'),
(4, 'Arkansas'),
(5, 'California'),
(6, 'Colorado'),
(7, 'Connecticut'),
(8, 'Delaware'),
(9, 'District of Columbia'),
(10, 'Florida'),
(11, 'Georgia'),
(12, 'Hawaii'),
(13, 'Idaho'),
(14, 'Illinois'),
(15, 'Indiana'),
(16, 'Iowa'),
(17, 'Kansas'),
(18, 'Kentucky'),
(19, 'Louisiana'),
(20, 'Maine'),
(21, 'Maryland'),
(22, 'Massachusetts'),
(23, 'Michigan'),
(24, 'Minnesota'),
(25, 'Mississippi'),
(26, 'Missouri'),
(27, 'Montana'),
(28, 'Nebraska'),
(29, 'Nevada'),
(30, 'New Hampshire'),
(31, 'New Jersey'),
(32, 'New Mexico'),
(33, 'New York'),
(34, 'North Carolina'),
(35, 'North Dakota'),
(36, 'Ohio'),
(37, 'Oklahoma'),
(38, 'Oregon'),
(39, 'Pennsylvania'),
(40, 'Rhode Island'),
(41, 'South Carolina'),
(42, 'South Dakota'),
(43, 'Tennessee'),
(44, 'Texas'),
(45, 'Utah'),
(46, 'Vermont'),
(47, 'Virginia'),
(48, 'Washington'),
(49, 'West Virginia'),
(50, 'Wisconsin'),
(51, 'Wyoming');


//Now the function to pull create the <select>

//Link to your database right here
<?
$States=mysql_query("SELECT * FROM states ORDER BY state ASC");
			echo "<select name="State"><option value="0"></option>";
				while($row=mysql_fetch_assoc($States)){
				extract($row);
				echo "<option value="$state">$state</option>";
				}
			echo "</select>";
?>
Here is an example to use in a form. Lets say if you are allowing a user to update their location information and want their current location selected.

Code: Select all

<?
//Get the current state for the user. You can do this however you need. $_SESSION, $_COOKIE, or a query
$get_current_state = mysql_query("SELECT state FROM users WHERE username='$username'");
$current_state = mysql_result($get_current_state,0);

$States=mysql_query("SELECT * FROM states ORDER BY state ASC");
echo "<select name="State"><option value="0"></option>";
while($row=mysql_fetch_assoc($States)){
if($row['state'] == $current_state)
{
echo "<option value=\"$row['state']\" selected>$row['state']</option>";
}
else
{
echo "<option value=\"$row['state']\">$row['state']</option>";
}
}
echo "</select>";
?>

Post Reply