IP Country count

Databases
1

For a site I’m working on, the owners would like to have some kind of statistics of ip address and countries from visitors for their pay per click advertisers. They already have a IP 2 Country table in the database, which I can use to get individual IP addresses and Countries from a visitor:


CREATE TABLE IF NOT EXISTS `geoip` (
  `start_ip` char(15) NOT NULL,
  `end_ip` char(15) NOT NULL,
  `start_long` int(10) unsigned NOT NULL,
  `end_long` int(10) unsigned NOT NULL,
  `country_code` char(2) NOT NULL,
  `country_name` varchar(50) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

and this is the query I use so far to extract the data:


	<cfset arguments.ip = "#cgi.remote_addr#">
	<cfset Aip = ListToArray(arguments.ip,'.') />
	<cfset Aip = ((Aip[1] * 16777216) + (Aip[2] * 65536) + (Aip[3] * 256) + (Aip[4])) />
	<cfquery name="qCountry" datasource="#Application.dsn#">
		SELECT * <!-- The * is only used to test -->
		FROM geoip
		WHERE START_LONG <= <cfqueryparam cfsqltype="CF_SQL_BIGINT" value="#Aip#">
		AND END_LONG >= <cfqueryparam cfsqltype="CF_SQL_BIGINT" value="#Aip#">
	</cfquery>

So far so good, As I said I can use this data on an action page to insert into a table where the information is stored:


CREATE TABLE IF NOT EXISTS `click_statistics` (
  `click_id` int(6) unsigned NOT NULL auto_increment,
  `advertiser_id` int(4) NOT NULL,
  `ip_address` char(15) NOT NULL,
  `county_code` char(2) NOT NULL,
  `country` varchar(50) NOT NULL,
  PRIMARY KEY  (`click_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The insert works perfectly:


    <cfquery datasource="#Application.dsn#">
    	INSERT INTO
        click_statistics
        ( advertiser_id, ip_address, county_code, country )
        VALUES
        (
         <cfqueryparam cfsqltype="cf_sql_integer" value="#Val( Url.company )#" />
        ,<cfqueryparam cfsqltype="cf_sql_char" value="#Trim( ipAddress )#" />
        ,<cfqueryparam cfsqltype="cf_sql_char" value="#Trim( qCountry.COUNTRY_CODE )#" />
        ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim( qCountry.COUNTRY_NAME )#" />
        )
    </cfquery>

So after every click on a banner this information is going in to the database. But what I would like, or actually they would like, is to have some kind of total clicks per country and that is where I got lost. Every country has a begin and end ip address. And I have no idea how to do a count on the available records in the database since they have a singe IP address. Does anyone have an idea how to do this.

Thank you in advance

2 

SELECT
    county_code
  , country
  , COUNT(*) as numberofclicks
FROM click_statistics
GROUP BY
    county_code
  , country
3

Oohps :injured: I must have been to much intrigued by the IP addresses etc. Guido. You made my day :slight_smile: