Page 1 of 1

PLEASE RESPOND

PostPosted: Thu Sep 25, 2008 6:53 pm
by whomadethisthing
Ok, I'm not sure if their are any administrators who actually know the "secret sauce" but I really need to figure out a query to this database I'm downloading. All 20 topics of this forum give me no indication on how to pull data from this database that will tell me what the country is for a certain IP. I mean, is that like some dark secret that no one wants to reply to? If so, I really don't care, PM me for Christ's sake... I just need this done, now!

Okay. Done ranting. Now for the actual question. We have a php environment set up, and we need to query the ip4, countries, and cityByCountry tables. We saw that there was a SVN repository we can connect to in order to pull down a bunch of scripts that will give us a better understanding of the query to those tables, but after attempting this, we were given the error: Can't connect to host 'hostip.info': No connection could be made because the target machine actively refused it.

So now, I'm wondering: Is this server just down? Is it restricted? Am I just not authorized? I mean what people? This is getting ridiculous. Please help!!!

Re: PLEASE RESPOND

PostPosted: Sun Oct 05, 2008 10:48 am
by geo_hostip
Hi,

I found the site hostip.info recently and have been using the api through php with success, posted this a few days ago but it got removed at the same time as some spam was !

have cut and pasted some parts from my php code below for you if it is of any help :

$ipadd = stripslashes($_SERVER['REMOTE_ADDR']);
$url = "http://api.hostip.info/get_html.php?ip=".$ipadd."&position=true";
$result = file($url);
print_r($result);

does this make sense ?

If you have problems with that, can you try typing the part in bold above into a web browser and adding an ip address, you can find your own using : http://www.whatismyip.com, you can also compare results from other sites i.e. http://www.melissadata.com/lookups/iplocation.asp

On how to use the database that is downloaded, I had a look today for the first time and did a few queries :

This will work where a city is found, would need to make it an outer join if wanted to include results where no city could be discerned :

SELECT i4.ip, i4.country, i4.city, c.name country_name, c.code country_code, y.name cityname, y.state, y.lat, y.lng
from ip4 i4 , countries c, cityByCountry y
where i4.ip = (select min(i.ip)
FROM ip4 i
where i.ip >= {enter your ip address as a number here} )
and i4.country = c.id
and y.country = i4.country
and y.city = i4.city

In fact, just take out references to the country city table to get country info only :

SELECT i4.ip, i4.country, c.name country_name, c.code country_code
from ip4 i4 , countries c
where i4.ip = (select min(i.ip)
FROM ip4 i
where i.ip >= {enter your ip address as a number here} )
and i4.country = c.id

to get the number for the above query take the ip address e.g. 1.2.3.4 : so, 1 is the most significant byte, 4 s the least:
echo "((1*256+2)*256+3)*256+4" | bc {if your using linux/unix}

I also took the small script found at http://www.hostip.info/use.html and added 2 more commands for my own use to get me details where I can't find them in hostip.info

called findip.sh :
#!/bin/tcsh -f
lynx -dump http://api.hostip.info/get_html.php?ip=$1
nslookup $1
whois $1 | egrep -i "country|city|state|longitude|latitude"

Hope that this is of some help.

When I have some time, I was going to create some mysql functions and procedure and upload them here.