ip-2-country

Tabel definitie:

[code]
CREATE TABLE `ip2country` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `range_start` int(10) unsigned NOT NULL default '0',
  `range_end` int(10) unsigned NOT NULL default '0',
  `country_code` varchar(32) NOT NULL,
  `country_name` varchar(32) NOT NULL,
  PRIMARY KEY  (`id`)
);
[/code]

<?php

	// Database
	mysql_connect('localhost', 'user', 'pass') or die('Cannot connect to server.');
	mysql_select_db('dbname') or die('Cannot find DB on server.');


	// Visit www.ip-to-country.com for the latest ip-to-country translations
	function ip2country($ip)
	{
		$ip_long = sprintf("%u", ip2long($ip));
		$sql = "SELECT * FROM `ip2country` WHERE (`range_start` <= '" . $ip_long . "') AND (`range_end` >= '" . $ip_long . "') LIMIT 1;";
		$rs = mysql_query($sql) or die('Error in query');

		if(mysql_num_rows($rs) > 0)
		{
			return mysql_fetch_assoc($rs);
		}
		else
		{
			return false;
		}
	}

	$ip = $_SERVER['REMOTE_ADDR'];
	if($array = ip2country($ip))
	{
		echo $ip . ' is found in ' . $array['country_name'];
	}
	else
	{
		echo 'Cannot find a country for: ' . $ip;
	}

?>



Op verzoek: Import script (voor het originele bestand, NIET voor m'n SQL bestand).

<?php

	define('LF', "\n", true);

	// Parse a CSV data to a 2D array
	function csvToArray($data)
	{
		// output
		$csv = array();
		$line = array();

		$escaped = false; // Flag: escape char
		$quoted = false; // Flag: quoted string

		$buffer = ''; // Buffer (quoted values)
		$junk = ''; // Junk buffer (unquoted values)

		for($i = 0; $i < strlen($data); $i++)
		{
			$char = $data[$i];

			if($quoted)
			{
				if(($char == '\\') && ($escaped === false))
				{
					// Set flags
					$escaped = true;
				}
				elseif(($char == '"') && ($escaped === false))
				{
					// Set flags
					$quoted = false;
					$escaped = false;
				}
				else
				{
					// Add char to buffer
					$buffer .= $char;

					// Set flags
					$escaped = false;
				}
			}
			else
			{
				if($char == LF) // Start a new line
				{
					if(trim(strlen($buffer)) > 0)
					{
						// Add buffer to line
						$line[] = $buffer;

						// Clear buffer
						$buffer = '';
					}
					else
					{
						$junk = trim($junk);

						// Add junk to line (possible unquoted values?)
						$line[] = $junk;
					}

					// Clear junk
					$junk = '';

					// Add line to CSV
					$csv[] = $line;

					// Clear line
					$line = array();
				}
				elseif($char == '"') // Start new value
				{
					// Set flags
					$quoted = true;
				}
				elseif($char == ',')
				{
					if(trim(strlen($buffer)) > 0)
					{
						// Add buffer to line
						$line[] = $buffer;

						// Clear buffer
						$buffer = '';
					}
					else
					{
						$junk = trim($junk);

						// Add junk to line (possible unquoted values?)
						$line[] = $junk;
					}

					// Clear junk
					$junk = '';
				}
				else // Add to junk char
				{
					$junk .= $char;
				}
			}
		}

		// Clean up
		if(strlen($buffer) > 0)
		{
			// Add buffer to line
			$line[] = $buffer;

			// Clear buffer
			$buffer = '';
		}
		else
		{
			$junk = trim($junk);

			// Add junk to line (possible unquoted values?)
			$line[] = $junk;
		}

		$csv[] = $line;

		return $csv;
	}


	// Import ip-2-country file to database
	function import($file)
	{
		$csv = csvToArray(file_get_contents($file));

		for($i = 0; $i < sizeof($csv); $i++)
		{
			$sql = "INSERT INTO `ip2country` (`range_start`, `range_end`, `country_code`, `country_name`) VALUES ('" . $csv[$i][0] . "', '" . $csv[$i][1] . "', '" . $csv[$i][2] . "', '" . ucwords(strtolower($csv[$i][4])) . "');";
			mysql_query($sql);
		}
	}

	import(dirname(__FILE__) . '/ip2country.csv');

?>