{"id":254173,"date":"2015-03-31T13:09:07","date_gmt":"2015-03-31T09:09:07","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=254173"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-29T21:00:00","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=254173","title":{"rendered":"IP-Geo. \u041e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u044f SQL \u0437\u0430\u043f\u0440\u043e\u0441\u0430"},"content":{"rendered":"<p> \t\t\t<img decoding=\"async\" src=\"\/\/habrastorage.org\/files\/54e\/1e8\/e57\/54e1e8e5774c45fcb937712dcb71ae51.png\"\/><\/p>\n<p>  \u0417\u0434\u0440\u0430\u0432\u0441\u0442\u0432\u0443\u0439, \u0425\u0430\u0431\u0440\u0430\u0445\u0430\u0431\u0440! \u041c\u0435\u043d\u044f \u0437\u043e\u0432\u0443\u0442 \u0411\u0430\u0431\u0438\u0447\u0435\u0432 \u041c\u0430\u043a\u0441\u0438\u043c \u0438 \u044f <s>\u0431\u044b\u0434\u043b\u043e<\/s>\u043a\u043e\u0434\u0435\u0440. \u042d\u0442\u043e \u043c\u043e\u044f \u043f\u0435\u0440\u0432\u0430\u044f \u0441\u0442\u0430\u0442\u044c\u044f \u043d\u0430 \u0425\u0430\u0431\u0440\u0430\u0445\u0430\u0431\u0440, \u043f\u0440\u043e\u0448\u0443 \u0441\u0442\u0440\u043e\u0433\u043e \u043d\u0435 \u0441\u0443\u0434\u0438\u0442\u044c.<\/p>\n<p>  \u041d\u0430\u0448\u0435\u043b \u043d\u0430 \u0425\u0430\u0431\u0440\u0435 <a href=\"http:\/\/habrahabr.ru\/post\/193024\/\">\u0441\u0442\u0430\u0442\u044c\u044e<\/a>, \u0432 \u043a\u043e\u0442\u043e\u0440\u043e\u0439 \u0431\u044b\u043b\u0438 \u0431\u0430\u0437\u044b \u0432 \u0434\u0432\u0443\u0445 \u043c\u043e\u0434\u0438\u0444\u0438\u043a\u0430\u0446\u0438\u044f\u0445: <a href=\"http:\/\/www.gogototour.com\/geoip\/net_city.zip\">\u0421\u0442\u0440\u0430\u043d\u044b \u0438 \u0433\u043e\u0440\u043e\u0434\u0430<\/a> \u0438 <a href=\"http:\/\/www.gogototour.com\/geoip\/net_country.zip\">\u0442\u043e\u043b\u044c\u043a\u043e \u0441\u0442\u0440\u0430\u043d\u044b<\/a>. \u0422\u0430\u043a\u0436\u0435 \u0432 \u044d\u0442\u0438\u0445 \u0430\u0440\u0445\u0438\u0432\u0430\u0445 \u0435\u0441\u0442\u044c \u043d\u0435\u0431\u043e\u043b\u044c\u0448\u0438\u0435 \u043f\u0440\u0438\u043c\u0435\u0440\u044b \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044f \u0442\u0430\u0431\u043b\u0438\u0446 \u043d\u0430 php.<\/p>\n<p>  \u0421\u0440\u0430\u0437\u0443 \u0445\u043e\u0447\u0443 \u0441\u043a\u0430\u0437\u0430\u0442\u044c, \u0447\u0442\u043e \u044d\u0442\u0430 \u0441\u0442\u0430\u0442\u044c\u044f \u0440\u0430\u0441\u0441\u0447\u0438\u0442\u0430\u043d\u0430 \u043d\u0430 \u043d\u043e\u0432\u0438\u0447\u043a\u043e\u0432, \u0430 \u043d\u0435 \u043d\u0430 \u043f\u0440\u043e\u0434\u0432\u0438\u043d\u0443\u0442\u044b\u0445 \u0433\u0443\u0440\u0443. <br \/>  <a name=\"habracut\"><\/a>  <\/p>\n<div class=\"spoiler\"><b class=\"spoiler_title\">\u041d\u0430 \u0432\u0441\u044f\u043a\u0438\u0439 \u0441\u043b\u0443\u0447\u0430\u0439 \u043f\u0440\u0438\u043b\u043e\u0436\u0443 \u0441\u0442\u0440\u0443\u043a\u0442\u0443\u0440\u0443 \u0442\u0430\u0431\u043b\u0438\u0446<\/b><\/p>\n<div class=\"spoiler_text\">\n<pre><code class=\"sql\">    --     -- \u0421\u0442\u0440\u0443\u043a\u0442\u0443\u0440\u0430 \u0442\u0430\u0431\u043b\u0438\u0446\u044b `net_city`     --      CREATE TABLE IF NOT EXISTS `net_city` (         `id`          INT(11) NOT NULL AUTO_INCREMENT,         `country_id`  INT(11)          DEFAULT NULL,         `name_ru`     VARCHAR(100)     DEFAULT NULL,         `name_en`     VARCHAR(100)     DEFAULT NULL,         `region`      VARCHAR(2)       DEFAULT NULL,         `postal_code` VARCHAR(10)      DEFAULT NULL,         `latitude`    VARCHAR(10)      DEFAULT NULL,         `longitude`   VARCHAR(10)      DEFAULT NULL,         PRIMARY KEY (`id`),         KEY `country_id` (`country_id`),         KEY `name_ru` (`name_ru`),         KEY `name_en` (`name_en`)     )     ENGINE = MyISAM     DEFAULT CHARSET = utf8;       --     -- \u0421\u0442\u0440\u0443\u043a\u0442\u0443\u0440\u0430 \u0442\u0430\u0431\u043b\u0438\u0446\u044b `net_city_ip`     --      CREATE TABLE IF NOT EXISTS `net_city_ip` (         `city_id`  INT(11)    DEFAULT NULL,         `begin_ip` BIGINT(11) DEFAULT NULL,         `end_ip`   BIGINT(11) DEFAULT NULL,         KEY `city_id` (`city_id`),         KEY `ip` (`begin_ip`)     )     ENGINE = MyISAM     DEFAULT CHARSET = utf8;       --     -- \u0421\u0442\u0440\u0443\u043a\u0442\u0443\u0440\u0430 \u0442\u0430\u0431\u043b\u0438\u0446\u044b `net_country`     --      CREATE TABLE IF NOT EXISTS `net_country` (         `id`      INT(11) NOT NULL AUTO_INCREMENT,         `name_ru` VARCHAR(100)     DEFAULT NULL,         `name_en` VARCHAR(100)     DEFAULT NULL,         `code`    VARCHAR(2)       DEFAULT NULL,         PRIMARY KEY (`id`),         KEY `code` (`code`),         KEY `name_en` (`name_en`),         KEY `name_ru` (`name_ru`)     )     ENGINE = MyISAM     DEFAULT CHARSET = utf8;       --     -- \u0421\u0442\u0440\u0443\u043a\u0442\u0443\u0440\u0430 \u0442\u0430\u0431\u043b\u0438\u0446\u044b `net_country_ip`     --      CREATE TABLE IF NOT EXISTS `net_country_ip` (         `country_id` INT(11)    DEFAULT '0',         `begin_ip`   BIGINT(11) DEFAULT NULL,         `end_ip`     BIGINT(11) DEFAULT '0',         KEY `country_id` (`country_id`),         KEY `ip` (`begin_ip`)     )     ENGINE = MyISAM     DEFAULT CHARSET = utf8;       --     -- \u0421\u0442\u0440\u0443\u043a\u0442\u0443\u0440\u0430 \u0442\u0430\u0431\u043b\u0438\u0446\u044b `net_euro`     --      CREATE TABLE IF NOT EXISTS `net_euro` (         `country_id` INT(11)    DEFAULT '0',         `begin_ip`   BIGINT(11) DEFAULT NULL,         `end_ip`     BIGINT(11) DEFAULT '0',         KEY `country_id` (`country_id`),         KEY `ip` (`begin_ip`)     )     ENGINE = MyISAM     DEFAULT CHARSET = utf8;       --     -- \u0421\u0442\u0440\u0443\u043a\u0442\u0443\u0440\u0430 \u0442\u0430\u0431\u043b\u0438\u0446\u044b `net_ru`     --      CREATE TABLE IF NOT EXISTS `net_ru` (         `city_id`  INT(11)    DEFAULT '0',         `begin_ip` BIGINT(11) DEFAULT NULL,         `end_ip`   BIGINT(11) DEFAULT NULL,         KEY `city_id` (`city_id`),         KEY `ip` (`begin_ip`)     )     ENGINE = MyISAM     DEFAULT CHARSET = utf8;     <\/code><\/pre>\n<p>  <\/div>\n<\/div>\n<p>  \u041c\u0435\u043d\u044f \u0431\u043e\u043b\u044c\u0448\u0435 \u0438\u043d\u0442\u0435\u0440\u0435\u0441\u0443\u044e\u0442 \u0437\u0430\u043f\u0440\u043e\u0441\u044b SQL.<\/p>\n<p>  LONG_IP_ADDRESS, \u0447\u0438\u0441\u043b\u043e \u043f\u043e\u043b\u0443\u0447\u0435\u043d\u043d\u043e\u0435 \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e \u0444\u0443\u043d\u043a\u0446\u0438\u0438 <a href=\"http:\/\/php.net\/manual\/ru\/function.ip2long.php\">ip2long()<\/a> \u0432 <a href=\"http:\/\/php.net\/\">PHP<\/a>.<\/p>\n<pre><code class=\"sql\">-- \u0418\u0449\u0435\u043c \u043f\u043e \u0440\u043e\u0441\u0441\u0438\u0439\u0441\u043a\u0438\u043c \u0438 \u0443\u043a\u0440\u0430\u0438\u043d\u0441\u043a\u0438\u043c \u0433\u043e\u0440\u043e\u0434\u0430\u043c -- \u0417\u0430\u043f\u0440\u043e\u0441 (1)  SELECT * FROM (     SELECT *     FROM net_ru     WHERE begin_ip &lt;= LONG_IP_ADDRESS \u2013 IP \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u044f, ip2long()     ORDER BY begin_ip DESC     LIMIT 1 ) AS t WHERE end_ip &gt;= LONG_IP_ADDRESS \u2013 IP \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u044f long <\/code><\/pre>\n<p>  \u041f\u043e\u0441\u043b\u0435 \u0437\u0430\u043f\u0440\u043e\u0441\u0430 (1), \u043f\u043e\u043b\u0443\u0447\u0430\u044e\u0442 \u043d\u0443\u0436\u043d\u044b\u0439 \u0433\u043e\u0440\u043e\u0434 \u0438\u0437 \u0442\u0430\u0431\u043b\u0438\u0446\u044b net_city:<\/p>\n<pre><code class=\"sql\">-- \u0417\u0430\u043f\u0440\u043e\u0441 (2)  SELECT * FROM net_city WHERE id = -- (\u0420\u0435\u0437\u0443\u043b\u044c\u0442\u0430\u0442 \u0438\u0437 \u043f\u0435\u0440\u0432\u043e\u0433\u043e \u0437\u0430\u043f\u0440\u043e\u0441\u0430).city_id <\/code><\/pre>\n<p>  \u041a\u043e\u0434 \u0438\u0437 \u043f\u0440\u0438\u043c\u0435\u0440\u0430 \u043d\u0430 PHP:<\/p>\n<pre><code class=\"php\">&lt;?php \/\/ \u041f\u043e\u0434\u043a\u043b\u044e\u0447\u0430\u0435\u043c\u0441\u044f \u043a \u0431\u0430\u0437\u0435 \u0434\u0430\u043d\u043d\u044b\u0445 $db_host = &quot;localhost&quot;; $db_user = &quot;&quot;; $db_password = &quot;&quot;; $db_database = &quot;geo&quot;; $link = mysql_connect ($db_host, $db_user, $db_password); if ($link && mysql_select_db ($db_database)) {     mysql_query (&quot;set names utf8&quot;); } else {     die (&quot;db error&quot;); }  \/\/ IP-\u0430\u0434\u0440\u0435\u0441, \u043a\u043e\u0442\u043e\u0440\u044b\u0439 \u043d\u0443\u0436\u043d\u043e \u043f\u0440\u043e\u0432\u0435\u0440\u0438\u0442\u044c $ip = &quot;79.134.219.2&quot;;  \/\/ \u041f\u0440\u0435\u043e\u0431\u0440\u0430\u0437\u0443\u0435\u043c IP \u0432 \u0447\u0438\u0441\u043b\u043e $int = sprintf(&quot;%u&quot;, ip2long($ip));  $country_name = &quot;&quot;; $country_id = 0;  $city_name = &quot;&quot;; $city_id = 0;  \/\/ \u0418\u0449\u0435\u043c \u043f\u043e \u0440\u043e\u0441\u0441\u0438\u0439\u0441\u043a\u0438\u043c \u0438 \u0443\u043a\u0440\u0430\u0438\u043d\u0441\u043a\u0438\u043c \u0433\u043e\u0440\u043e\u0434\u0430\u043c $sql = &quot;select * from (select * from net_ru where begin_ip&lt;=$int order by begin_ip desc limit 1) as t where end_ip&gt;=$int&quot;; $result = mysql_query($sql); if ($row = mysql_fetch_array($result)) {     $city_id = $row['city_id'];     $sql = &quot;select * from net_city where id='$city_id'&quot;;     $result = mysql_query($sql);     if ($row = mysql_fetch_array($result)) {         $city_name = $row['name_ru'];         $country_id = $row['country_id'];     } else {         $city_id = 0;     } } <\/code><\/pre>\n<p>  \u0418\u0437\u0431\u0430\u0432\u0438\u043c\u0441\u044f \u043e\u0442 \u043f\u043e\u0434\u0437\u0430\u043f\u0440\u043e\u0441\u0430 \u0432 \u0437\u0430\u043f\u0440\u043e\u0441\u0435 (1), \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u044f AND.<\/p>\n<pre><code class=\"sql\">-- \u041c\u043e\u0434\u0438\u0444\u0438\u0446\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u044b\u0439 \u0437\u0430\u043f\u0440\u043e\u0441 (3)  SELECT `city_id` FROM `net_ru` WHERE begin_ip &lt;= LONG_IP_ADDRESS AND end_ip &gt;= LONG_IP_ADDRESS <\/code><\/pre>\n<p>  \u0418 \u0432 \u044d\u0442\u043e\u043c \u0441\u043b\u0443\u0447\u0430\u0435 \u043c\u044b \u0438\u0437\u0431\u0430\u0432\u0438\u043c\u0441\u044f \u043e\u0442 \u043f\u043e\u0434\u0437\u0430\u043f\u0440\u043e\u0441\u0430. \u041d\u043e \u0432\u0441\u043f\u043e\u043c\u043d\u0438\u043c \u043f\u0440\u043e BETWEEN \u0438 \u0437\u0430\u043f\u0438\u0448\u0435\u043c \u0437\u0430\u043f\u0440\u043e\u0441 \u0442\u0430\u043a:<\/p>\n<pre><code class=\"sql\">-- \u041c\u043e\u0434\u0438\u0444\u0438\u0446\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u044b\u0439 \u0437\u0430\u043f\u0440\u043e\u0441 (4)  SELECT `city_id` FROM `net_ru` WHERE LONG_IP_ADDRESS BETWEEN begin_ip AND end_ip <\/code><\/pre>\n<p>  \u0422\u0430\u043a SQL-\u043a\u043e\u0434 \u0447\u0438\u0442\u0430\u0431\u0435\u043b\u044c\u043d\u0435\u0435 \u0438 \u043a\u043e\u0440\u043e\u0447\u0435.<\/p>\n<p>  \u041e\u0441\u0442\u0430\u043b\u0441\u044f \u043e\u0442\u0434\u0435\u043b\u044c\u043d\u044b\u0439 \u0437\u0430\u043f\u0440\u043e\u0441 \u043d\u0430 \u0433\u043e\u0440\u043e\u0434. \u041e\u0431\u044a\u0435\u0434\u0438\u043d\u0438\u043c \u043c\u043e\u0434\u0438\u0444\u0438\u0446\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u044b\u0439 \u0437\u0430\u043f\u0440\u043e\u0441 (4) \u0438 (2).<\/p>\n<pre><code class=\"sql\">-- \u041c\u043e\u0434\u0438\u0444\u0438\u0446\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u044b\u0439 \u0437\u0430\u043f\u0440\u043e\u0441 (5)  SELECT * FROM `net_city` `city`     JOIN (         SELECT `city_id`         FROM `net_ru`         WHERE LONG_IP_ADDRESS BETWEEN begin_ip AND end_ip     ) AS `res` ON `res`.`city_id` = `city`.`id` <\/code><\/pre>\n<p>  \u0422\u0430\u043a \u043f\u043e\u043b\u0443\u0447\u0438\u043b\u043e\u0441\u044c, \u0447\u0442\u043e IP-\u0430\u0434\u0440\u0435\u0441\u0430 79.134.219.2 \u0432 \u0431\u0430\u0437\u0435 net_ru \u2013 \u043d\u0435\u0442. \u041d\u043e \u043e\u043d \u0435\u0441\u0442\u044c \u0432 \u0431\u0430\u0437\u0435 net_city_ip. <br \/>  \u041d\u0430 \u043c\u043d\u043e\u0433\u0438\u0445 \u0440\u0435\u0441\u0443\u0440\u0441\u0430\u0445 \u043e\u0431\u044a\u0435\u0434\u0438\u043d\u044f\u044e\u0442 \u0437\u0430\u043f\u0440\u043e\u0441\u044b \u0442\u0430\u043a:<\/p>\n<pre><code class=\"sql\">-- \u041c\u043e\u0434\u0438\u0444\u0438\u0446\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u044b\u0439 \u0437\u0430\u043f\u043e\u0441 (5.1)  SELECT * FROM `net_city` `city`     JOIN (         SELECT `city_id`         FROM `net_ru`         WHERE LONG_IP_ADDRESS BETWEEN begin_ip AND end_ip     ) AS `res` ON `res`.`city_id` = `city`.`id` UNION SELECT * FROM `net_city` `city`     JOIN (         SELECT `city_id`         FROM `net_city_ip`         WHERE LONG_IP_ADDRESS BETWEEN begin_ip AND end_ip     ) AS `res` ON `res`.`city_id` = `city`.`id` <\/code><\/pre>\n<p>  \u0412\u0438\u0434\u0438\u043c, \u0447\u0442\u043e \u0437\u0430\u043f\u0440\u043e\u0441\u044b \u0438\u0434\u0435\u043d\u0442\u0438\u0447\u043d\u044b. \u041e\u0431\u044a\u0435\u0434\u0438\u043d\u0438\u043c \u0432\u043d\u0443\u0442\u0440\u0438 JOIN \u0434\u0432\u0430 \u0437\u0430\u043f\u0440\u043e\u0441\u0430, \u043f\u043e\u043b\u0443\u0447\u0438\u043c:<\/p>\n<pre><code class=\"sql\">-- \u041c\u043e\u0434\u0438\u0444\u0438\u0446\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u044b\u0439 \u0437\u0430\u043f\u0440\u043e\u0441 (5.2)  SELECT * FROM `net_city` `city`     JOIN (         SELECT `city_id`         FROM `net_ru`         WHERE LONG_IP_ADDRESS BETWEEN  begin_ip  AND end_ip         UNION         SELECT `city_id`         FROM `net_city_ip`         WHERE LONG_IP_ADDRESS BETWEEN  begin_ip  AND end_ip     ) as `res` ON `res`.`city_id` = `city`.`id` <\/code><\/pre>\n<p>  \u041c\u043e\u0434\u0438\u0444\u0438\u0446\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u044b\u0439 \u0437\u0430\u043f\u0440\u043e\u0441 (5.2) \u0445\u043e\u0440\u043e\u0448 \u0441\u043e\u0431\u043e\u0439, \u043d\u043e \u043d\u0430\u043c \u043d\u0435 \u043d\u0443\u0436\u043d\u044b \u0432\u0441\u0435 \u043f\u043e\u043b\u044f. <br \/>  \u0412\u044b\u0442\u0430\u0449\u0438\u043c:  <\/p>\n<ol>\n<li>name_ru<\/li>\n<li>name_en<\/li>\n<li>region<\/li>\n<li>postal_code<\/li>\n<li>latitude<\/li>\n<li>longitude<\/li>\n<\/ol>\n<pre><code class=\"sql\">-- \u041c\u043e\u0434\u0438\u0444\u0438\u0446\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u044b\u0439 \u0437\u0430\u043f\u0440\u043e\u0441 (6)  SELECT DISTINCT     `city`.`name_ru` `city_name_ru`,     `city`.`name_en` `city_name_en`,     `city`.`region`,     `city`.`postal_code`,     `city`.`latitude`,     `city`.`longitude` FROM `net_city` `city`     JOIN (         SELECT `city_id`         FROM `net_ru`         WHERE LONG_IP_ADDRESS BETWEEN begin_ip AND end_ip         UNION         SELECT `city_id`         FROM `net_city_ip`         WHERE LONG_IP_ADDRESS BETWEEN begin_ip AND end_ip     ) AS `res` ON `res`.`city_id` = `city`.`id` <\/code><\/pre>\n<p>  \u0414\u0430\u043b\u0435\u0435 \u043d\u0430\u043c \u043d\u0443\u0436\u043d\u043e \u0432\u044b\u0431\u0440\u0430\u0442\u044c \u0441\u0442\u0440\u0430\u043d\u0443, \u0432 \u043a\u043e\u0442\u043e\u0440\u043e\u0439 \u043d\u0430\u0445\u043e\u0434\u0438\u0442\u0441\u044f \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u044c. \u0414\u043e\u0431\u0430\u0432\u0438\u043c JOIN \u0441\u043e\u0435\u0434\u0438\u043d\u0435\u043d\u0438\u0435 \u0432 \u0437\u0430\u043f\u0440\u043e\u0441.<\/p>\n<pre><code class=\"sql\">-- \u041c\u043e\u0434\u0438\u0444\u0438\u0446\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u044b\u0439 \u0437\u0430\u043f\u0440\u043e\u0441 (7)  SELECT DISTINCT     `city`.`name_ru`    `city_name_ru`,     `city`.`name_en`    `city_name_en`,     `city`.`region`,     `city`.`postal_code`,     `city`.`latitude`,     `city`.`longitude`,     `country`.`name_ru` `country_name_ru`,     `country`.`name_en` `country_name_en`,     `country`.`code` FROM `net_city` `city`     JOIN (         SELECT `city_id`         FROM `net_ru`         WHERE LONG_IP_ADDRESS BETWEEN begin_ip AND end_ip         UNION         SELECT `city_id`         FROM `net_city_ip`         WHERE LONG_IP_ADDRESS BETWEEN begin_ip AND end_ip     ) AS `res` ON `res`.`city_id` = `city`.`id`     JOIN `net_country` `country`         ON `country`.`id` = `city`.`country_id` <\/code><\/pre>\n<p>  \u041f\u0440\u043e\u0442\u0435\u0441\u0442\u0438\u0440\u0443\u0435\u043c \u0437\u0430\u043f\u0440\u043e\u0441.<\/p>\n<p>  \u0414\u043b\u044f \u044d\u0442\u043e\u0433\u043e \u043f\u043e\u043b\u0443\u0447\u0438\u043c \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e PHP \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435 LANG_IP_ADDRESS:<\/p>\n<pre><code class=\"php\">&lt;?php echo ip2lang(\u201879.134.219.2\u2019); # \u0420\u0435\u0437\u0443\u043b\u044c\u0442\u0430\u0442: 1334237954 <\/code><\/pre>\n<p>  \u041f\u043e\u0434\u0441\u0442\u0430\u0432\u0438\u043c \u0435\u0433\u043e \u0432 \u043d\u0430\u0448 \u0437\u0430\u043f\u0440\u043e\u0441 \u0438 \u0432\u044b\u043f\u043e\u043b\u043d\u0438\u043c \u0432 phpMyAdmin.<\/p>\n<div class=\"spoiler\"><b class=\"spoiler_title\">\u041c\u043e\u0434\u0438\u0444\u0438\u0446\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u044b\u0439 \u0437\u0430\u043f\u0440\u043e\u0441 (7) \u0441 \u043f\u043e\u0434\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u043d\u044b\u043c \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435\u043c<\/b><\/p>\n<div class=\"spoiler_text\">\n<pre><code class=\"sql\">-- \u041c\u043e\u0434\u0438\u0444\u0438\u0446\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u044b\u0439 \u0437\u0430\u043f\u0440\u043e\u0441 (7) \u0441 \u043f\u043e\u0434\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u043d\u044b\u043c \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435\u043c  -- ip2lang(\u201879.134.219.2\u2019) \u0432\u043c\u0435\u0441\u0442\u043e LONG_IP_ADDRESS  SELECT DISTINCT     `city`.`name_ru`    `city_name_ru`,     `city`.`name_en`    `city_name_en`,     `city`.`region`,     `city`.`postal_code`,     `city`.`latitude`,     `city`.`longitude`,     `country`.`name_ru` `country_name_ru`,     `country`.`name_en` `country_name_en`,     `country`.`code` FROM `net_city` `city`     JOIN (         SELECT `city_id`         FROM `net_ru`         WHERE 1334237954 BETWEEN begin_ip AND end_ip         UNION         SELECT `city_id`         FROM `net_city_ip`         WHERE 1334237954 BETWEEN begin_ip AND end_ip     ) AS `res` ON `res`.`city_id` = `city`.`id`     JOIN `net_country` `country`         ON `country`.`id` = `city`.`country_id` <\/code><\/pre>\n<p>  <\/div>\n<\/div>\n<p>  \u0420\u0435\u0437\u0443\u043b\u044c\u0442\u0430\u0442 \u0440\u0430\u0431\u043e\u0442\u044b \u0437\u0430\u043f\u0440\u043e\u0441\u0430:<\/p>\n<p>  <img decoding=\"async\" src=\"\/\/habrastorage.org\/files\/618\/77b\/015\/61877b0156a2437e932b56a24d2474d9.png\"\/><\/p>\n<p>  1 \u0432\u0441\u0435\u0433\u043e, \u0437\u0430\u043f\u0440\u043e\u0441 \u0437\u0430\u043d\u044f\u043b 0.3408 \u0441\u0435\u043a.<\/p>\n<p>  JOIN \u0441\u043e\u0435\u0434\u0438\u043d\u0435\u043d\u0438\u044f \u0441\u0438\u043b\u044c\u043d\u043e \u0432\u043b\u0438\u044f\u044e\u0442 \u043d\u0430 \u0441\u043a\u043e\u0440\u043e\u0441\u0442\u044c \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f \u0437\u0430\u043f\u0440\u043e\u0441\u0430. \u0417\u0430\u043f\u0438\u0448\u0435\u043c JOIN \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e WHERE<\/p>\n<pre><code class=\"sql\">-- \u041c\u043e\u0434\u0438\u0444\u0438\u0446\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u044b\u0439 \u0437\u0430\u043f\u0440\u043e\u0441 (8)  -- \u0417\u0430\u043f\u0438\u0441\u0430\u043b\u0438 JOIN \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e WHERE  SELECT DISTINCT     `city`.`name_ru` `city_name_ru`,     `city`.`name_en` `city_name_en`,     `city`.`region`,     `city`.`postal_code`,     `city`.`latitude`,     `city`.`longitude`,     `country`.`name_ru` `country_name_ru`,     `country`.`name_en` `country_name_en`,     `country`.`code` FROM `net_city` `city`     JOIN `net_country` `country`         ON `country`.`id` = `city`.`country_id` WHERE `city`.`id` = (     SELECT `city_id`     FROM `net_city_ip`     WHERE LONG_IP_ADDRESS BETWEEN `begin_ip` AND `end_ip`     UNION     SELECT `city_id`     FROM `net_ru`     WHERE LONG_IP_ADDRESS BETWEEN `begin_ip` AND `end_ip` ) <\/code><\/pre>\n<p>  \u041f\u0440\u043e\u0442\u0435\u0441\u0442\u0438\u0440\u0443\u0435\u043c \u043c\u043e\u0434\u0438\u0444\u0438\u0446\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u044b\u0439 \u0437\u0430\u043f\u0440\u043e\u0441:<\/p>\n<div class=\"spoiler\"><b class=\"spoiler_title\">\u041c\u043e\u0434\u0438\u0444\u0438\u0446\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u044b\u0439 \u0437\u0430\u043f\u0440\u043e\u0441 \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e WHERE<\/b><\/p>\n<div class=\"spoiler_text\">\n<pre><code class=\"sql\">SELECT DISTINCT     `city`.`name_ru` `city_name_ru`,     `city`.`name_en` `city_name_en`,     `city`.`region`,     `city`.`postal_code`,     `city`.`latitude`,     `city`.`longitude`,     `country`.`name_ru` `country_name_ru`,     `country`.`name_en` `country_name_en`,     `country`.`code` FROM `net_city` `city`     JOIN `net_country` `country`         ON `country`.`id` = `city`.`country_id` WHERE `city`.`id` = (     SELECT `city_id`     FROM `net_city_ip`     WHERE 1334237954 BETWEEN `begin_ip` AND `end_ip`     UNION     SELECT `city_id`     FROM `net_ru`     WHERE 1334237954 BETWEEN `begin_ip` AND `end_ip` ) <\/code><\/pre>\n<p>  <\/div>\n<\/div>\n<p>  \u0420\u0435\u0437\u0443\u043b\u044c\u0442\u0430\u0442 \u0440\u0430\u0431\u043e\u0442\u044b \u0437\u0430\u043f\u0440\u043e\u0441\u0430:<\/p>\n<p>  <img decoding=\"async\" src=\"\/\/habrastorage.org\/files\/8fa\/d56\/dd1\/8fad56dd1a14483bae9b21c09b1c4766.png\"\/><\/p>\n<p>  1 \u0432\u0441\u0435\u0433\u043e, \u0437\u0430\u043f\u0440\u043e\u0441 \u0437\u0430\u043d\u044f\u043b 0.1527 \u0441\u0435\u043a.<\/p>\n<p>  \u041c\u043e\u0434\u0438\u0444\u0438\u0446\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u044b\u0439 \u0437\u0430\u043f\u0440\u043e\u0441 \u0431\u044b\u043b \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d \u0431\u044b\u0441\u0442\u0440\u0435\u0435 \u0431\u043e\u043b\u0435\u0435 \u0447\u0435\u043c \u0432 \u0434\u0432\u0430 \u0440\u0430\u0437\u0430.<\/p>\n<p>  \u0410\u043d\u0430\u043b\u043e\u0433\u0438\u0447\u043d\u044b\u0439 \u0437\u0430\u043f\u0440\u043e\u0441 \u043c\u043e\u0436\u043d\u043e \u043f\u043e\u043b\u0443\u0447\u0438\u0442\u044c \u0438 \u0434\u043b\u044f \u0441\u0442\u0440\u0430\u043d.<\/p>\n<p>  \u0414\u0430\u043d\u043d\u0430\u044f Geo-IP \u0431\u0430\u0437\u0430 \u0431\u044b\u043b\u0430 \u0432\u044b\u0431\u0440\u0430\u043d\u0430 \u0434\u043b\u044f \u043e\u0431\u044a\u044f\u0441\u043d\u0435\u043d\u0438\u044f JOIN\/UNION \u0441\u043e\u0435\u0434\u0438\u043d\u0435\u043d\u0438\u0439 \u0438 \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u0438 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432. <br \/>  \u041d\u0430\u0434\u0435\u044e\u0441\u044c, \u0434\u0430\u043d\u043d\u0430\u044f \u0441\u0442\u0430\u0442\u044c\u044f \u043f\u043e\u043c\u043e\u0436\u0435\u0442 \u043d\u0430\u0447\u0438\u043d\u0430\u044e\u0449\u0438\u043c \u043f\u043e\u043d\u044f\u0442\u044c, \u043d\u0430\u0441\u043a\u043e\u043b\u044c\u043a\u043e \u0432\u0430\u0436\u043d\u0430 \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u044f \u0438 \u043a\u0430\u043a \u0435\u0451 \u043c\u043e\u0436\u043d\u043e \u0434\u043e\u0431\u0438\u0442\u044c\u0441\u044f. \u0420\u0435\u043a\u043e\u043c\u0435\u043d\u0434\u0443\u044e \u043a \u0447\u0442\u0435\u043d\u0438\u044e <a href=\"http:\/\/habrahabr.ru\/post\/41166\/\">\u0441\u0442\u0430\u0442\u044c\u044e<\/a> \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u044f  <a href=\"http:\/\/habrahabr.ru\/users\/tuta_larson\/\" class=\"user_link\">tuta_larson<\/a>.<\/p>\n<p>  \u0414\u0430\u043d\u043d\u0430\u044f Geo-IP \u0431\u0430\u0437\u0430 \u043e\u0447\u0435\u043d\u044c \u0441\u0442\u0430\u0440\u0430\u044f \u0438 \u0437\u043d\u0430\u0435\u0442 \u043e\u0447\u0435\u043d\u044c \u043c\u0430\u043b\u043e IP-\u0430\u0434\u0440\u0435\u0441\u043e\u0432. \u041d\u043e \u0432\u044b \u043c\u043e\u0436\u0435\u0442\u0435 \u0441\u043e\u0441\u0442\u0430\u0432\u0438\u0442\u044c \u0441\u0432\u043e\u044e IP-\u0431\u0430\u0437\u0443 \u0438 \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u0435\u0439 \u043f\u043e\u043f\u043e\u043b\u043d\u044f\u0442\u044c \u0435\u0451.<\/p>\n<p>  <img decoding=\"async\" src=\"\/\/habrastorage.org\/files\/b94\/280\/91d\/b9428091dee0429ca999ce28dbde1f1b.png\"\/><\/p>\n<p>  \u0414\u0430\u043b\u044c\u0448\u0435 \u0441\u043e\u0441\u0442\u0430\u0432\u0438\u0442\u044c \u0440\u0435\u0439\u0442\u0438\u043d\u0433 IP \u0438 \u043e\u0441\u043d\u043e\u0432\u044b\u0432\u0430\u044f\u0441\u044c \u043d\u0430 \u0441\u043e\u0431\u0441\u0442\u0432\u0435\u043d\u043d\u044b\u0439 \u0440\u0435\u0439\u0442\u0438\u043d\u0433 \u201c\u0443\u0433\u0430\u0434\u044b\u0432\u0430\u0442\u044c\u201d \u0433\u043e\u0440\u043e\u0434 \u0438\u0437 \u043a\u043e\u0442\u043e\u0440\u043e\u0433\u043e \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u044c.<\/p>\n<p>  \u0411\u0430\u0437\u0430 \u0434\u0430\u043d\u043d\u044b\u0445 \u0438 \u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u044e \u043f\u043e GeoIP \u0431\u0440\u0430\u043b \u0438\u0437 \u0441\u0442\u0430\u0442\u044c\u0438: <a href=\"http:\/\/habrahabr.ru\/post\/193024\/\">\u00ab\u0411\u0430\u0437\u0430 GeoIP \u2013 \u0441\u0442\u0440\u0430\u043d\u044b \u0438 \u0433\u043e\u0440\u043e\u0434\u0430, \u0441\u0435\u043d\u0442\u044f\u0431\u0440\u044c 2013\u00bb<\/a>. \u0421\u043f\u0430\u0441\u0438\u0431\u043e \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u044e  <a href=\"http:\/\/habrahabr.ru\/users\/netload\/\" class=\"user_link\">netload<\/a> \u0437\u0430 \u0443\u0432\u043b\u0435\u043a\u0430\u0442\u0435\u043b\u044c\u043d\u0443\u044e \u0441\u0442\u0430\u0442\u044c\u044e, \u043d\u0430\u043f\u0438\u0441\u0430\u043d\u0443\u044e \u0432 2013 \u0433\u043e\u0434\u0443. \t\t\t<\/p>\n<div class=\"clear\"><\/div>\n<p> \u0441\u0441\u044b\u043b\u043a\u0430 \u043d\u0430 \u043e\u0440\u0438\u0433\u0438\u043d\u0430\u043b \u0441\u0442\u0430\u0442\u044c\u0438 <a href=\"http:\/\/habrahabr.ru\/post\/254461\/\"> http:\/\/habrahabr.ru\/post\/254461\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p> \t\t\t<img decoding=\"async\" src=\"\/\/habrastorage.org\/files\/54e\/1e8\/e57\/54e1e8e5774c45fcb937712dcb71ae51.png\"\/><\/p>\n<p>  \u0417\u0434\u0440\u0430\u0432\u0441\u0442\u0432\u0443\u0439, \u0425\u0430\u0431\u0440\u0430\u0445\u0430\u0431\u0440! \u041c\u0435\u043d\u044f \u0437\u043e\u0432\u0443\u0442 \u0411\u0430\u0431\u0438\u0447\u0435\u0432 \u041c\u0430\u043a\u0441\u0438\u043c \u0438 \u044f <s>\u0431\u044b\u0434\u043b\u043e<\/s>\u043a\u043e\u0434\u0435\u0440. \u042d\u0442\u043e \u043c\u043e\u044f \u043f\u0435\u0440\u0432\u0430\u044f \u0441\u0442\u0430\u0442\u044c\u044f \u043d\u0430 \u0425\u0430\u0431\u0440\u0430\u0445\u0430\u0431\u0440, \u043f\u0440\u043e\u0448\u0443 \u0441\u0442\u0440\u043e\u0433\u043e \u043d\u0435 \u0441\u0443\u0434\u0438\u0442\u044c.<\/p>\n<p>  \u041d\u0430\u0448\u0435\u043b \u043d\u0430 \u0425\u0430\u0431\u0440\u0435 <a href=\"http:\/\/habrahabr.ru\/post\/193024\/\">\u0441\u0442\u0430\u0442\u044c\u044e<\/a>, \u0432 \u043a\u043e\u0442\u043e\u0440\u043e\u0439 \u0431\u044b\u043b\u0438 \u0431\u0430\u0437\u044b \u0432 \u0434\u0432\u0443\u0445 \u043c\u043e\u0434\u0438\u0444\u0438\u043a\u0430\u0446\u0438\u044f\u0445: <a href=\"http:\/\/www.gogototour.com\/geoip\/net_city.zip\">\u0421\u0442\u0440\u0430\u043d\u044b \u0438 \u0433\u043e\u0440\u043e\u0434\u0430<\/a> \u0438 <a href=\"http:\/\/www.gogototour.com\/geoip\/net_country.zip\">\u0442\u043e\u043b\u044c\u043a\u043e \u0441\u0442\u0440\u0430\u043d\u044b<\/a>. \u0422\u0430\u043a\u0436\u0435 \u0432 \u044d\u0442\u0438\u0445 \u0430\u0440\u0445\u0438\u0432\u0430\u0445 \u0435\u0441\u0442\u044c \u043d\u0435\u0431\u043e\u043b\u044c\u0448\u0438\u0435 \u043f\u0440\u0438\u043c\u0435\u0440\u044b \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044f \u0442\u0430\u0431\u043b\u0438\u0446 \u043d\u0430 php.<\/p>\n<p>  \u0421\u0440\u0430\u0437\u0443 \u0445\u043e\u0447\u0443 \u0441\u043a\u0430\u0437\u0430\u0442\u044c, \u0447\u0442\u043e \u044d\u0442\u0430 \u0441\u0442\u0430\u0442\u044c\u044f \u0440\u0430\u0441\u0441\u0447\u0438\u0442\u0430\u043d\u0430 \u043d\u0430 \u043d\u043e\u0432\u0438\u0447\u043a\u043e\u0432, \u0430 \u043d\u0435 \u043d\u0430 \u043f\u0440\u043e\u0434\u0432\u0438\u043d\u0443\u0442\u044b\u0445 \u0433\u0443\u0440\u0443.   <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-254173","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/254173","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=254173"}],"version-history":[{"count":0,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/254173\/revisions"}],"wp:attachment":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=254173"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=254173"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=254173"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}