How to work with countries and cities in your project?

Question

PHP

I have my own project and I decided to work with countries and cities.
1) How to write city and country identifiers?
The user table has a city and country field, for example, when registering, I connected a service that determines the city and country, well, you can connect geoip or something like that, it doesn’t matter. The problem is this:
For example, it defines me as: RU, Moscow. That is, if it’s convenient to write it into the database on the one hand, you can easily get the flag by the country identifier, on the other hand, if you give the opportunity to edit the profile and the user can enter, for example, Govnoukraina instead of UA or Russia instead of RU, and there will already be discrepancies in the data.
2) Question number two, about the base of countries and cities.I came across such a thing, well, let's say it will not be bad to determine the country and give a drop-down list of countries and cities for selection, but on the other hand, how to make friends with the location determination service with base identifiers.
3) And if the site is multilingual? it needs to be hammered on transfers or to keep some bases? Or keep the name of each country and city in the appropriate language?
In general, please tell me how to implement and work with countries and cities on the site?

Answer 1

1) How to write city and country identifiers?
2 tables. Table of cities (id, city), table of countries (id, country)
2) Question number two, about the base of countries and cities.
Parse from the service where you define the location.
3) And if the site is multilingual?
Returning to the first question, we add the 3rd column with the translation.

Answer 2

you can try to take ready-made databases, such as geonames
, you can fasten multilingualism there quite easily (it even exists at the basic level), there are coordinates of objects, a hierarchy of regions and all that - it's easier to "make friends" with users' geolocation.

Answer 3

if you need to bind to a country by RU, FR, UA, etc., then you determine by what standard the service gives data (wiki to help) - and make an additional column in the country table - isoXXX, isoYYY, standartZZZ. When receiving data, look for a match in the required column.
If the site is multilingual, then translate only the names of countries.