php: showing my country based on my IP, mysql optimized
- by andufo
I'm downloaded WIPmania's worldip table from http://www.wipmania.com/en/base/ -- the table has 3 fields and around 79k rows:
startip // example: 3363110912
endip // example: 3363112063
country // example: AR (Argentina)
So, lets suppose i'm in Argentina and my IP address is: 200.117.248.17
1) I use this function to convert my ip to long
function ip_address_to_number($ip) {
if(!$ip) {
return false;
} else {
$ip = split('\.',$ip);
return($ip[0]*16777216 + $ip[1]*65536 + $ip[2]*256 + $ip[3]);
}
}
2) I search for the proper country code by matching the long converted ip:
$sql = 'SELECT * FROM worldip WHERE '.ip_address_to_number($_SERVER['REMOTE_ADDR']).' BETWEEN startip AND endip';
which is equivalent to:
SELECT country FROM worldip WHERE 3363174417 BETWEEN startip AND endip (benchmark: Showing rows 0 - 0 (1 total, Query took 0.2109 sec))
Now comes the real question.
What if another bunch of argentinian guys also open the website and they all have these ip addresses:
200.117.248.17
200.117.233.10
200.117.241.88
200.117.159.24
Since i'm caching all the sql queries; instead of matching EACH of the ip queries in the database, would it be better (and right) just to match the 2 first sections of the ip by modifying the function like this?
function ip_address_to_number($ip) {
if(!$ip) {
return false;
} else {
$ip = split('\.',$ip);
return($ip[0]*16777216 + $ip[1]*65536);
}
}
(notice that the 3rd and 4th splitted values of the IP have been removed).
That way instead of querying these 4 values:
3363174417
3363170570
3363172696
3363151640
...all i have to query is: 3363110912 (which is 200.117.0.0 converted to long).
Is this right? any other ideas to optimize this process? Thanks!
