Trying to grab database records are older than 14 days… In the database its stored as date/time.
I am wanting to check only on the date part of the field, so when the cron job runs through daily it only grabs records that are 14 days older than today.
I tried a few methods like:
WHERE DATE(timeslot) = DATE(NOW(), INTERVAL 14 DAY)
Hmm, i’m still lost on it… This is for appointments… If 30 days has passed since last appointment dispatch email, but exclude anyone that has another appointment booked between 30 days ago and into the future… if that makes sence?
The code below sends out to anyone who had an appointment logged 30 days ago, even if they have made another appointment for after that…
$sql = mysql_query("SELECT t.*, c.*
FROM timeslots as t
JOIN clients as c ON c.clientid = t.clientid
WHERE timeslot >= CURRENT_DATE - INTERVAL 31 DAY
AND timeslot < CURRENT_DATE - INTERVAL 30 DAY");
An example of results of the above script (these people had an appointment 30 days ago, so would be emailed…)
Fri 31 Aug 2012 @ 8:30 am -> Alan
Fri 31 Aug 2012 @ 12:30 pm -> Martin
Fri 31 Aug 2012 @ 3:00 pm -> Craig
Fri 31 Aug 2012 @ 9:00 am -> Sue
Fri 31 Aug 2012 @ 2:00 pm -> Kellie
Fri 31 Aug 2012 @ 9:30 am -> Karen
But these 2 names allready have up coming appointments…
So need to be excluded from the results above… Basically looking for people that havnt been back in the last 30 days and havnt booked any further appointments… A note saying we havnt seen you in awhile, if you would like to make another appointment, call…
$sql = mysql_query("SELECT c.clientid, c.email, c.name, MAX(DATE(t.timeslot)) as latest
FROM timeslots as t
JOIN clients as c ON c.clientid = t.clientid
GROUP BY c.clientid
HAVING MAX(DATE(timeslot)) = CURDATE() - INTERVAL 30 DAY");