Known Participant

Question

Trouble with city, state in Mysql Query

Forum|Forum|16 years ago
May 28, 2009
3 replies
1379 views

Hello,

I am having trouble with a query I am trying to setup. Basically, I want the user to type in their city. Then I want the system to ask them which city,state combo is theirs. So if they enter "Washington" the system will return:

Select your city/state:

Washington, NJ

Washington, PA

Washington, DE

City and state are stored as two fields in my mysql DB. I was considering using 'select distinct' but I wasnt sure how it would work in relation to two fields. So how would I go about making sure washington, nj only appears once (its in a database where there will be multiple washington, NJ listed).

So anyone have any ideas or code samples they have used to do something like this?

Thanks in advance,

Mark

Advanced techniques

This topic has been closed for replies.

-

-__cfSearching__-

Inspiring

davella wrote:
City and state are stored as two fields in my mysql DB. I was considering using 'select distinct' but I wasnt sure how it would work in relation to two fields. So how would I go about making sure washington, nj only appears once (its in a database where there will be multiple washington, NJ listed).

DISTINCT works the same way for single field or multiple fields. It returns all distinct combinations of the values in the columns you have listed.

SELECT DISTINCT City, State

FROM Table

WHERE City = 'Washington'

(Data) City, State

Washington, NJ

Washington, PA

Washington, NJ

Washington, DE

Washington, NJ

Washington, PA

So for your two columns it would distinct combinations of the "City" and "State" values. But why are there multiple "Washington, NJ" records? Are they actually different cities or duplicate records?

(Results)

Washington, NJ

Washington, PA

Washington, DE

D

davellaAuthor

Known Participant

To clarify why there are multiple city state combos. Lets say I was mapping

out all of the McDonalds on the east coast. This would consist of a lot of

New York, NY entries in the Database because there are hundreds of mcdonals

in ny. Each row in the db consists of more information then just the city

and state, in this example it would hold infromation like phone number,

business name, email, etc etc

Is there any easier way to do this? Is my data being redudent?

Thanks

ilssac

Inspiring

davella wrote:
Is there any easier way to do this?

Easier to maintain complex data, yes.

Is my data being redudent?
Thanks

Yes it is redundant. Imagine a future where the Dutch finally return and reclaim their colony rechristened by the English as "New York", restoring it to its original name of "New Amsterdam". How many records for McDonald's in your database would you have to go and change?

A more normalized database design would be to have a "Cities" table that the main table would reference with a foreign key. Then all these McDonald's would just have the value of the index key for the "New York" record in their 'CityKey" field. Then one change in the city table to modify the "New York" record to "New Amsterdam" and you are covered if the Dutch every get imperial again.

With this type of data it can be balancing act between normalization and simplicity. And you really have to look at your applications needs and potential growth to decide if the increase in complexity warrants the benefits of simplicity in future maintenance.

ilssac

Inspiring

SELECT DISTINCT city, state.

Will return one and only one record for each unique combination of city and state in the specified table filtered by the specified where clause.

Most likely what you are looking for.

But your question does scream of a database design that may need some normalization. If you have multiple city and states in your database and you have need for this to be shown in such a manner, you database could possible benefit from a city & state table that normalizes this data.