Hi, I'm sure one of you has come across this problem sometime in your programming career. It seems to be an absolute pain trying to find anything on the net though. Basically I'm after 3 tables containing all the country/state/city data in the world: countries (country_id, country_name) states (state_id, state_name, country_id) major cities (city_id, city_name, country_id, state_id) Which I would then put into 3 combo boxes and have them dependent on each other: The user selects a country, I use ajax to grab the states for that country. User selects a state, I use ajax to populate the city combo box with the cities in the state. User selects the city, then submits the form. Ok so I've got a rough idea what the code will look like from here however I'm missing all the data to make this happen. So far I've got a list of all the timezones and countries with international dialing code prefixes i.e. +61 for Australia (I had to format myself from some pdf file). Some sites have a list of the countries and cities, but no state data. Is there a free web service you can use to query for this information? From my readings so far it looks like a database doing this could be upwards of 80MB. Could slow down my web server quite a bit... I'm using PHP and MySQL by the way. Many thanks!
Googling "free world city state country database" brings up this page, will that do what you are after? You can easily delete the unnecessary columns. Are you worried about server storage space or the speed of the queries? The queries will be instant on a database containing this information, you will not have to worry about lag. 80MB may seem like a big database but it is peanuts so don't worry about it. If you don't have the storage to put the database on your own server, I think you are going to have a lot more headaches trying to find a free online service. And then you have to put up with the problem that if the online service goes down, so does your website. Much better to have a local copy of this IMO, considering how static the data is.
Thanks Mikos! I'm kinda getting there with the data from that site. So far I think the matching pattern between the data is the 2 digit country code and the region code. You can use that to link the country, region and city data together. I've got the country table into the database by importing the csv file with SQLYog. Then the region table I had to manually parse the file with PHP and insert the rows from there because it wouldn't accept the region code for some reason. The code is 2chars and ranges from 00 - 99 and AA - ZZ but SQL just sets the cell to null if it encounters the code with letters. I've set the column type to varchar but it doesn't like it either. For the cities file I imported that in and put a where clause on the population to be > 40000 so I'm only getting the main cities. Cut the database right down to 8700 rows (252KB) instead of 2.3million rows (130MB). Also some of the region codes are null in this one so it looks like I'll have to parse the big file with PHP. Hell of a mission I tell you.
Well finally sorted it out after two days of importing/parsing the data. Got all the countries, regions/states and cities with population > 45K in the database, now in relational format. Database schema looks like this: SQL file here if anyone needs it. Query all the data like so: Code: SELECT countries.country_id, country_code, country_name, country_call_code, regions.region_id, region_name, city_id, city_name FROM countries INNER JOIN regions ON regions.country_id = countries.country_id INNER JOIN cities ON cities.region_id = regions.region_id Very quick to query, only 7600 rows.
