<?php // LOAD
error_reporting(E_ALL);
// DEMONSTRATE HOW TO LOAD THE IP-TO-COUNTRY DATA FROM MAXMIND GEOLITE
// http://geolite.maxmind.com/download/geoip/database/GeoIPCountryCSV.zip
// ON JULY 1, 2010 THE FILE HAD 129,763 ROWS
// ON APRIL 25, 2013 THE FILE HAD 167,637 ROWS
// PUT THE PATH/NAME OF THE DOWNLOADED CSV FILE HERE
$csv = 'path/to/GeoIPCountryWhois.csv';
// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";
// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);
// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
$err
= "CONNECT FAIL: "
. $mysqli->connect_errno
. ' '
. $mysqli->connect_error
;
trigger_error($err, E_USER_ERROR);
}
// REMOVE THE OLD DATA BASE TABLE, IF ANY (IGNORE ERRORS)
$mysqli->query('DROP TABLE ip2country');
// CREATE THE NEW TABLE
$sql
=
" CREATE TABLE ip2country
( ip_string_lo VARCHAR(16) NOT NULL
, ip_string_hi VARCHAR(16) NOT NULL
, ip_number_lo INT UNSIGNED NOT NULL
, ip_number_hi INT UNSIGNED NOT NULL
, country_code VARCHAR(2) NOT NULL
, country_name VARCHAR(64) NOT NULL
)
"
;
if (!$res = $mysqli->query($sql))
{
$err
= "QUERY FAIL: $sql "
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
;
trigger_error($err, E_USER_ERROR);
}
// OPEN THE CSV FILE AND LOAD THE TABLE
$fp = fopen($csv, 'r');
if (!$fp) trigger_error("FAIL: UNABLE TO OPEN $csv". E_USER_ERROR);
// READ EACH ROW OF THE IP AND COUNTRY INFORMATION
$kount = 0;
while (!feof($fp))
{
$row = fgetcsv($fp);
// IGNORE ERRORS OR EMPTY RECORDS
if (!$row) continue;
// COUNT VALID RECORDS
$kount++;
// IF COUNTRY IS LIKE Korea, Republic of - RECONSTRUCT THE NAME
if (strpos($row[5], ','))
{
$x = explode(',', $row[5]);
$row[5] = trim($x[1]) . ' ' . $x[0];
}
// ESCAPE THE DATA FOR SAFE USE IN THE QUERY
foreach ($row as $key => $val) { $row[$key] = $mysqli->real_escape_string($val); }
// CONSTRUCT AND RUN A QUERY TO INSERT THE DATA
$sql
=
"
INSERT INTO ip2country
( ip_string_lo, ip_string_hi, ip_number_lo, ip_number_hi, country_code, country_name ) VALUES
( '$row[0]', '$row[1]', '$row[2]', '$row[3]', '$row[4]', '$row[5]' )
"
;
if (!$res = $mysqli->query($sql))
{
$err
= "QUERY FAIL: $sql "
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
;
trigger_error($err, E_USER_ERROR);
}
}
echo "IP-TO-COUNTRY TABLE LOADED WITH " . number_format($kount) . " RECORDS<br/>";
// ALTER THE TABLE TO ADD INDEXES
$mysqli->query("ALTER TABLE `ip2country` ADD INDEX ( `ip_number_lo` )" );
$mysqli->query("ALTER TABLE `ip2country` ADD INDEX ( `ip_number_hi` )" );
// ALL DONE.
die("INDEXING COMPLETE");
<?php // REST
error_reporting(E_ALL);
// DEMONSTRATE HOW TO USE THE IP-TO-COUNTRY DATA FROM MAXMIND GEOLITE
// A RESTful SERVICE TO LOOK UP THE COUNTRY INFORMATION FOR A GIVEN IP ADDRESS
// http://www.maxmind.com/app/geoip_country
// ON JULY 1, 2010 THE FILE HAD 129,763 ROWS
// ON APRIL 25, 2013 THE FILE HAD 167,637 ROWS
// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";
// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);
// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
$err
= "CONNECT FAIL: "
. $mysqli->connect_errno
. ' '
. $mysqli->connect_error
;
trigger_error($err, E_USER_ERROR);
}
// IF NO IP ADDRESS IS GIVEN, USE THE REMOTE ADDRESS OF THE CLIENT
$ip = empty($_GET["ip"]) ? $_SERVER["REMOTE_ADDR"] : $_GET["ip"];
// CONVERT TO BINARY - SEE http://php.net/manual/en/function.ip2long.php
$x = ip2long($ip);
if ($x === FALSE) die('ERROR: INVALID IP ADDRESS');
// IP ADDRESSES ARE UNSIGNED AND MAY RETURN NEGATIVE VALUES
$ip_number = sprintf('%u', $x);
// QUERY FOR THE BINARY VALUE
$sql
=
"
SELECT country_code, country_name
FROM ip2country
WHERE $ip_number
BETWEEN ip_number_lo
AND ip_number_hi
LIMIT 1
"
;
if (!$res = $mysqli->query($sql))
{
$err
= "QUERY FAIL: $sql "
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
;
trigger_error($err, E_USER_ERROR);
}
// DID WE FIND ANYTHING?
$num = $res->num_rows;
if (!$num) die("ERROR: NO DATA FOUND");
// YES, WE FOUND THE COUNTRY
$row = $res->fetch_object();
die("$row->country_code,$row->country_name");
<?php // DENY/ALLOW
error_reporting(E_ALL);
// DEMONSTRATE HOW TO USE THE IP-TO-COUNTRY DATA FROM MAXMIND GEOLITE
// DENY/ALLOW WEB PAGE ACCESS BASED ON THE COUNTRY INFORMATION FOR A GIVEN IP ADDRESS
// http://www.maxmind.com/app/geoip_country
// ON JULY 1, 2010 THE FILE HAD 129,763 ROWS
// ON APRIL 25, 2013 THE FILE HAD 167,637 ROWS
// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";
// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);
// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
$err
= "CONNECT FAIL: "
. $mysqli->connect_errno
. ' '
. $mysqli->connect_error
;
trigger_error($err, E_USER_ERROR);
}
// AN ARRAY OF COUNTRY CODES
$countries = array
( 'US'
, 'CA'
, 'AU'
, 'NZ'
, 'GB'
)
;
// CONVERT IP TO BINARY - SEE http://php.net/manual/en/function.ip2long.php
$x = ip2long($_SERVER["REMOTE_ADDR"]);
if ($x === FALSE) die('ERROR: INVALID IP ADDRESS');
// IP ADDRESSES ARE UNSIGNED AND MAY RETURN NEGATIVE VALUES
$ip_number = sprintf('%u', $x);
// QUERY FOR THE BINARY VALUE
$sql
=
"
SELECT country_code
FROM ip2country
WHERE $ip_number
BETWEEN ip_number_lo
AND ip_number_hi
LIMIT 1
"
;
// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res = $mysqli->query($sql))
{
$err
= "QUERY FAIL: "
. $sql
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
;
trigger_error($err, E_USER_ERROR);
}
// DID WE FIND ANYTHING?
$num = $res->num_rows;
if ($num)
{
// YES, WE FOUND THE COUNTRY
$row = $res->fetch_object();
if (in_array($row->country_code, $countries))
{
echo "YOU ARE FROM <b>$row->country_code</b> SO YOU CAN SEE THIS PAGE";
}
else
{
die("YOU CANNOT SEE THIS PAGE");
}
}
else
{
die("YOU CANNOT SEE THIS PAGE");
}
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (7)
Author
Commented:And to quote http://www.maxmind.com/app/country, "$50 site license fee and $12 per month of updates."
I have never seen GeoLite return the wrong country, but if greater accuracy is an important goal, then the paid version is probably a good value. As is true with most things, you only get what you pay for!
Since this article was written in mid-2010, I have found another service that I have used with satisfactory results: http://www.geoio.com/
Commented:
Thanks
Author
Commented:Commented:
Commented:
Thank you.
View More