mysql lookup ip address from another table with ip ranges

Question

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 0…

Accepted Answer

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)
```

mysql lookup ip address from another table with ip ranges

Solution

Problem