Page 1 of 1
php code to find location from IP addresses in mysql table
Posted: Wed Jul 27, 2011 11:56 pm
by breaktech
Hi,
I have got a database of users for a mobile sites which consists of a column consisting of IP addresses of each hit of user. Now I need to write a php code which will identify (approximately) the address of users from where they are accessing it. so that we can cound how many users are from same place accessing the site.
the database is in mysql.
Thank you.
Re: php code to find location from IP addresses in mysql tab
Posted: Thu Jul 28, 2011 9:02 am
by Celauran
What do you have so far? Where are you running into problems?
Re: php code to find location from IP addresses in mysql tab
Posted: Thu Jul 28, 2011 10:44 pm
by breaktech
I have database.. I am trying to write code to work on geolocation API's so that I can create a new table of from which address how many hits have been done to the site.. I am a newbie to php so I don't have idea how to use that API to get the location from IP addresses I have in my database.
Re: php code to find location from IP addresses in mysql tab
Posted: Thu Jul 28, 2011 11:33 pm
by greyhoundcode
What geolocation API are you using? There are probably a number of tutorials out there, such as
this one for
NetGeo, but if we don't know what the API is I'm not sure how you expect us to help.
You must also have a basic concept in terms of how you will handle the results of the geolocation query. Are you going to put them in another table, or another column in the same table, or something completely different?
What sort of stats are you trying to derive exactly, and at what level(s) of detail - visitors per country, per region, per town?
Re: php code to find location from IP addresses in mysql tab
Posted: Fri Jul 29, 2011 12:27 am
by breaktech
Ok, Sorry for not putting my query clearly.
I want to add a column to the same table in which I already have a coulmn containing IP addresses named "ip". Now this new column I would like to put city and country of the ip.
Now I have got the php code from the link you have given above
http://pastebin.com/yke68MK4 . Now how would I write a php code so that it will take ip from the "ip" column and result with city and/or country in new column added?
after this my aim is to make a different table in which there will be two column one with city and one with no of hits from the same city.
so in city column there will be no repeated entry.
Hope I am clear enough this time.
Thank you!
Re: php code to find location from IP addresses in mysql tab
Posted: Fri Jul 29, 2011 10:25 am
by greyhoundcode
Sure, though NetGeo was just an example that came to mind ... it's probably not ideal (take a look at the info on their homepage). Perhaps a commercial web service might be a safer bet?
The function you placed in pastebin has a few issues anyway: the mode parameter for the call to fopen() ought to be in quotes - it should be a string - and it doesn't handle failure too nicely.
Code: Select all
function getLocationCaidaNetGeo($ip)
{
$NetGeoURL = "http://netgeo.caida.org/perl/netgeo.cgi?target=".$ip;
if($NetGeoFP = fopen($NetGeoURL, r))
{
ob_start();
fpassthru($NetGeoFP);
$NetGeoHTML = ob_get_contents();
ob_end_clean();
fclose($NetGeoFP);
}
preg_match ("/LAT:(.*)/i", $NetGeoHTML, $temp) or die("Could not find element LAT");
$location[0] = $temp[1];
preg_match ("/LONG:(.*)/i", $NetGeoHTML, $temp) or die("Could not find element LONG");
$location[1] = $temp[1];
return $location;
}
Re: php code to find location from IP addresses in mysql tab
Posted: Mon Aug 01, 2011 6:29 am
by breaktech
natgeo.caida.org is not working for me..
Re: php code to find location from IP addresses in mysql tab
Posted: Mon Aug 01, 2011 7:44 am
by breaktech
I got this API Php script to get city and country location.
<?php
include('ip2locationlite.class.php');
//Load the class
$ipLite = new ip2location_lite;
$ipLite->setKey('<your_api_key>');
//Get errors and locations
$locations = $ipLite->getCity($_SERVER['REMOTE_ADDR']);
$errors = $ipLite->getError();
//Getting the result
echo "<p>\n";
echo "<strong>First result</strong><br />\n";
if (!empty($locations) && is_array($locations)) {
foreach ($locations as $field => $val) {
echo $field . ' : ' . $val . "<br />\n";
}
}
echo "</p>\n";
//Show errors
echo "<p>\n";
echo "<strong>Dump of all errors</strong><br />\n";
if (!empty($errors) && is_array($errors)) {
foreach ($errors as $error) {
echo var_dump($error) . "<br /><br />\n";
}
} else {
echo "No errors" . "<br />\n";
}
echo "</p>\n";
..............................
Now I need to query in such a way that the script fetches ip address from the table of my database with filed "ip" and I want to make new table of two rows with the ip and the city/country.
Please help
Re: php code to find location from IP addresses in mysql tab
Posted: Tue Aug 02, 2011 2:58 pm
by greyhoundcode
breaktech wrote:I got this API Php script to get city and country location.
breaktech wrote:Now I need to query in such a way that the script fetches ip address from the table of my database with filed "ip" and I want to make new table of two rows with the ip and the city/country. Please help
Remember to wrap up your PHP code when you post it on the forum (use the
PHP Code button) - it makes it a lot easier to read.
So: what sort of database are you using (MySQL, Access, a flat file of some description?) and how are your tables structured? And what part is it you need help with exactly - connecting to the database from your PHP app, writing the SQL queries, or what?
(edit)
OK, so on re-reading I realize you already stated you are using MySQL and that one table consists of a single column. But, still, it would be helpful if you were more specific in terms of what you are struggling with.
Re: php code to find location from IP addresses in mysql table
Posted: Wed Aug 04, 2021 2:57 am
by Rameez
You can use ipgeolocation.io PHP SDK to get the location information from an IP address. Here is the code:
<?php
$apiKey = "PUT_YOUR_API_KEY_HERE";
$ip = "CLIENT_IP_ADDRESS";
$location = get_geolocation($apiKey, $ip);
$decodedLocation = json_decode($location, true);
echo "<pre>";
print_r($decodedLocation);
echo "</pre>";
function get_geolocation($apiKey, $ip, $lang = "en", $fields = "*", $excludes = "") {
$url = "
https://api.ipgeolocation.io/ipgeo?apiK ... .$excludes;
$cURL = curl_init();
curl_setopt($cURL, CURLOPT_URL, $url);
curl_setopt($cURL, CURLOPT_HTTPGET, true);
curl_setopt($cURL, CURLOPT_RETURNTRANSFER, true);
curl_setopt($cURL, CURLOPT_HTTPHEADER, array(
'Content-Type: application/json',
'Accept: application/json',
'User-Agent: '.$_SERVER['HTTP_USER_AGENT']
));
return curl_exec($cURL);
}
?>