}
$country_ids = [];
foreach (db_geonames()->select('code', 'geoname_id')->from('geo_country')->get_2d() as $code => $id) {
$id && ($country_ids[$code] = $id);
}
$region_ids = [];
foreach (db_geonames()->select('code', 'geoname_id')->from('geo_admin1')->get_2d() as $code => $id) {
$id && ($region_ids[$code] = $id);
}
if ($lang) {
$sql = '
SELECT g.id, a.name, g.name AS name_eng, g.country, g.latitude, g.longitude, g.admin1, g.population
FROM geo_geoname AS g
LEFT JOIN geo_alternate_name AS a ON a.geoname_id = g.id
WHERE
g.feature_class = "p"
AND g.population > 10000
AND a.language_code = "' . _es($lang) . '"
GROUP BY g.id
ORDER BY g.country, a.name COLLATE utf8_unicode_ci
';
}
$to_update = [];
foreach (db_geonames()->get_all($sql) as $a) {
$to_update[$a['id']] = ['id' => $a['id'], 'country' => $a['country'], 'name' => $a['name'], 'name_eng' => $a['name_eng'], 'population' => $a['population'], 'lat' => todecimal($a['latitude'], 6), 'lon' => todecimal($a['longitude'], 6), 'region_id' => $region_ids[$a['country'] . '.' . $a['admin1']]];
}
db()->replace_safe($table, $to_update);
db()->query('DELETE FROM ' . $table . ' WHERE country != "ua"') or print_r(db()->error());
db()->update($table, ['active' => 1], 'country = "ua"');
echo 'Trying to get 2 first records: ' . PHP_EOL;
print_r(db()->get_all('SELECT * FROM ' . $table . ' LIMIT 2'));
#!/usr/bin/php
<?php
require_once dirname(__DIR__) . '/scripts_init.php';
$table = DB_PREFIX . 'geo_countries';
$capital_ids = [];
foreach (db_geonames()->from('geo_geoname')->where('feature_code', '=', 'pplc')->get_all() as $a) {
$capital_ids[$a['country']] = $a['id'];
}
$to_update = [];
foreach (db_geonames()->from('geo_country')->get_all() as $a) {
$to_update[$a['code']] = ['code' => $a['code'], 'cont' => $a['continent'], 'tld' => substr($a['tld'], 1), 'currency' => $a['currency'], 'area' => $a['area'], 'population' => $a['population'], 'phone_prefix' => $a['phone_prefix'], 'languages' => $a['languages'], 'geoname_id' => $a['geoname_id'], 'capital_id' => $capital_ids[$a['code']]];
}
db()->update_batch_safe($table, $to_update, 'code');
echo 'Trying to get 2 first records: ' . PHP_EOL;
print_r(db()->get_all('SELECT * FROM ' . $table . ' LIMIT 2'));
