Trouble with city, state in Mysql Query

[davella]

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

[Dan_Bracuk]

where city = 'Washinton' and state = 'NJ'

[davella]

Im sorry I forgot to mention, there is no input for state.  So I need to automatically know how to display each 'washington' from each state.

Thanks!

[ilssac]

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.

[davella]

Thanks, I will test this out as soon as I am done with work. My database

consists entirely of zip codes, lat, long, city, state, etc. (its a list of

business locations) I want to make it so the user will generally search

city,state but in the event they just put city I want the db to be able to

handle that request as well. This is sort of a backup plan for people who

don't follow the search rules.

Does this make sense? Does this provide a heavier burden on the Db then

need be?

Thanks again

[-__cfSearching__-]

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

[davella]

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]

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.

[davella]

I definitely see your point. I have been using this method purely out of

ignorance. I will look into switching over to using a foreign key to

reference and plan on switching over when I have time to implement such a

task. Right now my application is used on a very small scale. Ideally, I

would hope its use will grow so I am confident the switch will be

inevitable. I unfortunately learned how to get things to work, not

necessarily to work correctly or efficiently. But im starting to learn now!

Thanks again!