Maybe a group project?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Maybe a group project?

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

google: geoip

It's not exactly accurate however.. I've been listed as being in Chicago, obviously I don't live there.. ;)
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

Post 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.
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post 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?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Whats your query look like?
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post by Todd_Z »

Code: Select all

$ipLong = ip2long($ip);
	$sql = "SELECT `COUNTRY_NAME` FROM `IP_Countries` WHERE `IP_FROM` <= '$ipLong' and `IP_TO` >= '$ipLong'";
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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)
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post 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?
User avatar
smpdawg
Forum Contributor
Posts: 292
Joined: Thu Jan 27, 2005 3:10 pm
Location: Houston, TX
Contact:

Post by smpdawg »

Did you make sure to index the fields IP_FROM and IP_TO? Without them, it would be slower.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

^ hence why I asked for the export of the table structure ;)
User avatar
smpdawg
Forum Contributor
Posts: 292
Joined: Thu Jan 27, 2005 3:10 pm
Location: Houston, TX
Contact:

Post by smpdawg »

haha. Didn't even read that. Yes, structure dump please. It would make life easier.
User avatar
smpdawg
Forum Contributor
Posts: 292
Joined: Thu Jan 27, 2005 3:10 pm
Location: Houston, TX
Contact:

Post 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. :D
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post 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...
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post 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]
Post Reply