{"id":565,"date":"2009-04-07T11:40:13","date_gmt":"2009-04-07T10:40:13","guid":{"rendered":"http:\/\/www.galhano.com\/blog\/?p=565"},"modified":"2009-04-08T14:50:05","modified_gmt":"2009-04-08T13:50:05","slug":"geotargeting-database","status":"publish","type":"post","link":"https:\/\/galhano.com\/blog\/?p=565","title":{"rendered":"Geotargeting database"},"content":{"rendered":"<h4>Source link: http:\/\/ekstreme.com\/geotargeting\/flags-comments.php<\/h4>\n<h2>Requirements<\/h2>\n<ul>\n<li>A database of worldwide IP addresses. (I&#8217;ll show you where to get one in a second.)<\/li>\n<li>A database, like MySQL, and a server-side programming language, like PHP. The examples in this tutorial are PHP\/MySQL based, but any combination should work.<\/li>\n<li>Optionally, a set of country flags &#8211; again, I&#8217;ll show where you get them.<\/li>\n<\/ul>\n<h2>Geo IP database: setup<\/h2>\n<p>The first thing you need is a database of world wide IP addresses. A freely available one is MaxMind&#8217;s <a href=\"http:\/\/www.maxmind.com\/app\/geoip_country\">GeoLite Country Database<\/a>. <em>Download the CSV version<\/em>, not the binary format version.<\/p>\n<p>A note about the database:<\/p>\n<ul>\n<li>It is updated once a month, so you can (and should) update your copy frequently.<\/li>\n<li>It is claimed to be 97% accurate. This is sufficient for most users, but if you want a more accurate version, download the <a href=\"http:\/\/www.maxmind.com\/app\/country\">MaxMind GeoIP Country Database<\/a> version, which is not free.<\/li>\n<\/ul>\n<p>Once you download the IP database in CSV format, you need to upload it into your MySQL database. Here are the instructions:<\/p>\n<ul>\n<li><em>Create a new table in your database; call it IPCountries.<\/em><\/li>\n<li>In the IPCountries table, <em>create 6 (six) new fields<\/em>.<\/li>\n<li>The fields are:<br \/>\n<table border=\"0\" cellspacing=\"4\" cellpadding=\"0\">\n<thead>\n<tr>\n<td class=\"TableHeading\">Field<\/td>\n<td class=\"TableHeading\">Type<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td class=\"TableContents\">IP_START<\/td>\n<td class=\"TableContents\">VARCHAR, length 50<\/td>\n<\/tr>\n<tr>\n<td class=\"TableContents\">IP_END<\/td>\n<td class=\"TableContents\">VARCHAR, length 50<\/td>\n<\/tr>\n<tr>\n<td class=\"TableContents\">IP_FROM<\/td>\n<td class=\"TableContents\">Double<\/td>\n<\/tr>\n<tr>\n<td class=\"TableContents\">IP_TO<\/td>\n<td class=\"TableContents\">Double<\/td>\n<\/tr>\n<tr>\n<td class=\"TableContents\">COUNTRY_CODE2<\/td>\n<td class=\"TableContents\">Char, length 2<\/td>\n<\/tr>\n<tr>\n<td class=\"TableContents\">COUNTRY_NAME<\/td>\n<td class=\"TableContents\">VARCHAR, length 50<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n<li><em>Import the CSV file into the MySQL table.<\/em> If you are using phpMyAdmin, at the very bottom of the table view is a link that says &#8216;Insert data from a text file into the table&#8217;. Click that and then make sure the settings are the following:<br \/>\n<table border=\"0\" cellspacing=\"4\" cellpadding=\"0\">\n<thead>\n<tr>\n<td class=\"TableHeading\">Setting<\/td>\n<td class=\"TableHeading\">Value<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td class=\"TableContents\">Fields terminated by<\/td>\n<td class=\"TableContents\">, (a comma)<\/td>\n<\/tr>\n<tr>\n<td class=\"TableContents\">Fields enclosed by<\/td>\n<td class=\"TableContents\">&#8221; (a double-quote)<\/td>\n<\/tr>\n<tr>\n<td class=\"TableContents\">Lines terminated by<\/td>\n<td class=\"TableContents\">\\n (a linefeed)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><!--more--><br \/>\nNow browse to where you save the CSV file and click &#8216;Submit&#8217;. It&#8217;s a large file, so be patient <img decoding=\"async\" class=\"wp-smiley\" src=\"http:\/\/ekstreme.com\/wp-includes\/images\/smilies\/icon_smile.gif\" alt=\":)\" \/><\/li>\n<\/ul>\n<p>Now that we have the IP database ready, let&#8217;s use it!<\/p>\n<h2>IP Database Explanation<\/h2>\n<p>With the database ready, let&#8217;s take a look at what each field means:<\/p>\n<table border=\"0\" cellspacing=\"4\" cellpadding=\"0\">\n<thead>\n<tr>\n<td class=\"TableHeading\">Field<\/td>\n<td class=\"TableHeading\">Type<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td class=\"TableContents\">IP_START<\/td>\n<td class=\"TableContents\">The start of the IP range.<\/td>\n<\/tr>\n<tr>\n<td class=\"TableContents\">IP_END<\/td>\n<td class=\"TableContents\">The end of the IP range.<\/td>\n<\/tr>\n<tr>\n<td class=\"TableContents\">IP_FROM<\/td>\n<td class=\"TableContents\">The start of the IP range, as a long number.<\/td>\n<\/tr>\n<tr>\n<td class=\"TableContents\">IP_TO<\/td>\n<td class=\"TableContents\">The end of the IP range, as a long number.<\/td>\n<\/tr>\n<tr>\n<td class=\"TableContents\">COUNTRY_CODE2<\/td>\n<td class=\"TableContents\">A two-letter country code, such as &#8216;UK&#8217;.<\/td>\n<\/tr>\n<tr>\n<td class=\"TableContents\">COUNTRY_NAME<\/td>\n<td class=\"TableContents\">The full country name, such as &#8216;Germany&#8217;.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h2>Geotargeting PHP Code<\/h2>\n<p>The code is very simple:<\/p>\n<ul>\n<li>We get the visitor&#8217;s IP address&#8230;<\/li>\n<li>&#8230;and look it up in the database&#8230;<\/li>\n<li>&#8230;and figure out which country the IP address belongs to.<\/li>\n<\/ul>\n<p>And the code is:<\/p>\n<div class=\"code\">\n<div>$DatabaseServer = &#8220;YOUR_DATABASE_SERVER&#8221;;<\/div>\n<div>$Username = &#8220;YOUR_DATABASE_USERNAME&#8221;;<\/div>\n<div>$Password = &#8220;YOUR_DATABASE_PASSWORD&#8221;;<\/div>\n<div>$DatabaseName = &#8220;YOUR_DATABASE_NAME&#8221;;<\/div>\n<div>$link = mysql_connect($DatabaseServer, $Username, $Password) or die(&#8216;Could not connect: &#8216; . mysql_error());<\/div>\n<div>mysql_select_db($DatabaseName) or die(&#8216;Could not select database&#8217;);<\/div>\n<div>$IP = $_SERVER[&#8220;REMOTE_ADDR&#8221;]; \/\/Get the IP address<\/div>\n<div>$res = mysql_query(&#8220;SELECT country_code2,country_name FROM IPCountries WHERE IP_FROM&lt;=inet_aton(&#8216;$IP&#8217;) AND IP_TO&gt;=inet_aton(&#8216;$IP&#8217;)&#8221;);\/\/look up IP address<\/div>\n<div>$Codes = mysql_fetch_array($res); \/\/get result<\/div>\n<div>$CountryCode = $Codes[&#8216;country_code2&#8217;]; \/\/two-letter country code<\/div>\n<div>$CountryName = $Codes[&#8216;country_name&#8217;]; \/\/full country name<\/div>\n<div>echo &#8220;$CountryCode &#8211; $CountryName&#8221;; \/\/print it out, just as an example<\/div>\n<div>mysql_close($link); \/\/clean up<\/div>\n<\/div>\n<p>And what does it look like? For your IP address, this is its output:<\/p>\n<div class=\"ReverseDNSResult\">Geographical location of IP address is: <img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/ekstreme.com\/support\/flags\/unknown.png\" alt=\"Unknown country flag\" width=\"14\" height=\"14\" \/> Unknown country<\/div>\n<p>This code is used to display IP information at the PHPCounter <a href=\"http:\/\/ekstreme.com\/phpcounter\/dnscheck.php\">reverse DNS and whois tool<\/a>. (I&#8217;ll show you where to get flag icons later.)<\/p>\n<p>In this example, we simply printed out the country code and name. In a real-world example, you could use this information to target content. For example, to output content for IP addresses from France, you could use:<\/p>\n<div class=\"code\">\n<div>if($CountryCode == &#8220;FR&#8221;){<\/div>\n<div>\/\/echo contents for France<\/div>\n<div>echo &#8220;Bonjour!&#8221;<\/div>\n<div>}<\/div>\n<div>else{<\/div>\n<div>\/\/default content<\/div>\n<div>echo &#8220;Hello!&#8221;<\/div>\n<div>}<\/div>\n<div>\n<div class=\"entry\">\n<h2>Country flags<\/h2>\n<p>One cool feature is to get country flags to display. There are two places to get good-quality flags:<\/p>\n<ul>\n<li>The <a href=\"http:\/\/awstats.sourceforge.net\/#DOWNLOAD\">AWStats<\/a> package is an open source log analyzer. It comes with a full set of country flags. Download it, and the flags are in the <span class=\"code\">\\wwwroot\\icon\\flags<\/span> directory.<\/li>\n<li>A set of open source flags are published by the <a href=\"http:\/\/sourceforge.net\/projects\/flags\/\">Flags of the World<\/a> project.<\/li>\n<\/ul>\n<p>Personally, I like the AWStats flags more.<\/p>\n<h2>Closing remarks<\/h2>\n<p>Just a few comments to close off with:<\/p>\n<ul>\n<li><em>Performance:<\/em> If you have a popular website, having geotargeting code can cause serious strain. The IP database contains over 60000 rows, which is not exactly a small database. Looking through the database will stress your server.<\/li>\n<li><em>Usability:<\/em> If you geotarget languages, then make sure that there is a way to change the language by the user. An example: if you geotarget French content to France, then an English-speaking tourist in France will get French. Make sure this tourist can get to your English content if he\/she wants to!<\/li>\n<li><em>Accuracy:<\/em> The database is claimed to be 97% accurate. This is sufficient for most users, but if you want a more accurate version, download the <a href=\"http:\/\/www.maxmind.com\/app\/country\">MaxMind GeoIP Country Database<\/a> version, which is not free.<\/li>\n<\/ul>\n<p>In short, just think through your geotargeting implementation, as you would for everything else!<\/p><\/div>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Source link: http:\/\/ekstreme.com\/geotargeting\/flags-comments.php Requirements A database of worldwide IP addresses. (I&#8217;ll show you where to get one in a second.) A database, like MySQL, and a server-side programming language, like PHP. The examples in this tutorial are PHP\/MySQL based, but any combination should work. Optionally, a set of country flags &#8211; again, I&#8217;ll show where [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"close","ping_status":"close","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[],"class_list":["post-565","post","type-post","status-publish","format-standard","hentry","category-php","author-admin"],"_links":{"self":[{"href":"https:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/565","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=565"}],"version-history":[{"count":3,"href":"https:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/565\/revisions"}],"predecessor-version":[{"id":567,"href":"https:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/565\/revisions\/567"}],"wp:attachment":[{"href":"https:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=565"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=565"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=565"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}