Page 1 of 3
Maybe a group project?
Posted: Tue Mar 01, 2005 3:15 pm
by Todd_Z
Hey guys - I have an idea for a project that maybe a few people could contribute to =>
Given a database of ip addresses, this script creates an image of a map showing (through colors) the density of the ip address locations.
Here are my inital questions...
Whats the easiest way to find the country of an ip address?
Any ideas on how to do this map idea?
Posted: Tue Mar 01, 2005 3:38 pm
by feyd
google: geoip
It's not exactly accurate however.. I've been listed as being in Chicago, obviously I don't live there..

Posted: Tue Mar 01, 2005 9:39 pm
by Pyrite
You want to check out the IP to Country CSV list, which is updated monthly and it's free.
I use it to redirect users from different countries on my website.
Posted: Wed Mar 02, 2005 3:32 pm
by Todd_Z
When I check the db for 16,000 ip addresses, it takes a rediculously long time... any way i can speed this up?
Posted: Wed Mar 02, 2005 3:40 pm
by John Cartwright
Whats your query look like?
Posted: Wed Mar 02, 2005 3:42 pm
by Todd_Z
Code: Select all
$ipLong = ip2long($ip);
$sql = "SELECT `COUNTRY_NAME` FROM `IP_Countries` WHERE `IP_FROM` <= '$ipLong' and `IP_TO` >= '$ipLong'";
Posted: Wed Mar 02, 2005 3:46 pm
by feyd
try BETWEEN syntax.. it may help, not sure.. post the table's export structure (need to see everything of how you set up the table)
Posted: Wed Mar 02, 2005 3:49 pm
by Todd_Z
Code: Select all
$ipLong = ip2long($ip);
$sql = "SELECT `COUNTRY_NAME` FROM `IP_Countries` WHERE '$ipLong' BETWEEN `IP_FROM` AND `IP_TO`";
Is that what you meant?
Posted: Wed Mar 02, 2005 3:53 pm
by smpdawg
Did you make sure to index the fields IP_FROM and IP_TO? Without them, it would be slower.
Posted: Wed Mar 02, 2005 3:56 pm
by feyd
^ hence why I asked for the export of the table structure

Posted: Wed Mar 02, 2005 4:13 pm
by smpdawg
haha. Didn't even read that. Yes, structure dump please. It would make life easier.
Posted: Wed Mar 02, 2005 4:50 pm
by smpdawg
BTW - I like the idea but resolution to country is pretty vague. I would like to know to a finer detail like state or even city. It would make an interesting display especially if it were dynamic so you could see where the people you are talking to are located.
It is good for trying to default to a language or hint at a language but I want more.

Posted: Wed Mar 02, 2005 5:19 pm
by Todd_Z
How do i "structure dump" through phpmyadmin?
Field Type Null Default
IP_FROM double No 0
IP_TO double No 0
COUNTRY_CODE2 char(2) No
COUNTRY_CODE3 char(3) No
COUNTRY_NAME varchar(50) No
Indexes :
Keyname Type Cardinality Field
IP_FROM INDEX 54775 IP_FROM
IP_TO INDEX 54775 IP_TO
Copied from the print view from phpmyadmin...
Posted: Wed Mar 02, 2005 5:35 pm
by feyd
click export... uncheck data... make sure structure is checked.. and go.. should see a CREATE TABLE query in there.. that's what we want to see..
Posted: Wed Mar 02, 2005 5:37 pm
by Todd_Z
CREATE TABLE `IP_Countries` (
`IP_FROM` double NOT NULL default '0',
`IP_TO` double NOT NULL default '0',
`COUNTRY_CODE2` char(2) NOT NULL default '',
`COUNTRY_CODE3` char(3) NOT NULL default '',
`COUNTRY_NAME` varchar(50) NOT NULL default '',
KEY `IP_FROM` (`IP_FROM`),
KEY `IP_TO` (`IP_TO`)
) TYPE=MyISAM;
[sorry folks... still a semi-n00b i guess]