CODEWITHSUNDEEP
mysqlselectdrop-down-menu
Bobby
Bobby

Reputation: 4502

mysql populating multiple select drop down

I'm trying to populate a few select drop down lists from mysql, but each drop down is from a different table. When I do the following:

$data_sql = mysql_query("SELECT * FROM countries");
while($data = mysql_fetch_assoc($data_sql)){
  $countrylist .= '<option value="'.$data[country_iso].'">'.$data[country].'</option>';
}

All Works fine. But when I get into adding more tables as follows:

$data_sql = mysql_query("SELECT * FROM countries, states");
while($data = mysql_fetch_assoc($data_sql)){
  $countrylist .= '<option value="'.$data[country_iso].'">'.$data[country].'</option>';
  $statelist .= '<option value="'.$data[state_iso].'">'.$data[state].'</option>';
}

My drop downs work but have each row repeated 3 times.

Upvotes: 0

Views: 1191

Answers (1)

Wrikken
Wrikken

Reputation: 70460

You stumbled on a implicit JOIN, while you want a UNION

SELECT * FROM countries
UNION 
SELECT * states

What your current query (SELECT * FROM countries, states) does, is the following: For every row in countries, combine the data from this country with all states.

This would mean that if you have 10 countries, and 10 states, the total number of rows would be 100. Count yourself lucky you did a cartesian join on a very small table :)

You just want to do 2 queries separately: trying to combine the results isn't nearly as effective as 2 separate ones.

Upvotes: 0

Related Questions