ip2country
Tabel definitie:
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
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`)
);
`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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
<?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;
}
?>
// 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).
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
<?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');
?>
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');
?>