Current time minus timestamp from db

[jacko_162]

Im trying to create an IF statement for the following data from database;

 

if $record['logoffDateTime'] is older or equal to 60 days old, echo TRUE else echo FALSE.

 

this is my code snippets im working from;

 

<?php
// Define and perform the SQL query
$results = mysql_query('SELECT * FROM membertracking ORDER BY name ASC');
$results_array = array();
while ($row = mysql_fetch_array($results)) {
$results_array[$row['characterID']] = $row;
}

foreach ($results_array as $id => $record) {
$tempdate = mktime(0,0,0,date("m"),date("d")-60,date("Y"));
$tempdate = date("Y-m-d", $tempdate);

$datecheck = $record['logoffDateTime'];


if ($datecheck <= '$tempdate') {

echo "TRUE";
} else {
echo "FALSE";
};
?>
?></td>


<td><?php echo $record['name'];?></td>
<td><?php echo $record['startDateTime'];?></td>
<td><?php echo $record['logoffDateTime'];?></td>
<td><?php echo $record['location'];?></td>
</tr>
<?php } ?>

 

for some reason its returning "TRUE" for all my results, even though some of the "$record['logoffDateTime']" is older than 60 days.. what have i done wrong?

[Jessica]

'$tempdate' is a literal string, reading $tempdate. Not the variable contained within $tempdate.

[jacko_162]

sorry im a noob php dev.

 

i remove the ' and all i get is FASLE returns.

 

i stored date in db as DateTime : 2012-03-16 23:41:00

 

does this make a difference to my calcs?

[jacko_162]

now im using;

 

$days = 60;
if(date('Y-m-d', strtotime("{$record['logoffDateTime']} +{$days}day")) < date('Y-m-d'))

 

and i am getting somewhere.. thx for help ;p

[Jessica]

SELECT IF(
(DATEDIFF(logoffDateTime, CURDATE()) > 60), 1, 0
) AS columnalias, membertracking.* -- You should list specific fields, not *
FROM membertracking 
ORDER BY name ASC

Not tested.

 

Run that query and do a print_r on the resulting row. (I may have the 1 and 0 backward, not sure exactly what you're going for.)

[kicken]

I generally find when doing something like this, rather than trying to subtract two dates, it's easier/better to subtract 60 days from 'now' and then compare to the db records.  Eg:

SELECT
   CASE WHEN (CURRENT_TIMESTAMP-INTERVAL 60 DAY) < logoffDateTime THEN 1 ELSE 0 END as isOlderThan60Days,
   ...