[RESOLVED] Help getting average/total time on site

[Paul_Ferrie]

Hi guys,

My math is letting me down a bit and i am hoping someone here can help me.

Basicly i am trying to display the time spent on my site and the time spent on each section of the site. The site is built in flash and all the tracking is working as it should. It just tallying up the time is proving to be a headache.

When someone enter the site the tracker gets there ip and add's it to the db with timestamp. When the user continues to navigate the site the sections they visit are recorded in the db with timestamp and section id. This is the same for when they leave that section.

Below is the code i am using to retrieve the results.

for($i=0; $i < $lastTen; $i++) {
   	$visitorsD  = mysql_fetch_array($visitorsC);
	$visitorPagesA 	= mysql_query("SELECT * FROM visits WHERE uid = '".$visitorsD['id']."' ORDER BY id") or die(mysql_error());	
	$output .= "&lastTen" . $i . "numViews="	. mysql_num_rows($visitorPagesA);
	$output .= "&lastTen" . $i . "lastVisit="	. $visitorsD['date'] . " " . $visitorsD['time'];
	$output .= "&lastTen" . $i . "country="		. ucWords(strToLower($visitorsD['country'])); 
	$output .= "&lastTen" . $i . "browser="		. $visitorsD['browser']; 
	$output .= "&lastTen" . $i . "os="			. $visitorsD['operatingSystem'];
	$output .= "&lastTen" . $i . "entry="		. $visitorsD['entryPage'];
	//---------------------------------------------------------------------------------
	$totTime = 0;
	$totalTime = 0;
	$time = 0;
	//---------------------------------------------------------------------------------
	$userPageDate 	= split("/",$visitorsD['date']);
	$dateID			= $userPageDate[2]."-".$userPageDate[1]."-".$userPageDate[0];
	$visitorPages 	= mysql_query("SELECT * FROM userpages WHERE  dateClicked = '".$dateID."' && ip ='".$visitorsD['ip']."'") or die(mysql_error());
	$pagesClicked 	= mysql_num_rows($visitorPages);
	for($l = 0; $l < $pagesClicked; $l++){
		$clicked  	= mysql_fetch_array($visitorPages);
		//Time in section
		$time	= round($clicked['timeOnPage'] - $clicked['timeClicked'],1);
		//Total time on site taking from adding time on section together
		$totTime += $time;
		//if time is less than 60 then treat as seconds
		if($time<60){
			$pageTime = $time." secs";
		}else{
		// if greater than 60 then dived and treat as minutes
			$pageTime= round($time/60,1);
			if($pageTime<60){
				$pageTime=$pageTime."mins";
			}else{
				$pageTime= round($pageTime/60)."mins";
			}
		}
		$output .= "&lastTen" . $i . "pages" . $l . "pageTitle=" . $clicked['pageID'];
		$output .= "&lastTen" . $i . "pages" . $l . "pageTime="  . $pageTime;
	}
	if($totTime < 60) {
		$output .= "&lastTen" . $i . "totalTime=" . $totTime . " mins";
	} else {
		$mins= round($totTime/60);
		if($mins<60){
			$output .= "&lastTen" . $i . "totalTime=" .$mins  . " mins";
		}else{
			$mins= round($mins/60);
			$output .= "&lastTen" . $i . "totalTime=" .$mins  . " mins";
		}
	}
	$output .= "&lastTen" . $i . "pagesClicked=". $pagesClicked;
}

Can anyone point out where i am going wrong

[bpat1434]

Why are you adding the string " secs" and "mins" within the for() loop where you calculate the section & total time?

[Paul_Ferrie]

bpat1434 wrote:

Why are you adding the string " secs" and "mins" within the for() loop where you calculate the section & total time?

Sorry bud:S am i over looking something?
I dont see how adding "secs, mins" would have any relevance on the outcome.
You have lost me with that question.😕

[Weedpacket]

Paul Ferrie wrote:

I dont see how adding "secs, mins" would have any relevance on the outcome.

Well, if it has no relevance, why are you doing it at all? What would "145mins"/60 be? Wouldn't it make more sense to append it to the output, the way you're doing a few lines further down?

In general, one of the things that's making this whole task much hairier than it ought to be is the fact that you're trying to do three things simultaneously: collect raw data, calculate statistics, and generate output. Perhaps if you did them sequentially you'd be in a better state to explain what exactly is "going wrong".

[Paul_Ferrie]

Sorry bud i didnt write the code. I am trying to mod it to meet my needs.

What would "145mins"/60 be?

Can tell me where i am doing this?

Thanks
for any help

Paul

[Weedpacket]

Paul Ferrie wrote:

Can tell me where i am doing this?

No, you're right, you're not. I was confused.

But we're still in the dark about what is going wrong: remember, we can't run your code even if it was complete, and we don't know what it looked like or what it was doing before you started "modding"; so we don't know what the result looks like or even what it is supposed to look like. So far the entire description of the problem is that you collect some IPs and timestamps, that you want to do display the "time spent" on "each section of the site" and that your "math is letting you down".

[Paul_Ferrie]

Here's a dump of what i am running the query on.
I have removed the ip address's from this post

INSERT INTO `userpages` (`id`, `pageID`, `ip`, `dateClicked`, `timeClicked`, `timeOnPage`) VALUES 
(1, 'frontcover', '', '2008-02-07', '1202383410', '1202395962'),
(2, 'spread1', '', '2008-02-07', '1202383442', '1202383461'),
(3, 'spread2', '', '2008-02-07', '1202383466', '1202383614'),
(4, 'Video playing:1', '', '2008-02-07', '1202383503', '1202383594'),
(5, 'rearcover', '', '2008-02-07', '1202383618', '1202383649'),
(6, 'Send to a friend form', '', '2008-02-07', '1202383628', '1202383644'),
(7, 'frontcover', '', '2008-02-07', '1202388525', '1202388555'),
(8, 'spread1', '', '2008-02-07', '1202388560', '1202388907'),
(9, 'Video playing:1', '', '2008-02-07', '1202388826', '1202388862'),
(10, 'Video playing:3', '', '2008-02-07', '1202388870', '1202388872'),
(11, 'frontcover', '', '2008-02-07', '1202395715', '1202425538'),
(12, 'spread1', '', '2008-02-07', '1202395891', '1202425543'),
(13, 'Video playing:2', '', '2008-02-07', '1202395903', '1202395905'),
(14, 'Video playing:7', '', '2008-02-07', '1202395914', '1202395916'),
(15, 'spread2', '', '2008-02-07', '1202395926', '1202398329'),
(16, 'rearcover', '', '2008-02-07', '1202395936', '1202398344'),
(17, 'frontcover', '', '2008-02-08', '1202475237', '1202475257'),
(18, 'frontcover', '', '2008-02-11', '1202724604', '1202724630'),
(19, 'Video playing:5', '', '2008-02-11', '1202724632', '1202724827'),
(20, 'spread1', '', '2008-02-11', '1202724832', '1202724883'),
(21, 'spread2', '', '2008-02-11', '1202724888', '1202724913'),
(22, 'frontcover', '', '2008-02-13', '1202895840', '1202895850');

Here's a grab of the data from flash view.

Thanks again for any help.

[bpat1434]

I'm not 100% sure, but if a user is on your site for say..... 180 minutes (total time), you do the following math:

color=red[/color]If $totalTime < 60 Shouldn't that be treat as "seconds"?
[indent]Add to the output string as mins...[/indent]
Else
[indent]
color=red[/color]$mins = round($totalTime/60); Probably want to use floor instead of round...
if $mins < 60
[indent]Add to the output string as mins[/indent]
Else
[indent]
color=red[/color]$mins = round($mins/60); This should probably be floor as well...
Add to the output string as mins
[/indent]
[/indent]

Now, look at your code. You're always returning minutes. Obviously, if the difference form timeClicked to timeOnPage is say 58, you should be dealing with seconds (since both those times are seconds from Unix Epoch). the output string for color=red[/color] should be as seconds, not as minutes.

The subsequent parts use [man]round/man which will actually round up if you have a number like 10.5 or 11.8 which would mean that you're giving them extra time on the site. Typically, you'd want to [man]floor/man that calculation to get the base number (10 or 11 in the above example) and leave the remaining.

Here's a simple function which takes to unix timestamps (what you have in your database for timeClicked and timeOnPage) and will return an array of hours, minutes, and seconds for the difference of those times:

<?php

function timeDiff($start, $end)
{
	// Set up some "static" variables which hold
	// how long in seconds each period is:
	$_minute = 60;
	$_hour = 60*$minutes;
	$_day = 24*$hour;

// Get the time difference
$diff = $end-$start;

// Set up the basic result array
$output = array('days'=>0, 'hours'=>0, 'minutes'=>0, 'seconds'=>0);

// How many days difference?
$days = floor($diff/$_day);
$output['days'] = $days;
$diff -= $days*$_day;

// How many hours difference?
$hours = floor($diff/$_hour);
$output['hours'] = $hours;
$diff -= $hours*$_hour;

// How many minutes difference?
$minutes = floor($diff/$_minute);
$output['minutes'] = $minutes;
$diff -= $minutes*$_minute;

// Left over is seconds difference
$output['seconds'] = $diff;

return $output;
}

Now, I've added in there the time-period for days. You can add periods as you want, just make sure that the longer periods (weeks, months, years) come prior to the smaller periods (hours, minutes, seconds).

Now, in your loop you could easily call this function, and then depending upon what is returned as a non-zero value, just add that to the output string:

for($l=0; $l<mysql_num_rows($result); $l++)
{
    // Do whatever here....

$diff = timeDiff($timeClicked, $timeOnPage);

if($diff['days'] > 0)
    // Add days to the output string
if($diff['hours'] > 0)
    // Add hours to the output string
if($diff['minutes'] > 0)
    // Add minutes to the output string
if($diff['seconds'] > 0)
    // Add seconds to the output string

// Do whatever else here...
}

Hope that helps.

[Paul_Ferrie]

Thank you very much bud😃