php/mysql append state to city

Having a hard time figuring out the best way to do this...

I have a search function that takes "search terms" and "search location". In the location input, I have an suggestion feature that brings up "city, state abbreviation" but it seems some users just do not use it(or can't) so they end up ent开发者_运维知识库ering just a city name... I need to append the state abbreviation after the form is submitted. I have a table with all city and state names in the U.S. but the problem is... there are multiple cities with the same name in different states... I would like to add the state abbreviation for the state that the city is most popular for(does that make sense?).

For example, if the user enters "Miami" I would like it to become "Miami, FL" as opposed to "Miami, WV"...

Any ideas?

Thanks!

You may want to consider the correlation between the "city population" and its "general popularity".

Therefore one easy option might be to gather the population of each city, store it in the database, and if a city name is used that you need to disambiguate, you can choose the one with the largest population.

The references in the following Wikipedia article might be useful: List of US cities by population.

Google Maps resolves disambiguations in a similar manner, even though the weighting might be different:

http://maps.google.com/?q=Miami

http://maps.google.com/?q=Miami,WV

I used to think Google Maps also takes into consideration the current viewport when resolving such issues, but searching for Miami while zoomed into the West Virginia still takes you to Florida.

I would like to add the state abbreviation for the state that the city is most popular for(does that make sense?).

Nope. That definitely doesn't make sense. What if somebody is from Miami, WV?

I think you'll have either to force the user to select a state, or enter a Zip code that can help you figure out the state later. Wouldn't that make sense anyway? Wouldn't the address be worthless without a zip code?

You'd probably need to have weighting values present in your table of city and state names - then run a query against the city/state name table to select the row with matching city name and highest weight value, and return the state name.

A better option, however, would probably be to run a query, grab all of the possible states, and then ask the user which one is correct - best would be to use AJAX to prompt them before they continue on with the form; but even just a followup page could also work. Trying to fill in the state automagically in this case will probably just lead to confusion for users who happened to mean the not-so-popular version of the city.

Assign a popularity weighting for each of your cities in your city table, and order by the popularity weight when doing your select statement.

Alternatively allow the user to choose which one they mean after they have input the city... Which will definately be the best idea...