Using DISTINCT

zzz

I have a table with 3 columns:

zip
City
State

Most cities have multiple zip codes. I'd like to create a dropdown list with one city per area.

Say a city has zip codes 10002, 10003 ... 10012. I would like to only list this city once.

Now, I've tried something like this (I use db class so bare with me):

$selectCity = $db->get_results("SELECT zip, city, state FROM t1";

and draw list

echo '<select name=cityList>';	

foreach ($selectCity as $city) {

echo '<option value="">'.$city->city.' -- '.$city->state;
echo '</select>';

I was thinking of using DISTINCT but am not sure how to use it here, since there could be same name city in different state...

Also I'd like to shorten my list by skipping cities with, say only 3 zip codes.

Worked myself into the woods at the moment...

Piranha

zzz wrote:
I was thinking of using DISTINCT but am not sure how to use it here, since there could be same name city in different state...

Basically that shows that you have a design problem in the database. You should probably have a database that is built something like this:

[b]state[/b]
id
name

[b]city[/b]
id
name
stateid

[b]zip[/b]
id
code
cityid

This is in fact part of proper database normalizing. It would solve all the other problems you have in this thread quite easy. Always normalize to at least 3NF (better to go to BCNF) to make sure that you can use the database properly.

zzz

Unfortunatelly I don't have much control over this db design. But I am almost sure it is possible to do something about it, I just don't know how yet.

:rolleyes:

Piranha

Basically no, it is not possible to do anything about it. You can of course check against both city and state, but there may probably be 2 cities with the same name in one state.

SELECT columns 
FROM table 
WHERE something 
GROUP BY city, state
HAVING COUNT(zip) > 3

NogDog

If you don't need the zip, then leave it out and do your distinct thus:

SELECT DISTINCT city, state FROM t1

zzz

Well, I do need zip code as well. I'll give Priranha's query idea a try.