FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Newbie PHP/MySQL question





Bikerman
Hi folks,
I have a problem which I hope someone can help me with. Apologies in advance for the 'newbi-ness' - I'm a systems manager, not a programmer and it is many moons since I last coded in anger (and that would be with Fortran V).

Outline:
The IP address and other user details are captured, on my (Joomla) site, into a MySQL table called vvisitorcounter. What I want to do is add geolocation information to this table. I have installed and tested a PHP/PEAR add-on to do the actual geolocation (called GeoIP) and it seems to work fine. The server is running PHP 5.3 and MySQL 14.2.

Problem:
I want to run a script to go through the table, retrieve the ip address (fieldname 'ip'), lookup the region, latitude and longitude using that IP address, and write these values back into the table.
I have created the necessary fields in the table. The routine to grab the required details seems to work fine - the basic syntax is:
$location = $geoip->lookupLocation(required_address);
and this returns:
$location->city
$location->region
$location->latitude
$location->longitude


I have sufficient knowledge to have been able to put this into php and test it - it works fine. The script I generated is:

<?php
require_once "GeoIP.php";
$geoip = Net_GeoIP::getInstance('GeoIPCity.dat', Net_GeoIP::SHARED_MEMORY);
$con = mysql_connect("localhost","xxxxx","xxxxxx");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("joomla", $con);
$result = mysql_query("SELECT distinct ip, latitude FROM vvisitcounter");
while ($row = mysql_fetch_array($result))
{
$location = $geoip->lookupLocation($row['ip']);
echo "IP Address: " . $row['ip'] .
" Region: " . $location->region .
" Latitude: " . $location->latitude .
" Longitude: " . $location->longitude .
"<br/>";
}
mysql_close($con);
?>


So now I need to alter the script to implement the write-back of the region, latitude and longitude to the table.
Unfortunately my knowledge of php is very slight, hence this cry for help.

Would any kind soul be willing to help this newbie?

Thanks
Bikerman
jmraker
To write those 4 values to the database you would need to use the mysql update command inside the loop to update the corresponding fields in the database. Something like
for
Code:
while ($row = mysql_fetch_array($result))
{
$location = $geoip->lookupLocation($row['ip']);
mysql_query('UPDATE vvisitcounter SET region="' . $location->region . '", latitude="' . $location->latitude . '", latitude="' . $location->longitude . '" WHERE ip="' . $row['ip'] . '"';

echo "IP Address: " . $row['ip'] .
" Region: " . $location->region .
" Latitude: " . $location->latitude .
" Longitude: " . $location->longitude .
"<br/>";
}


I don't think that geoip data is reliable and it often reflects the city that your ISP, Cable company, etc is based
Bikerman
Thanks, but that doesn't appear to do the job....not sure why...it just 'blank screens' when I call the script from the server.

PS - yes, I know that geoip progs tend to only report back the position of the ISP or the nearest main router, but short of some seriously dodgy (illegal) practices (like hacking the actual target computer where possible) then that is about as good as we can do. It is, in any case, of sufficient resultion for my needs since I'm looking primarily to the country of the user and only using the lat/long data on specific individuals. I am using the commercial data from MaxMind (bought it at an IT fair a few months ago for a quid) which resolves down to organisation where possible.
sonam
Blank screen is usually syntax error in script. It is always good to use some error reporting on the top of script. For example:

Code:
error_reporting(E_ALL);
ini_set("display_errors", "1");
ini_set('error_reporting', E_ALL);


By the way your first line haven't brackets and this is potential error.

Quote:
require_once "GeoIP.php";


I think you need to write:
Code:
require_once("GeoIP.php");


Sonam
Fire Boar
No brackets is fine for require, the problem is in the mysql_query line: there is no closing parenthesis. Of course, that's ignoring the problem that mysql_ shouldn't really be used at all - it's pretty much deprecated in favour of mysqli.
Related topics
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.