Toxic Byte

First things first, you need to download the database itself from maxmind. Then upload this to your website to import into the database. There are ways to import this directly using phpMyAdmin’s upload tool, but when I tried my connection kept timing out or other errors occured. I also thing it’s better to wrap up the database creation and upload into a script so that you can simply blat it over the top when MaxMind release an updated version of the database (which they do monthly I think).

Okay, where was I? Yes, so we’ve uploaded the GeoIPCountryWhois.csv file to your website’s space. Personally I created a data folder outsite the public_html folder, because there’s no point making it public.

Once that is uploaded it’s time to make a script to do all the work. Normally I’d do this in perl, but seeing as I code in perl all day long, I thought I’d do it in PHP for the sake of it. You need to create a php file called, let’s say, install_geoip.php (I’ve created my in a private folder that’s passworded by apache because you don’t want every man and his dog running this for you).

There’s probably 100 better ways to do this, but this is how I did. You’ll need to set up a database and a user to do this in.

<?php
/*
 * install_geoip.php - For installing the geoip database from www.maxmind.com
 * - Chris Andrews (http://www.toxicbyte.com/)
 *
 * History:
 * 30/08/2006
 *  - Started off, Christ only knows where
 */

$db_host = "xxxxxx";
$db_user = "xxxxxx";
$db_pass = "xxxxxx";
$db_name = "xxxxxx";

$file_location = "/path/to/GeoIPCountryWhois.csv";

// Drop the table and it’s contents from the database
$sql_drop_table = "DROP TABLE IF EXISTS geo_ip;";

/*
 Create the table that will contain the following:
    - Beginning IP Address
    - Ending IP Address
    - Beginning IP Number
    - Ending IP Number
    - ISO 3166 Country Code
    - Country Name
*/
$sql_create_table = <<<SQL
CREATE TABLE geo_ip
(
  begin_ip      CHAR(15)     NOT NULL,
  end_ip        CHAR(15)     NOT NULL,
  begin_num     INT UNSIGNED NOT NULL,
  end_num       INT UNSIGNED NOT NULL,
  country_code  CHAR(2)      NOT NULL,
  country_name  VARCHAR(50)  NOT NULL
);
SQL;

// Insert the row into the database using bound parameters
$sql_insert_row = <<<SQL
INSERT INTO geo_ip
    (begin_ip, end_ip, begin_num, end_num, country_code, country_name)
VALUES
    (‘%s’, ‘%s’, ‘%s’, ‘%s’, ‘%s’, ‘%s’);
SQL;

$sql_drop_ip_row_begin_ip = <<<SQL
ALTER TABLE geo_ip DROP begin_ip;
SQL;

$sql_drop_ip_row_end_ip = <<<SQL
ALTER TABLE geo_ip DROP end_ip;
SQL;

$dbh = mysql_connect($db_host, $db_user, $db_pass)
    or die(‘I cannot connect to the database because: ‘ . mysql_error());

mysql_select_db($db_name);
?>
Dropping old table<br />
<?php
$result = mysql_query($sql_drop_table)
    or die(‘Query failed: ‘ . mysql_error());
?>

Creating new table<br />
<?php
$result = mysql_query($sql_create_table)
    or die(‘Query failed: ‘ . mysql_error());
?>

Open file for reading<br />
<?php
    if (! $input_file = fopen($file_location, "r"))
    {
        print "No openzee file!";
        exit;
    }
?>

Reading file, line by line into database<br />
<?php
$break_at_counter = 0;
$break_at_value = -1;
$counter = 0;
while (($data = fgetcsv($input_file, 1000, ",")) !== FALSE)
{
    if ($break_at_value > 0 and $break_at_counter++ >= $break_at_value)
    {
        break;
    }    

    $row = array_map(‘mysql_real_escape_string’, $data);
    $result = mysql_query(vsprintf($sql_insert_row, $row))
        or die(‘Query failed: ‘ . mysql_error() . "<br />" . vsprintf($sql_insert_row, $row));

    #vprintf("Done: %s, %s, %s, %s, %s, %s<br />", $row);
    if ($counter++ % 100 == 0)
    {
        print ". ";
    }
}
print "<br />nDone!<br />n";
?>

Dropping unneeded columns<br />
<?php
$result = mysql_query($sql_drop_ip_row_begin_ip)
    or die(‘Query failed: ‘ . mysql_error());
$result = mysql_query($sql_drop_ip_row_end_ip)
    or die(‘Query failed: ‘ . mysql_error());
?>

Closing the database<br />
<?php
    mysql_close($dbh);
?>

Technorati Tags: geotargetting, php, maxmind, geoip

Post a Comment | Trackback URI

New! We've just started a message board and discussion forum over on the Toxic Byte Technical Forum. It's a bit lonely over there at the moment, but feel free to join in and participate!