I need functionality to get country name from IP.
I have to show the country in front of every unique visitor that I have on my site. (I used to ask a question about a similar approach, but now I changed it.)
The result that I expect will be as follows: -
Country Visited | No. of Times _______________________________ United States | 482 Korea | 213 Pakistan | 123 ....
The result shows that 482 unique visitors came from the USA, 213 from Korea, etc.
To achieve this, I divided this task into two parts, first getting all the IP addresses of the Unique User, and the second part - to search for two tables with information about the country / IP to get the country.
Then we summarize the data based on the displayed top displayed table. I am going to show the queries that I wrote for each part. Below I have shown the structure and examples of data used tables.
The first part - Getting unique visitors to my site
SELECT a.uid, a.hostname, COUNT( * ) AS times FROM login_activity a WHERE a.uid =83 GROUP BY a.hostname ORDER BY times
The output is shown here, as shown below. This means that the user with the identifier 83 has 80 unique visits to the site:
uid | hostname | times ------------------------------------- 83 | 157.191.122.36 | 80
This is wrong, since I have to get no. hits for each IP address. But for some reason I can’t do it.
Part Two - IP Based Country Search
SELECT cc, cn FROM ip NATURAL JOIN cc WHERE INET_ATON( "157.191.122.36" ) BETWEEN START AND END
This gives me a conclusion like:
cc | cn -------- US | United States
This is correct as it gives me the correct country based on the provided IP address.
Using these three tables and the two queries I showed above, I need to show the following result:
Country Visited | No. of Times _______________________________ United States | 482 Korea | 213 Pakistan | 123 ....
Appendix (Table structure and data)
login_activity
(Structure)
CREATE TABLE IF NOT EXISTS `mslop_login_activity` ( `aid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'The primary identifier for an activity (session).', `uid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'The mslop_users.uid corresponding to a session, or 0 for anonymous user.', `host_user_agent` varchar(256) NOT NULL DEFAULT '' COMMENT '$_SERVER["HOST_USER_AGENT"] string. This can be used with get_browser() in PHP.', `hostname` varchar(128) NOT NULL DEFAULT '' COMMENT 'The IP address that was used for this session.', `timestamp` int(11) NOT NULL DEFAULT '0' COMMENT 'The UNIX timestamp when the session was started.', PRIMARY KEY (`aid`), KEY `aid` (`aid`), KEY `uid` (`uid`), KEY `timestamp` (`timestamp`) );
(Data)
INSERT INTO `mslop_login_activity` (`aid`, `uid`, `host_user_agent`, `hostname`, `timestamp`) VALUES (1, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8; rv:19.0) Gecko/20100101 Firefox/19.0', '172.24.1.143', 1363038356), (873, 4, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/534.59.8 (KHTML, like Gecko) Version/5.1.9 Safari/534.59.8', '157.191.122.36', 1369773601), (883, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; Tablet PC 2.0)', '157.191.122.36', 1369774959), (893, 83, 'Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.31 (KHTML, like Gecko) Chrome/26.0.1410.64 Safari/537.31', '157.191.122.36', 1369818602), (903, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; GTB7.4; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; Tablet PC 2.0)', '157.191.122.36', 1369838690), (913, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; GTB7.4; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; Tablet PC 2.0; AskTbORJ/5.15.23.36191)', '157.191.122.36', 1369840224), (923, 83, 'Mozilla/5.0 (Windows NT 6.1; rv:21.0) Gecko/20100101 Firefox/21.0', '157.191.122.36', 1369841748), (933, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; Tablet PC 2.0)', '157.191.122.36', 1370255417), (943, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E)', '157.191.122.36', 1370258059), (953, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E)', '157.191.122.36', 1370258060), (963, 93, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; AskTbORJ/5.15.15.36191)', '157.191.122.36', 1370299827), (973, 93, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; AskTbORJ/5.15.15.36191)', '157.191.122.36', 1370299852), (983, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/534.59.8 (KHTML, like Gecko) Version/5.1.9 Safari/534.59.8', '157.191.122.36', 1370299899), (993, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/534.59.8 (KHTML, like Gecko) Version/5.1.9 Safari/534.59.8', '157.191.122.36', 1370299920), (1003, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/534.59.8 (KHTML, like Gecko) Version/5.1.9 Safari/534.59.8', '157.191.122.36', 1370299995), (1013, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370319811), (1023, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370319835), (1033, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370319939), (1043, 83, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370320312), (1053, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370320640), (1063, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370320713), (1073, 83, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370363702), (1083, 83, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370363987), (1093, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370364021), (1103, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370364028), (1113, 83, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370365896), (1123, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370366087), (1133, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/534.59.8 (KHTML, like Gecko) Version/5.1.9 Safari/534.59.8', '157.191.122.36', 1370367070), (1143, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370368567), (1153, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370368575), (1163, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; Tablet PC 2.0)', '157.191.122.36', 1370379345), (1173, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; GTB7.4; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; Tablet PC 2.0)', '157.191.122.36', 1370427302), (1183, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; .NET CLR 1.1.4322)', '157.191.122.36', 1370503422), (1193, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/534.59.8 (KHTML, like Gecko) Version/5.1.9 Safari/534.59.8', '157.191.122.36', 1370534329), (1203, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; Tablet PC 2.0)', '157.191.122.36', 1370866207), (1213, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; .NET CLR 1.1.4322)', '157.191.122.36', 1370871353), (1223, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; chromeframe/26.0.1410.43; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; Tablet PC 2.0)', '157.191.122.36', 1370877702), (1233, 83, 'Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.110 Safari/537.36', '157.191.122.36', 1371015245), (1243, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; Tablet PC 2.0)', '157.191.122.36', 1371069245), (1253, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E)', '157.191.122.36', 1371117509), (1263, 83, 'Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.110 Safari/537.36', '157.191.122.36', 1371137716), (1273, 83, 'Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0)', '157.191.122.36', 1371197327), (1283, 83, 'Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.110 Safari/537.36', '157.191.122.36', 1371427531), (1293, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E)', '157.191.122.36', 1371552899), (1303, 83, 'Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E)', '157.191.122.36', 1371635304), (1313, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/534.59.8 (KHTML, like Gecko) Version/5.1.9 Safari/534.59.8', '157.191.122.36', 1371770752), (1323, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/534.59.8 (KHTML, like Gecko) Version/5.1.9 Safari/534.59.8', '157.191.122.36', 1371771394), (1333, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.110 Safari/537.36', '157.191.122.36', 1371771647), (1343, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/536.26.17 (KHTML, like Gecko) Version/6.0.2 Safari/536.26.17', '157.191.122.36', 1371772611), (1353, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/536.26.17 (KHTML, like Gecko) Version/6.0.2 Safari/536.26.17', '157.191.122.36', 1371772626), (1363, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.110 Safari/537.36', '157.191.122.36', 1371772931), (1373, 93, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SIMBAR={37EF0ACB-CCB3-11E2-9611-E006E6BAE768}; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; AskTbORJ/5.15.15.36191)', '157.191.122.36', 1371782438), (1383, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/534.59.8 (KHTML, like Gecko) Version/5.1.9 Safari/534.59.8', '157.191.122.36', 1371782562), (1393, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.116 Safari/537.36', '157.191.122.36', 1371790767), (1403, 83, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/536.26.17 (KHTML, like Gecko) Version/6.0.2 Safari/536.26.17', '157.191.122.36', 1371834780);
ss
(Structure)
CREATE TABLE cc ( ci TINYINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, cc CHAR(2) NOT NULL, cn VARCHAR(50) NOT NULL );
(Data)
Since it is large, you can download it from this link: -
http://d.pr/f/N3KT
f
(Structure)
CREATE TABLE ip ( start INT UNSIGNED NOT NULL, end INT UNSIGNED NOT NULL, ci TINYINT UNSIGNED NOT NULL );
(Data)
Since it is large, you can download it from this link: -
http://d.pr/f/wcEy .