Populating the Zone field in function of telephone prefix

mysql_phoneprefix_to_zone_field.sql

UPDATE telcountry_table
SET country =
   CASE 
    WHEN left(telnum,4) = 0093 THEN 'Afghanistan'
    WHEN left(telnum,5) = 00355 THEN 'Albania'
    WHEN left(telnum,5) = 00213 THEN 'Algeria'
    WHEN left(telnum,5) = 00376 THEN 'Andorra'
    WHEN left(telnum,5) = 00244 THEN 'Angola'
    WHEN left(telnum,6) = 001264 THEN 'Anguilla'
    WHEN left(telnum,6) = 006721 THEN 'Antarctica'
    WHEN left(telnum,4) = 0054 THEN 'Argentina'
    WHEN left(telnum,5) = 00374 THEN 'Armenia'
    WHEN left(telnum,5) = 00297 THEN 'Aruba'
    WHEN left(telnum,4) = 0061 THEN 'Australia'
    WHEN left(telnum,4) = 0043 THEN 'Austria'
    WHEN left(telnum,5) = 00994 THEN 'Azerbaijan'
    WHEN left(telnum,6) = 001242 THEN 'Bahamas'
    WHEN left(telnum,5) = 00973 THEN 'Bahrain'
    WHEN left(telnum,5) = 00880 THEN 'Bangladesh'
    WHEN left(telnum,6) = 001246 THEN 'Barbados'
    WHEN left(telnum,5) = 00375 THEN 'Belarus'
    WHEN left(telnum,4) = 0032 THEN 'Belgium'
    WHEN left(telnum,5) = 00501 THEN 'Belize'
    WHEN left(telnum,5) = 00229 THEN 'Benin'
    WHEN left(telnum,6) = 001441 THEN 'Bermuda'
    WHEN left(telnum,5) = 00975 THEN 'Bhutan'
    WHEN left(telnum,5) = 00591 THEN 'Bolivia'
    WHEN left(telnum,5) = 00267 THEN 'Botswana'
    WHEN left(telnum,4) = 0055 THEN 'Brazil'
    WHEN left(telnum,5) = 00359 THEN 'Bulgaria'
    WHEN left(telnum,5) = 00257 THEN 'Burundi'
    WHEN left(telnum,5) = 00855 THEN 'Cambodia'
    WHEN left(telnum,5) = 00237 THEN 'Cameroon'
    WHEN left(telnum,3) = 001 THEN 'Canada'
    WHEN left(telnum,5) = 00235 THEN 'Chad'
    WHEN left(telnum,4) = 0056 THEN 'Chile'
    WHEN left(telnum,4) = 0086 THEN 'China'
    WHEN left(telnum,4) = 0057 THEN 'Colombia'
    WHEN left(telnum,5) = 00269 THEN 'Comoros'
    WHEN left(telnum,5) = 00242 THEN 'Congo'
    WHEN left(telnum,5) = 00385 THEN 'Croatia'
    WHEN left(telnum,4) = 0053 THEN 'Cuba'
    WHEN left(telnum,5) = 00357 THEN 'Cyprus'
    WHEN left(telnum,4) = 0045 THEN 'Denmark'
    WHEN left(telnum,5) = 00253 THEN 'Djibouti'
    WHEN left(telnum,6) = 001767 THEN 'Dominica'
    WHEN left(telnum,5) = 00593 THEN 'Ecuador'
    WHEN left(telnum,4) = 0020 THEN 'Egypt'
    WHEN left(telnum,5) = 00291 THEN 'Eritrea'
    WHEN left(telnum,5) = 00372 THEN 'Estonia'
    WHEN left(telnum,5) = 00251 THEN 'Ethiopia'
    WHEN left(telnum,5) = 00679 THEN 'Fiji'
    WHEN left(telnum,5) = 00358 THEN 'Finland'
    WHEN left(telnum,4) = 0033 THEN 'France'
    WHEN left(telnum,5) = 00241 THEN 'Gabon'
    WHEN left(telnum,5) = 00220 THEN 'Gambia'
    WHEN left(telnum,5) = 00995 THEN 'Georgia'
    WHEN left(telnum,4) = 0049 THEN 'Germany'
    WHEN left(telnum,5) = 00233 THEN 'Ghana'
    WHEN left(telnum,5) = 00350 THEN 'Gibraltar'
    WHEN left(telnum,4) = 0030 THEN 'Greece'
    WHEN left(telnum,5) = 00299 THEN 'Greenland'
    WHEN left(telnum,6) = 001473 THEN 'Grenada'
    WHEN left(telnum,5) = 00590 THEN 'Guadeloupe'
    WHEN left(telnum,6) = 001671 THEN 'Guam'
    WHEN left(telnum,5) = 00502 THEN 'Guatemala'
    WHEN left(telnum,5) = 00224 THEN 'Guinea'
    WHEN left(telnum,5) = 00592 THEN 'Guyana'
    WHEN left(telnum,5) = 00509 THEN 'Haiti'
    WHEN left(telnum,5) = 00504 THEN 'Honduras'
    WHEN left(telnum,4) = 0036 THEN 'Hungary'
    WHEN left(telnum,5) = 00354 THEN 'Iceland'
    WHEN left(telnum,4) = 0091 THEN 'India'
    WHEN left(telnum,4) = 0062 THEN 'Indonesia'
    WHEN left(telnum,4) = 0098 THEN 'Iran'
    WHEN left(telnum,5) = 00964 THEN 'Iraq'
    WHEN left(telnum,5) = 00353 THEN 'Ireland'
    WHEN left(telnum,5) = 00972 THEN 'Israel'
    WHEN left(telnum,4) = 0039 THEN 'Italy'
    WHEN left(telnum,6) = 001876 THEN 'Jamaica'
    WHEN left(telnum,4) = 0081 THEN 'Japan'
    WHEN left(telnum,5) = 00962 THEN 'Jordan'
    WHEN left(telnum,3) = 007 THEN 'Kazakhstan'
    WHEN left(telnum,5) = 00254 THEN 'Kenya'
    WHEN left(telnum,5) = 00686 THEN 'Kiribati'
    WHEN left(telnum,5) = 00965 THEN 'Kuwait'
    WHEN left(telnum,5) = 00996 THEN 'Kyrgyzstan'
    WHEN left(telnum,5) = 00856 THEN 'Laos'
    WHEN left(telnum,5) = 00371 THEN 'Latvia'
    WHEN left(telnum,5) = 00961 THEN 'Lebanon'
    WHEN left(telnum,5) = 00226 THEN 'Lesotho'
    WHEN left(telnum,5) = 00231 THEN 'Liberia'
    WHEN left(telnum,5) = 00218 THEN 'Libya'
    WHEN left(telnum,5) = 00423 THEN 'Liechtenstein'
    WHEN left(telnum,5) = 00370 THEN 'Lithuania'
    WHEN left(telnum,5) = 00352 THEN 'Luxembourg'
    WHEN left(telnum,5) = 00853 THEN 'Macao'
    WHEN left(telnum,5) = 00389 THEN 'Macedonia'
    WHEN left(telnum,5) = 00261 THEN 'Madagascar'
    WHEN left(telnum,5) = 00265 THEN 'Malawi'
    WHEN left(telnum,4) = 0060 THEN 'Malaysia'
    WHEN left(telnum,5) = 00960 THEN 'Maldives'
    WHEN left(telnum,5) = 00223 THEN 'Mali'
    WHEN left(telnum,5) = 00356 THEN 'Malta'
    WHEN left(telnum,5) = 00596 THEN 'Martinique'
    WHEN left(telnum,5) = 00222 THEN 'Mauritania'
    WHEN left(telnum,5) = 00230 THEN 'Mauritius'
    WHEN left(telnum,4) = 0052 THEN 'Mexico'
    WHEN left(telnum,5) = 00691 THEN 'Micronesia'
    WHEN left(telnum,5) = 00373 THEN 'Moldova'
    WHEN left(telnum,5) = 00377 THEN 'Monaco'
    WHEN left(telnum,5) = 00976 THEN 'Mongolia'
    WHEN left(telnum,5) = 00382 THEN 'Montenegro'
    WHEN left(telnum,6) = 001664 THEN 'Montserrat'
    WHEN left(telnum,5) = 00212 THEN 'Morocco'
    WHEN left(telnum,5) = 00258 THEN 'Mozambique'
    WHEN left(telnum,4) = 0095 THEN 'Myanmar'
    WHEN left(telnum,5) = 00264 THEN 'Namibia'
    WHEN left(telnum,5) = 00674 THEN 'Nauru'
    WHEN left(telnum,5) = 00977 THEN 'Nepal'
    WHEN left(telnum,4) = 0031 THEN 'Netherlands'
    WHEN left(telnum,5) = 00505 THEN 'Nicaragua'
    WHEN left(telnum,5) = 00227 THEN 'Niger'
    WHEN left(telnum,5) = 00234 THEN 'Nigeria'
    WHEN left(telnum,5) = 00683 THEN 'Niue'
    WHEN left(telnum,4) = 0047 THEN 'Norway'
    WHEN left(telnum,5) = 00968 THEN 'Oman'
    WHEN left(telnum,4) = 0092 THEN 'Pakistan'
    WHEN left(telnum,5) = 00680 THEN 'Palau'
    WHEN left(telnum,5) = 00970 THEN 'Palestine'
    WHEN left(telnum,5) = 00507 THEN 'Panama'
    WHEN left(telnum,5) = 00595 THEN 'Paraguay'
    WHEN left(telnum,4) = 0051 THEN 'Peru'
    WHEN left(telnum,4) = 0063 THEN 'Philippines'
    WHEN left(telnum,4) = 0048 THEN 'Poland'
    WHEN left(telnum,5) = 00351 THEN 'Portugal'
    WHEN left(telnum,5) = 00974 THEN 'Qatar'
    WHEN left(telnum,5) = 00262 THEN 'Reunion'
    WHEN left(telnum,4) = 0040 THEN 'Romania'
    WHEN left(telnum,3) = 007 THEN 'Russia'
    WHEN left(telnum,5) = 00250 THEN 'Rwanda'
    WHEN left(telnum,5) = 00221 THEN 'Senegal'
    WHEN left(telnum,5) = 00381 THEN 'Serbia'
    WHEN left(telnum,5) = 00248 THEN 'Seychelles'
    WHEN left(telnum,4) = 0065 THEN 'Singapore'
    WHEN left(telnum,5) = 00421 THEN 'Slovakia'
    WHEN left(telnum,5) = 00386 THEN 'Slovenia'
    WHEN left(telnum,5) = 00252 THEN 'Somalia'
    WHEN left(telnum,4) = 0034 THEN 'Spain'
    WHEN left(telnum,5) = 00249 THEN 'Sudan'
    WHEN left(telnum,5) = 00597 THEN 'Suriname'
    WHEN left(telnum,5) = 00268 THEN 'Swaziland'
    WHEN left(telnum,4) = 0046 THEN 'Sweden'
    WHEN left(telnum,4) = 0041 THEN 'Switzerland'
    WHEN left(telnum,5) = 00992 THEN 'Tajikistan'
    WHEN left(telnum,5) = 00255 THEN 'Tanzania'
    WHEN left(telnum,4) = 0066 THEN 'Thailand'
    WHEN left(telnum,5) = 00228 THEN 'Togo'
    WHEN left(telnum,5) = 00690 THEN 'Tokelau'
    WHEN left(telnum,5) = 00676 THEN 'Tonga'
    WHEN left(telnum,5) = 00216 THEN 'Tunisia'
    WHEN left(telnum,4) = 0090 THEN 'Turkey'
    WHEN left(telnum,5) = 00993 THEN 'Turkmenistan'
    WHEN left(telnum,5) = 00688 THEN 'Tuvalu'
    WHEN left(telnum,5) = 00256 THEN 'Uganda'
    WHEN left(telnum,5) = 00380 THEN 'Ukraine'
    WHEN left(telnum,5) = 00598 THEN 'Uruguay'
    WHEN left(telnum,5) = 00998 THEN 'Uzbekistan'
    WHEN left(telnum,5) = 00678 THEN 'Vanuatu'
    WHEN left(telnum,4) = 0058 THEN 'Venezuela'
    WHEN left(telnum,4) = 0084 THEN 'Vietnam'
    WHEN left(telnum,5) = 00967 THEN 'Yemen'
    WHEN left(telnum,5) = 00260 THEN 'Zambia'
    WHEN left(telnum,5) = 00263 THEN 'Zimbabwe'
   ELSE ''
   END

php_phoneprefix_to_zone_field.php

$call_code = array();
$call_code['93']='Afghanistan';
$call_code['355']='Albania';
$call_code['213']='Algeria';
$call_code['376']='Andorra';
$call_code['244']='Angola';
$call_code['1264']='Anguilla';
$call_code['6721']='Antarctica';
$call_code['54']='Argentina';
$call_code['374']='Armenia';
$call_code['297']='Aruba';
$call_code['61']='Australia';
$call_code['43']='Austria';
$call_code['994']='Azerbaijan';
$call_code['1242']='Bahamas';
$call_code['973']='Bahrain';
$call_code['880']='Bangladesh';
$call_code['1246']='Barbados';
$call_code['375']='Belarus';
$call_code['32']='Belgium';
$call_code['501']='Belize';
$call_code['229']='Benin';
$call_code['1441']='Bermuda';
$call_code['975']='Bhutan';
$call_code['591']='Bolivia';
$call_code['267']='Botswana';
$call_code['55']='Brazil';
$call_code['359']='Bulgaria';
$call_code['257']='Burundi';
$call_code['855']='Cambodia';
$call_code['237']='Cameroon';
$call_code['1']='Canada';
$call_code['235']='Chad';
$call_code['56']='Chile';
$call_code['86']='China';
$call_code['57']='Colombia';
$call_code['269']='Comoros';
$call_code['242']='Congo';
$call_code['385']='Croatia';
$call_code['53']='Cuba';
$call_code['357']='Cyprus';
$call_code['45']='Denmark';
$call_code['253']='Djibouti';
$call_code['1767']='Dominica';
$call_code['593']='Ecuador';
$call_code['20']='Egypt';
$call_code['291']='Eritrea';
$call_code['372']='Estonia';
$call_code['251']='Ethiopia';
$call_code['679']='Fiji';
$call_code['358']='Finland';
$call_code['33']='France';
$call_code['241']='Gabon';
$call_code['220']='Gambia';
$call_code['995']='Georgia';
$call_code['49']='Germany';
$call_code['233']='Ghana';
$call_code['350']='Gibraltar';
$call_code['30']='Greece';
$call_code['299']='Greenland';
$call_code['1473']='Grenada';
$call_code['590']='Guadeloupe';
$call_code['1671']='Guam';
$call_code['502']='Guatemala';
$call_code['224']='Guinea';
$call_code['592']='Guyana';
$call_code['509']='Haiti';
$call_code['504']='Honduras';
$call_code['36']='Hungary';
$call_code['354']='Iceland';
$call_code['91']='India';
$call_code['62']='Indonesia';
$call_code['98']='Iran';
$call_code['964']='Iraq';
$call_code['353']='Ireland';
$call_code['972']='Israel';
$call_code['39']='Italy';
$call_code['1876']='Jamaica';
$call_code['81']='Japan';
$call_code['962']='Jordan';
$call_code['7']='Kazakhstan';
$call_code['254']='Kenya';
$call_code['686']='Kiribati';
$call_code['965']='Kuwait';
$call_code['996']='Kyrgyzstan';
$call_code['856']='Laos';
$call_code['371']='Latvia';
$call_code['961']='Lebanon';
$call_code['226']='Lesotho';
$call_code['231']='Liberia';
$call_code['218']='Libya';
$call_code['423']='Liechtenstein';
$call_code['370']='Lithuania';
$call_code['352']='Luxembourg';
$call_code['853']='Macao';
$call_code['389']='Macedonia';
$call_code['261']='Madagascar';
$call_code['265']='Malawi';
$call_code['60']='Malaysia';
$call_code['960']='Maldives';
$call_code['223']='Mali';
$call_code['356']='Malta';
$call_code['596']='Martinique';
$call_code['222']='Mauritania';
$call_code['230']='Mauritius';
$call_code['52']='Mexico';
$call_code['691']='Micronesia';
$call_code['373']='Moldova';
$call_code['377']='Monaco';
$call_code['976']='Mongolia';
$call_code['382']='Montenegro';
$call_code['1664']='Montserrat';
$call_code['212']='Morocco';
$call_code['258']='Mozambique';
$call_code['95']='Myanmar';
$call_code['264']='Namibia';
$call_code['674']='Nauru';
$call_code['977']='Nepal';
$call_code['31']='Netherlands';
$call_code['505']='Nicaragua';
$call_code['227']='Niger';
$call_code['234']='Nigeria';
$call_code['683']='Niue';
$call_code['47']='Norway';
$call_code['968']='Oman';
$call_code['92']='Pakistan';
$call_code['680']='Palau';
$call_code['970']='Palestine';
$call_code['507']='Panama';
$call_code['595']='Paraguay';
$call_code['51']='Peru';
$call_code['63']='Philippines';
$call_code['48']='Poland';
$call_code['351']='Portugal';
$call_code['974']='Qatar';
$call_code['262']='Reunion';
$call_code['40']='Romania';
$call_code['7']='Russia';
$call_code['250']='Rwanda';
$call_code['221']='Senegal';
$call_code['381']='Serbia';
$call_code['248']='Seychelles';
$call_code['65']='Singapore';
$call_code['421']='Slovakia';
$call_code['386']='Slovenia';
$call_code['252']='Somalia';
$call_code['34']='Spain';
$call_code['249']='Sudan';
$call_code['597']='Suriname';
$call_code['268']='Swaziland';
$call_code['46']='Sweden';
$call_code['41']='Switzerland';
$call_code['992']='Tajikistan';
$call_code['255']='Tanzania';
$call_code['66']='Thailand';
$call_code['228']='Togo';
$call_code['690']='Tokelau';
$call_code['676']='Tonga';
$call_code['216']='Tunisia';
$call_code['90']='Turkey';
$call_code['993']='Turkmenistan';
$call_code['688']='Tuvalu';
$call_code['256']='Uganda';
$call_code['380']='Ukraine';
$call_code['598']='Uruguay';
$call_code['998']='Uzbekistan';
$call_code['678']='Vanuatu';
$call_code['58']='Venezuela';
$call_code['84']='Vietnam';
$call_code['967']='Yemen';
$call_code['260']='Zambia';
$call_code['263']='Zimbabwe';

$func = function($value) {
   $code2=substr($value['telnum'],2,2);
   $code3=substr($value['telnum'],2,3);
   $code4=substr($value['telnum'],2,4);
   if(array_key_exists($code2,$call_code))
      $value['country']=$call_code[$code2];
   else if(array_key_exists($code3,$call_code))
      $value['country']=$call_code[$code3];
   else if(array_key_exists($code4,$call_code))
      $value['country']=$call_code[$code4];
   return $value;
};

$countries=array_map($func,$data);