CODEWITHSUNDEEP
mysql
Linus
Linus

Reputation: 845

mysql lookup ip address from another table with ip ranges

I have 2 tables as below

client_table
 date (timestamp)
 ip (varchar)
 value1 (int)
 value2 (int)

ip_address_table
 starting_ip (varchar)
 ending_ip (varchar)
 provider (varchar)

I am trying to lookup client_table entries with ip_address_table table to find the average per hour if the ip address is within the range. (if it's not within the range ignore the row) Below is what i have come up with

SELECT avg(value1),avg(value2)
FROM client_table
WHERE (INET_ATON(ip) BETWEEN INET_ATON(starting_ip) AND INET_ATON(ending_ip))
GROUP BY DATE(date),HOUR(date)

But, it does not give the correct output. i had a look at http://blog.jcole.us/2007/11/24/on-efficiently-geo-referencing-ips-with-maxmind-geoip-and-mysql-gis/ but struggling to write a stored function to simplify the task any help appreciated

Thanks

EDIT: My sample data is below

client_table
"date"  "ip"    "value1"    "value2"
"2012-01-10 06:38:29"   "121.1.2.3" "1049"  "301"
"2012-01-11 06:41:35"   "58.1.2.3"  "16453" "794"
"2012-01-12 06:43:50"   "101.1.2.3" "1712"  "410"
"2012-01-13 06:44:04"   "121.1.10.3"    "6594"  "863"
"2012-01-14 06:45:00"   "79.1.2.3"  "879"   "614"

ip_address_table
"starting_ip" "ending_ip" "provider"
"121.0.0.0" "121.255.255.255" provider1
"79.1.2.0" "79.1.2.255" provider1
....

Upvotes: 3

Views: 3488

Answers (1)

krakover
krakover

Reputation: 3029

I don't see you're using the ip table, maybe try something like this (not tested):

SELECT avg(value1),avg(value2)
FROM client_table c, ip_address_table ip
WHERE (INET_ATON(ip) BETWEEN INET_ATON(starting_ip) AND INET_ATON(ending_ip))
GROUP BY DATE(date),HOUR(date)

Upvotes: 4

Related Questions