8 Replies Latest reply on May 28, 2009 2:31 PM by davella

    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

        • 1. Re: Trouble with city, state in Mysql Query
          Dan Bracuk Level 5

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

          • 2. Re: Trouble with city, state in Mysql Query
            davella Level 1

            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!

            • 3. Re: Trouble with city, state in Mysql Query
              ilssac Level 5

              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.

              • 4. Re: Trouble with city, state in Mysql Query
                -==cfSearching==- Level 4

                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

                • 5. Re: Trouble with city, state in Mysql Query
                  davella Level 1

                  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

                  • 6. Re: Trouble with city, state in Mysql Query
                    davella Level 1

                    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

                    • 7. Re: Trouble with city, state in Mysql Query
                      ilssac Level 5

                      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.

                      • 8. Re: Trouble with city, state in Mysql Query
                        davella Level 1

                        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!