CODEWITHSUNDEEP
phpmysqltimestamp
Budove
Budove

Reputation: 403

PHP MySQL if latest timestamp is two weeks older than today then

I'm trying to query a table where attendance records are kept per user and group. If the lastest timestamp per user and group is two weeks older than today's date, then an image will show. Here is the code I have at this point but I don't think I'm understanding the time formats or something.

$tsquery = mysql_query("SELECT ts FROM `attendance` WHERE contact_id='$contactid' AND             group_id='$groupid' AND `ts` + INTERVAL 7 DAY < NOW() ORDER BY ts LIMIT 1 ");
$ts=mysql_fetch_array($tsquery);

if(isset($ts)){
echo "<img src='images/care.png' title='Care Group' style='border: none; background-color:     #fff;'>";
} else {
}

I'm getting false results on some people. If someone didn't attend an event 2 weeks ago, but did attend an event yesterday, it's returning true rather than finding that they were in attendance within the last two weeks. Is there a better way of doing this that would return more accurate results?

Upvotes: 0

Views: 747

Answers (1)

hobbs
hobbs

Reputation: 240274

You need to turn your logic around a bit. Checking for the presence of rows matching ts + INTERVAL 7 DAY < NOW() means "did they attend any events more than 7 days ago"? Checking for rows matching ts + INTERVAL 7 DAY >= NOW() means "did they attend any events in the past week?" If there are no rows then they haven't done anything in a week.

(Also, just a nitpick, but you have "two weeks" in prose and "7 days" in code. Two weeks is, of course, 14 days.)

Upvotes: 1

Related Questions