how old is UTC_TIMESTAMP in my db?

[smerny]

I have a database with datetime in the format of 2009-12-17 12:03:32

 

How can I take that information into my PHP file and determine how many minutes ago it was?

[smerny]

no one knows?

[PFMaBiSmAd]

Use the mysql function timestampdiff with a unit of minute - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_timestampdiff

[smerny]

can't get it to work

 

Error:

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

 

Query:

SELECT ID, TIMESTAMPDIFF(MINUTE,to,UTC_TIMESTAMP) AS minutes FROM pengtracklog WHERE ip='x' ORDER BY ID DESC LIMIT 1

[smerny]

here is the bigger picture of what i am trying to do:

 

$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());

[PFMaBiSmAd]

At least two of your column names are reserved keywords and either need to be renamed to something else or require special handling so that they are not treated as keywords and break the SQL syntax of your query - http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

[smerny]

okay I made that change and a couple others... think I got it working now, will just have to wait 5 minutes to test the one more thing

[smerny]

yep, working great now... this is the final TEST code incase anyone ever looks at this thread and is interested

 

$act = "test";
$user_id = 0;
$ip = $_SERVER['REMOTE_ADDR'];
$search = "SELECT ID, TIMESTAMPDIFF(MINUTE,totime,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 totime=UTC_TIMESTAMP WHERE ID='{$row['ID']}'";
else
	$action = "INSERT INTO pengtracklog (ID, ID_user, action, ip, fromtime, totime) 
		VALUES (NULL, '{$user_id}', '{$act}', '{$ip}', UTC_TIMESTAMP, UTC_TIMESTAMP)";
}
else
$action = "INSERT INTO pengtracklog (ID, ID_user, action, ip, fromtime, totime) 
	VALUES (NULL, '{$user_id}', '{$act}', '{$ip}', UTC_TIMESTAMP, UTC_TIMESTAMP)";

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

echo $action." [sUCCESS]<br />";
echo "MINUTES= ".$row['minutes'];