Using the free MaxMind GeoLite database, I show you how to set up geotargeting on your website to find the country a visitor is browsing from.
Why This Guide?
This guide is tailored to newcomers whose websites maybe on shared hosting and want to understand the basics of geotargeting.
Whilst geotargeting is used on many websites, (including almost all the top 1000 traffic sites), setting it up can be tricky and can deter newcomers.
There are many approaches you can take but if your website is using shared hosting, some of these approaches can’t be taken.
It took me a little while to get a basic GeoIP country lookup working on this site. I hope this guide will help get you up to speed a bit faster.
Requirements
A server running PHP, a MySQL database and access to phpMyAdmin to administer it. If you have CPanel installed on your server, phpMyAdmin is usually linked through from there.
1 – Download The Data File
Visit Maxmind: Legacy GeoLite & download the free GeoLite Country CSV / Zip file (GeoIPCountryCSV.zip).
2 – Create A Table To Contain The GeoIP data.
In phpMyAdmin, select the database you want to create the GeoIP data table.
Click the SQL tab, copy and paste in the following query and click ‘Go’.
This will create a table called ‘geoip’ to contain the data which we will import from our downloaded .csv file.
CREATE TABLE IF NOT EXISTS `geoip` ( `ipstart` VARCHAR(50) COLLATE UTF8_GENERAL_CI NOT NULL, `ipend` VARCHAR(50) COLLATE UTF8_GENERAL_CI NOT NULL, `locid_start` INT(1) UNSIGNED ZEROFILL NOT NULL, `locid_end` INT(1) UNSIGNED ZEROFILL NOT NULL, `country_code` VARCHAR(4) COLLATE UTF8_GENERAL_CI NOT NULL, `country` VARCHAR(100) COLLATE UTF8_GENERAL_CI NOT NULL, PRIMARY KEY( `ipstart`,`ipend`, `locid_end`, `country` ) ) DEFAULT CHARSET=UTF8 COLLATE=UTF8_GENERAL_CI
This SQL Query is inspired by https://gist.github.com/aufa/8957728. I removed the ID column, which for my use, made a simple country code lookup slightly faster, and data import slightly easier.
3 – Import the GeoLite data
Open the GeoIPCountryCSV.zip file and extract the GeoIPCountryWhois.csv file. It’s this CSV file which we will import into the ‘geoip’ table.
In phpMyAdmin, select the geoip table we have just created by clicking ‘geoip’ on the left hand navigation bar. Once selected, click ‘Import’ from the top tab.
Press ‘Choose File’, select the GeoIPCountryWhois.csv file you downloaded, leave the other settings as they are and click Go.
After a moment, the data should be imported into the geoip table. If successful you should be shown a message stating that import was successful.
Double check the data was imported by clicking on the ‘geoip’ table. There should be many rows filled with data. At this point, it might be worth taking a moment to familiarize yourself with the column names and the type of data they contain.
4 – Use PHP To Determine The Visitors Country
With the PHP code below, we find the visitors IP address, use an SQL command to convert it to a number, and then compare it to a range of numbers within the geoip table to determine the name of the country the visitor is browsing from.
On the web page, the name of the visitors country is shown, alongside the time it took to generate this information.
To use this script, create a blank .php page and copy and paste in the code from below.
On line 5, put in your own details: host, username, database name, password. Save the page, (eg. geoip-test.php), upload it to your server and open the page in a web browser.
If everything is setup correctly, you should see the name of the country you are browsing from along with the page generation time.
<?php
//Start Timer
$time_start = microtime(true);
$mysqli = mysqli_connect('hostname', 'db_username', 'db_password', 'db_name');
if (!$mysqli) {
die('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
}
$user_ip = $_SERVER['REMOTE_ADDR'];
$query = "SELECT country FROM geoip WHERE INET_ATON('".$user_ip."') BETWEEN locid_start AND locid_end LIMIT 1";
$result = mysqli_query($mysqli, $query) or trigger_error("Query Failed! SQL: $query - Error: ". mysqli_error($mysqli), E_USER_ERROR);
while ($row = $result->fetch_assoc()) {
$geoip_country = $row['country'];
}
mysqli_close($mysqli);
//Finish Timer
$time_end = microtime(true);
echo 'You are browsing from: '.$geoip_country;
//Print Out Time
$time = $time_end - $time_start;
echo '<br>Page Generated In: '.$time.' seconds.';
?>
We can test this script as if you are browsing from another country without the need for a proxy server.
Select a country block IP from a site such as Nirsoft.
Replace
$user_ip = $_SERVER['REMOTE_ADDR'];
with a block IP for another country.
For example, a Belize IP is 179.42.192.0, so we can use:
$user_ip = '179.42.192.0';
to bring up this country name.
5 – Updates
If you decide that the approach shown here is suitable, and you build on this code for your own geotargeting needs, you will need to periodically update the GeoIP data to maintain a level of accuracy.
MaxMind updates the GeoLite country CSV file on the first Tuesday of each month.
Before importing this updated file, the geoip table will need to be emptied.
To empty the geoip table, click the SQL tab in phpMyAdmin, input the following query and press Go:
TRUNCATE geoip
Note – truncating the table is a faster method than selecting ‘Empty’ table, which is another option in phpMyAdmin.
Once the table is empty, import the new GeoLite CSV data file as shown in Step 3.
It is possible to update the GeoLite data set automatically (but perhaps not if you are on shared hosting) – this is beyond the scope of this article (if interested, see MaxMind GeoIp Update).
Final Thoughts
Speed – It’s important to test the speed of a GeoIp look up. If your page speed is slow, visitors may not hang around.
In such cases, you would need to consider other approaches to implementing geotargeting on your website.
Using MaxMind GeoLite data on this website, a single country look up for a visitor averages 0.0015 seconds, (your mileage may vary).
What speed is too slow for a website? That depends on your overall site speed and how long you are prepared to let visitors wait to load your webpages. For this site, I regularly run website speed tests, and aim for a sub 1 second load time.
Note: due to latency, the location of the server can have a big impact on site speed. Caching, minifying and compressing page files, using a Content Delivery Network (CDN) and a speedy dedicated server can all help site speed.
If a GeoIp lookup is too slow, there is a GeoIP extension available for PHP, and .dat files containing IP data which don’t require a database, which may make for a better setup. However, it may not be possible to take this approach if your website is on shared hosting. Note: MaxMind offers a newer database (GeoLite2) which may give a faster lookup.
Accuracy – Whilst MaxMind don’t say how accurate the GeoLite version is, I have no reason to think that this free database is unusable. MaxMind claim that their paid version is 99.9% accurate and offers extra data such as whether the visitor is using a VPN.
Good luck with your geotargeting.
