Creazione di un database IP-to-Country

Adesso, passiamo alla parte divertente, cioè al parsing del file di mapping IP-country e al relativo salvataggio dei dati nelle nostre 2 tabelle mysql

<?php
	/*
		http://www.ipinfodb.com/
		
		API KEY: bisogna registrarsi per averla
		http://lite.ip2location.com/?file=IP2LOCATION-LITE-DB1.CSV.ZIP&key=	
	*/

	// CONNESSIONE AL DB - DA INSERIRE QUI
	
	$stream_ZIPFILE = __CURL_getURL( 'http://lite.ip2location.com/?file=IP2LOCATION-LITE-DB1.CSV.ZIP&key=la-chiave-api-del-sito-ipinfodb.com', 'http://www.ipinfodb.com/download.php?file=IP2LOCATION-LITE-DB1.CSV.ZIP' );
	file_put_contents ( 'tempzipfile', $stream_ZIPFILE );
	$stream_ZIPFILE = ''; // releasing memory for this variable
	
	$zip = zip_open ( 'tempzipfile' );
	unlink ( 'tempzipfile' );
	do {
		$entry = zip_read($zip);
	} while ($entry && zip_entry_name($entry) != "IP2LOCATION-LITE-DB1.CSV");
	
	zip_entry_open($zip, $entry, "r");
	$entry_content = zip_entry_read($entry, zip_entry_filesize($entry));
	$entry_content = str_replace ( chr(13).chr(10), chr(10), $entry_content );
	
	$linee = explode ( chr(10), $entry_content );
	$entry_content = ''; // releasing memory for entry_content ( it's a long string )
	
	// flushing ip_to_country DB
	$sql = "TRUNCATE TABLE ip_to_country";
	mysql_query($sql);
	
	// parsing Text DB Lines
	foreach ( $linee as $linea ) {
		list ( $start_ip_integer, $end_ip_integer, $country_code, $country_name ) = explode ( ',', str_replace ( '"', '' , $linea ) );
		
		// inserting country into country DB  (if needed)
		$sql = "SELECT id FROM country_list WHERE country_name_en='" . $country_name . "'";
		$rs = mysql_query ( $sql );
		if ( $rs && mysql_num_rows ( $rs ) == 1 ) $idCountry = mysql_result ( $rs, 0 );
		else {
			// translating country in Italian
			$stream_TRANSLATE = __CURL_getURL( 'https://www.googleapis.com/language/translate/v2?key=la-tua-api-key-google.translate&q=' . urlencode ( $country_name ) . '&source=en&target=it', 'http://www.google.it/' );
			$stream_TRANSLATE_array = json_decode ( $stream_TRANSLATE );
			$countryname_IT = strtoupper ( $stream_TRANSLATE_array -> data -> translations[0] -> translatedText );
			
			$sql = "INSERT INTO country_list ( country_code, country_name_en, country_name_it ) VALUES ( '$country_code', '$country_name', '$countryname_IT' )";
			mysql_query ( $sql );
			
			$idCountry = mysql_insert_id();
		}
		
		$sql = "INSERT INTO ip_to_country ( ip_start_integer, ip_end_integer, country_id ) VALUES ( '$start_ip_integer', '$end_ip_integer', '$idCountry' )";
		mysql_query ( $sql );
	}


	function __CURL_getURL($url, $referer) {
		$header[0] = "Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8";
		$header[] = "Cache-Control: max-age=0";
		$header[] = "Connection: keep-alive";
		$header[] = "Keep-Alive: 300";
		$header[] = "Accept-Charset: ISO-8859-1,utf-8;q=0.7,*;q=0.7";
		$header[] = "Accept-Language: it-it,it;q=0.8,en-us;q=0.5,en;q=0.3";
		$header[] = "Pragma: ";
		$header[] = "Referer: " . $referer;

		$ch = curl_init();
		curl_setopt($ch, CURLOPT_URL, $url);
		curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
		curl_setopt($ch, CURLOPT_HTTPHEADER, $header); 
		curl_setopt($ch, CURLOPT_USERAGENT, "Mozilla/5.0 (Windows; U; Windows NT 5.1; it; rv:1.9.1.3) Gecko/20090824 Firefox/3.5.3");
		curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
		curl_setopt($ch, CURLOPT_VERBOSE, true);
		curl_setopt($ch, CURLOPT_COOKIEJAR, '/tmp/cookies.txt');
		curl_setopt($ch, CURLOPT_COOKIEFILE, '/tmp/cookies.txt');
		$result = curl_exec($ch);
		curl_close($ch);

		return $result;
	}

?>