So today, I was buzzing around and searching for a `countries` table script that I could use in MySQL database. As you can imagine, it wouldn’t make any sense to add all world countries into database table one-by-one.
I’ve found few interesting things around there, but all of those solutions were not flexible and were created for individual projects – and when I say that, I mean that some of those tables have only two columns (like country code and country name), some of those have 3 columns, and some of those have bunch of columns (that are probably not useful to many developers).
Anyway, after checking some of those solutions I came to idea that I could make something on my own – something that will be flexible and customizable. And the result was GetCountries “program” where you can select which columns do you want, click “Get it!” and the program will fetch “fresh” results from Geonames API and will generate you a SQL script. After that you can copy/paste that script and eventually change column names.
Hope someone will find that useful.
GetCountries app | code @ Github
Related articles
- Handy PHP and MySQL Queries for Developers (dzone.com)
- Trying to translate a MySQL query into a PHP query (stackoverflow.com)
- MySQL ordering results by specific field values (techtalk.virendrachandak.com)
- Open Source Wars (arnoldit.com)
echo behind; 
18 replies on “Countries database table for MySQL”
I had to do this just the other day. I found many of the official names on the ISO site (http://www.iso.org/iso/country_codes.htm) to be overly wordy and had to cut them down to their generally used names. It appears that your source already did that.
uhh.. Not sure why you would want to extract and store the data in MySQL table. Check out http://cldr.unicode.org .. the data for most applications is provided in XML files
Suppose that some developers are using MySQL, some XML, and even some other solution for that things. To be true, I’m still thinking what will I use for my next app – I’ll try to search around for a little bit.
That’s brilliant! Definitively a resource to save for later use. :)
Next: Timezones? https://gist.github.com/373168#file_timezones.js
Why some languages are defined in en-LR way, and others are in en,ru,pl way? it would be good to have consistency. anyway, good stuff!
This depends on geonames so I really don’t have answer for that, sorry.
avrudoi@yandex.ru free if it came to me it is also
Phone codes would be useful as well. Great job!
What are the north, south, east, and west fields?
Geonames says:
“north,south,east,west : coordinates of bounding box”
http://www.geonames.org/export/JSON-webservices.html
Hey dperitch – I built an API based off the data I pulled from your tool! See: http://www.emergentone.com/blog/world-countries-and-timezones-api/
Thanks for the great post.
Wow, really nice hack…glad that this has inspired you :)
Hi there I am so happy I found your webpage, I really found you
by error, while I was searching on Aol for something else, Anyhow I
am here now and would just like to say thanks for a remarkable post and
a all round interesting blog (I also love the theme/design), I don’t have time
to read through it all at the moment but I have book-marked it and also included
your RSS feeds, so when I have time I will be back to read a lot more, Please do
keep up the great work.
You are massively helpful…
ty so much dude , you save me a lot of time.
Thank you for this, would love to have the SQL dump for world cities too. That’d be amazing :-)
Thank you very much for this script. I have to complete this job in like 30 min and could never accomplish that without your script …. You just made my day a lot better ..
Raj