Author |
Topic |
famaash
Starting Member
2 Posts |
Posted - 2013-06-23 : 09:47:55
|
I am in need of functionality for getting the Country name from the IP.I have to show the country in front of every unique visitor I have on my site. (Previously, I asked a question regarding a similar approach, but I changed it now.)The output I expect would be something as follows:-quote: Country Visited | No. of Times _______________________________ United States | 482 Korea | 213 Pakistan | 123 ....
The output is showing that 482 Unique visitors have come from US, 213 from Korea, and so on.To achieve this, I divided this task into two part, first getting all the IPs of the Unique user and the second part is to search two tables with country/IP information to get the country.We then aggregate data based on the top output table shown. I am going to show the queries I wrote for each part. Below, I have shown the Structure and Sample Data of the tables used.First Part - Getting Unique Visitors on my site------------------------------------------------quote: SELECT a.uid, a.hostname, COUNT( * ) AS times FROM login_activity a WHERE a.uid =83 GROUP BY a.hostname ORDER BY times
This shows the output as below. It means that the USer with ID 83 has 80 unique visits to the site.:-quote: uid | hostname | times ------------------------------------- 83 | 157.191.122.36 | 80
This is wrong as I should be getting the no. of hits for each IP. But somehow I am unable to do so.Second Part - Finding the country based on IP---------------------------------------------quote: SELECT cc, cn FROM ip NATURAL JOIN cc WHERE INET_ATON( "157.191.122.36" ) BETWEEN START AND END
This gives me the output as:- quote: cc | cn -------- US | United States
This is correct, as it's giving me the correct Country based on the IP provided.With the help of these three tables and two queries I've shown on top, I need to show the following result:-quote: Country Visited | No. of Times _______________________________ United States | 482 Korea | 213 Pakistan | 123 ....
I will be very thankful if you can help me in any way ...EDIT:-This is what I tried, but it isn't working properly and gives a same number for Visits, regardless of the IP :- quote: SELECT cn As 'Country Visited', count(0) AS 'No. of Times'FROM mslop_login_activity aINNER JOIN ip ON ( INET_ATON(a.hostname) BETWEEN START AND END )NATURAL JOIN ccGROUP BY cn
Appendix (Structure and Data of the Tables)-------------------------------------------**login_activity**---(Structure)quote: 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)quote: 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);
**cc**-------(Structure)quote: 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's large, you can download it through this link :- quote: http://d.pr/f/N3KT
**ip**------(Structure)quote: CREATE TABLE ip ( start INT UNSIGNED NOT NULL, end INT UNSIGNED NOT NULL, ci TINYINT UNSIGNED NOT NULL );
(Data)Since it's large, you can download it through this link:-quote: http://d.pr/f/wcEy.
I apologize for this long post and will be very thankful for your help. quote:
quote:
quote:
quote:
|
|
famaash
Starting Member
2 Posts |
Posted - 2013-06-23 : 15:25:40
|
This is what worked for me ...SELECT cc.cn , sum(a.visits) as total_visitsFROM ( SELECT hostname , COUNT( DISTINCT TIMESTAMP ) AS visits FROM mslop_login_activity GROUP BY hostname ) aJOIN ipON INET_ATON( a.hostname ) BETWEEN ip.START AND ip.ENDJOIN ccON cc.ci=ip.ciGROUP BY cc.cn ORDER BY total_visits DESC LIMIT 0 , 30 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-24 : 00:57:30
|
quote: Originally posted by famaash This is what worked for me ...SELECT cc.cn , sum(a.visits) as total_visitsFROM ( SELECT hostname , COUNT( DISTINCT TIMESTAMP ) AS visits FROM mslop_login_activity GROUP BY hostname ) aJOIN ipON INET_ATON( a.hostname ) BETWEEN ip.START AND ip.ENDJOIN ccON cc.ci=ip.ciGROUP BY cc.cn ORDER BY total_visits DESC LIMIT 0 , 30
So you were using MySQL?This is a ms sql server forum. So you may be better off posting in MySQL forums in future for getting MySQL solutions as we dont have much expertise on MySQL here------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|