Lookup CIDR on database with ip adress and take the closesd match

Question

For my system i like to make a small country location detection. So i found this database: https://github.com/datasets/geoip2

There are values like:

network,geoname_id,continent_code,continent_name,country_iso_code,country_name,is_anonymous_proxy,is_satellite_provider
"41.74.160.0/20","49518","AF","Africa","RW","Rwanda","0","0"
"41.77.160.0/22","49518","AF","Africa","RW","Rwanda","0","0"

If i get an ip adress like 42.76.160.0 from my ip getenv function how can i lookup this ip with php & pdo to match the right value from database.

EDIT:
Well meantime i found a solution:
I converted the cidr into a normal ip adress and do lookups like:

            SELECT `country_iso_code`, `country_name`
            FROM `GeoIP`
            WHERE inet_aton(network) <= inet_aton(:ipadress)
            ORDER BY inet_aton(network) DESC
            LIMIT 1

On my server i get additionally ~50-75ms higher content loads, but it's only for a small part of my site. (It doesn't make much difference to set up a table with inet_aton applied already )

There is a module for PHP you can use to gather GeoIP data for an IP address, instead of doing it manually. http://php.net/manual/en/book.geoip.php — S. Dev, May 03 '18 at 08:30
@S.Dev My nginx does not have GeoIP support, but Plesk probably will include that sometimes. Meantime i have to do this at my own. — delato468, May 03 '18 at 08:38
Here a user on the PHP site has provided a method to check if an IP exists in the CIDR subnet. You could iterate through your database until you find a match, though it will probably be slow. http://php.net/manual/en/ref.network.php#74656 — S. Dev, May 03 '18 at 08:49
@S.Dev Hi but the problem is if the ip if mentioned above isnt in the subnet but close to it or between 2 values. And it would nice if i could fetch them from my query instead of running php code. — delato468, May 03 '18 at 08:58

Lookup CIDR on database with ip adress and take the closesd match

0 Answers0