Fast translation of large lists on the example of regions of the world

It took me to translate the list of regions of the world into Russian. Similar lists with ISO codes are embedded in many CMS and online stores, and their translation by hand is a very ungrateful task. I didn’t find anything like this in Russian, but it wouldn’t help much, since it is necessary to compare the names in English with the names in Russian. As a result, a simple idea was born to quickly translate such lists, which I want to share - all of a sudden someone will come in handy too.

I translated the list of regions for OpenCart using Wikipedia and the simplest script:

$db = mysql_connect('localhost', 'username', 'password'); mysql_select_db('database', $db); mysql_set_charset('utf8', $db); $result = mysql_query("SELECT zone_id, name FROM zone GROUP BY zone_id ASC"); //    . while ($row = mysql_fetch_assoc($result)) { $page = @file_get_contents('http://en.wikipedia.org/wiki/'.str_replace(" ", "_", $row['name'])); //    . preg_match("/<a href=\"\/\/ru\.wikipedia\.org\/wiki\/(.*)\" title/", $page, $matches); //         . if (!empty($matches)) { mysql_query("UPDATE zone SET name = '".$row['name']."::".urldecode(str_replace("_", " ", $matches[1]))."' WHERE zone_id = '".$row['zone_id']."'"); //  ,     . } else { mysql_query("UPDATE zone SET name = '".$row['name']."' WHERE zone_id = '".$row['zone_id']."'"); //   ,  . } } 

The script simply asks for Wikipedia, substituting the English name in the link, parses the page for the presence of a link to the Russian version and takes the translation from it. The trick is that on Wikipedia, almost all links contain the same name as in the text. Even such a horror.

Nuances:

• Somewhere one third of the list remained untranslated, but it strongly depends on how much the English names coincide with those listed on Wikipedia.
• The translated list will have to be cleaned up a little by hand. For example, in my case, it was necessary to remove the names of countries in brackets or all there "(values)", "(city)", "(province)", etc.
• Wikipedia replaces spaces with underscores, so you need to transfer them back and forth (available in the script). But I’m not sure that there are any other substitutions happening - it’s worth considering this moment.
• The captain reminds that the larger the list, the longer it will be processed. For example, I had a list of 4000+ zones, and I ran it through the browser, restricting the query to 1000 lines. But it is better in such cases not to run through the browser.

Once again, this is just an example. Theoretically, this solution can be used to translate any lists via Wikipedia from any language to any. Perhaps this task has other options for searching and parsing the data, but I did not invent anything simpler.