Get amount of each unique...?

[techiefreak05]

I'm writing a web analytics script and it's working great, with graphs, detailed stast, etc... but I' having a problem coming up with the query to display the correct number of "visits" or "unique" hits... I can display total page views just fine...

 

on the stats page I show the lifetime unique visits, and the lifetime total page views like so:

 

LIFETIME UNIQUE VISITS:

SELECT COUNT(DISTINCT(ip)) as unique_hits FROM tracking 
WHERE project_id = '2'

 

LIFETIME TOTAL PAGE VIEWS:

SELECT COUNT(ip) as total_hits FROM tracking 
WHERE project_id = '2'

 

 

Those both work fine... but when I run the stats for the week, the "unique visits" for the week are GREATER than the lifetime unique visits...

 

BECAUSE... I have a function that generates an array of dates in between the start and end parameters, and I loop through the array, and grab the total, and unique for that day.... but if someone visists the site each day, he will counted 7 time, once for each day, since the loop looks for the unique for each day...

 

WEEKLY STATS:

<?php
//$range is the array of dates.

foreach($range as $day=>$xdate){
    $sql_Counter = "SELECT COUNT(DISTINCT(ip)) as unique_hits,
                    COUNT(ip) as total_hits,
                    '".$xdate."' as raw_date,
                    date_format(date,'%Y-%m-%d') as date_only ,
                    date_format('".$xdate."','%m-%d') as xdate_only 
                    FROM tracking
                    WHERE
                    project_id = '".$proj."' AND date_format(date,'%Y-%m-%d') = date_format('".$xdate."','%Y-%m-%d')";
    $q_Counter = mysql_query($sql_Counter) or die(mysql_error()); 
    while($r2=mysql_fetch_assoc($q_Counter)){
     // add each day's value to the total values for the week...
   }
}
?>

 

How can I update the lifetime unique visits query to count the number of EACH unique ip's and add them together?

 

Thanks a lot.

[fenway]

Sorry, I don't follow.

[techiefreak05]

that's what I was afraid of.

 

basically I run a query for all the unique IPs for any give project for the "lifetime stats",

 

and when I get the weekly stats, I run a query for each DAY in the last week, that grabs the unique IPs for each DAY, meaning if a person visits a site each day for a week, his visit count is 7, and the weekly stats will count that as 7, while the lifetime stats sounts him as 1, since it gets the stats for a lifetime, instead of daily.

[fenway]

Then you'll need to get a 7-day unique version -- you can't just sum across the days.

[eran]

Two things - as for your problem with the unique IPs, I suggest you keep a counter for each project and update it every day with a cron job (for the last day's unique hits). It might be possible to do it with a query on demand, but it will be a very heavy duty query and that's probably not worth it.

Regarding your approach to calculate daily hits, you can achieve the same effect using one query for each range. Something like the following:

 

SELECT COUNT(DISTINCT(ip)) as unique_hits, COUNT(ip) as total_hits, '".$xdate."' as raw_date,
date_format(date,'%Y-%m-%d') as date_only 
date_format('".$xdate."','%m-%d') as xdate_only 
FROM tracking
WHERE project_id = " . (int) $proj . "
AND `date` BETWEEN . " . $startDate . " AND " . $endDate . "
GROUP BY DATE(`date`)

 

put the dates of the range in $startDate and $endDate respectively. I assumed `date` is the date column you are using - by going over the range and grouping per day you get the same result.