mysql geoip

[Destramic]

hey guys ive decided to use geoip via my database on my server.

 

now i've download blocks.cvs and location.cvs from:

http://geolite.maxmind.com/download/geoip/database/GeoLiteCity_CSV/GeoLiteCity-latest.zip

but when trying to load block.cvs in excel it says:

 

 

Failer to load completly

 

does anyone know of another source where i can that/these files from please?

 

thank you

[QuickOldCar]

It's a csv file not cvs

 

Use load data infile and import to a mysql database or follow this tutorial how to import those exact 2 files

[Destramic]

that worked beautifully thank you...and yeah i meant CSV.

 

what im trying to achive is:

 

1. to get longitude and latitude via users ip

2. and to get city by longitude and latitude

 

i've tried to google for sql querys but couldnt find what i was after if you coud help or point me in the right direction.

 

thanks again

[QuickOldCar]

This should be able to get you their ip

$ip = getenv('HTTP_CLIENT_IP')?:
getenv('HTTP_X_FORWARDED_FOR')?:
getenv('HTTP_X_FORWARDED')?:
getenv('HTTP_FORWARDED_FOR')?:
getenv('HTTP_FORWARDED')?:
getenv('REMOTE_ADDR');

if (strstr($ip, ', ')) {
    $ips = explode(', ', $ip);
    $ip = $ips[0];
}

They have documentation on the maxmind site.

http://dev.maxmind.com/geoip/legacy/csv/

 

The api from github may be of use to you

https://github.com/maxmind/geoip-api-php

 

I may as well mention this

http://php.net/manual/en/ref.geoip.php

Edited February 1, 2015 by QuickOldCar

[Destramic]

thank you...although i havent got the geoip extension installed on my localhost/server so im uable to use it....thats why i went down the route of mysql.

 

here is the query i'm using...but when using it doesnt return  a city (which is the main reason i need this)

SELECT l.country, 
       l.region, 
       l.city, 
       l.postal_code, 
       l.longitude, 
       l.latitude, 
       l.dma_code, 
       l.area_code 
FROM blocks b JOIN locations l ON l.location_id = b.location_id
WHERE  INET_ATON('95.146.187.2') BETWEEN b.ip_start AND b.ip_end LIMIT 1

now ive downloaded the free blocks and locations....how am i able to get what i need?...do i need to pay for the service with maxmind?

 

thank you for your help

[QuickOldCar]

There is a few ways to go about getting the data from your own database, have to look around the one for your csv you are using.

It's claimed some queries aren't that efficient and some others are better.

 

If you use it locally is no need to pay for their service.

With the external api's to the maxmind site you do, that's because they are always getting updated or improved upon using their database.

[Destramic]

well ive been fiddling about with for a few days now to see if i can accually get a result which returns my country...reason being because i get a city result when searching on maxmind and other geo ip site.

 

i've even tried this:

SELECT l.country, l.city, l.postal_code, l.latitude, l.longitude, l.dma_code, l.area_code
FROM blocks b
INNER JOIN locations l ON b.location_id = l.location_id
WHERE MBRCONTAINS(b.ip_polygon, POINTFROMWKB(POINT(INET_ATON('95.146.187.2'), 0)));

is there any other methods of me getting the users city?...if not a suppose i could go down the route of the user inputting it.

 

and when searching locations table for my city "Cwmbran" i can find it, but my longitude and latitude from my ip is like 1 out on each

 

thank you

Edited February 5, 2015 by Destramic