User on FROM x TO y

[smerny]

I would like to create a log of users on a webpage...

 

instead of just every time they load a page make a new row like

 

ID_user | time

3 | 2009-12-17 12:03:32

6 | 2009-12-17 18:59:42

6 | 2009-12-17 19:01:37

3 | 2009-12-17 19:02:31

6 | 2009-12-17 19:02:55

3 | 2009-12-17 19:03:38

 

I would like to create something like

 

ID_user | from | to

3 | 2009-12-17 12:03:32 | 2009-12-17 12:03:32

6 | 2009-12-17 18:59:42 | 2009-12-17 19:02:55

3 | 2009-12-17 19:02:31 | 2009-12-17 19:03:38

 

notice that 3 gets split up into 2 sessions? I would like to put a threshold of like 5 minutes so if it's been over 5 minutes since the users last "to", start a new row with both "from" and "to" being the current time... otherwise if the current time is less than 5 minutes from the last "to" for that user, update the "to" field to the current time.

 

I'm not real good with times on PHP/SQL, I'm using UTC_TIMESTAMP for the current time... I'd have to pull that data out of the db and then compare it with the current time using PHP? or is there some fancy SQL that can do it all internally?

 

[ignace]

UPDATE users SET from = to, to = now() WHERE ID_user = $ID_User

[smerny]

all that would do is show the last 2 times for each user..?

[smerny]

psuedocode:

 

 

1) find last row with the users ID in column ID_user

2) check if current time is more than 5 minutes past the "to" in that row

3) if it is less than 5, update the "to" in that row to the current time

4) if it is more than 5 or if the users ID is not yet in the database, create a new row with the users ID, "to" and "from" being current time

 

[smerny]

all I really need is to know the best way to figure out the difference UTC_TIMESTAMP in the and the date() in PHP and I can figure out how to do this... but I figured while asking that I'd see if there is a better way....? anyone on either of those?

[ignace]

Don't execute that query in you application it's supposed to execute at a 5 min interval using cron

[smerny]

i don't know what cron is but i still don't think that is what i am looking for

[smerny]

Tried this:

$act = "test";
$user_id = 0;
$ip = $_SERVER['REMOTE_ADDR'];
$search = "SELECT ID, TIMESTAMPDIFF(MINUTE,to,UTC_TIMESTAMP) AS minutes	FROM pengtracklog WHERE ip='{$ip}' ORDER BY ID DESC LIMIT 1";
$result = mysql_query($search) or die (mysql_error());

if($row = mysql_fetch_assoc($result)){
if($row['minutes'] > 5)
	$action = "UPDATE pengtracklog SET to=UTC_TIMESTAMP WHERE ID='{$row['ID']}'";
else
	$action = "INSERT INTO pengtracklog (ID, ID_user, action, ip, from, to) 
		VALUES (NULL, '{$user_id}', '{$act}', '{$ip}', UTC_TIMESTAMP, UTC_TIMESTAMP)
		WHERE ID='{$row['ID']}'";
}
else
$action = "INSERT INTO pengtracklog (ID, ID_user, action, ip, from, to) 
	VALUES (NULL, '{$user_id}', '{$act}', '{$ip}', UTC_TIMESTAMP, UTC_TIMESTAMP)
	WHERE ID='{$row['ID']}'";

$result = mysql_query($action) or die (mysql_error());

got this:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'to,UTC_TIMESTAMP) AS minutes FROM pengtracklog WHERE ip='x' ORDER BY ID ' at line 1

[smerny]

solved here:

 

http://www.phpfreaks.com/forums/index.php/topic,281060.0.html