Annual updates

alfienobel

I know there are lots of posts about time problems but here's another.

I have a database storing information from questionnaires. The mySQL database has a date field date_of_registration which stores the date the user submitted the questionnaire i.e 2002-4-11
What I need to do is flag up those users whose entry was submitted more than one year ago ,a nd send them a reminder to update their info.

I've tried the following code:

$queryGetAnnualUpdates = "SELECT practitioner_forename, practitioner_surname, course_code, course_title, date_of_registration
FROM (t_institution
INNER JOIN t_course ON t_institution.institution_code = t_course.institution_code)
INNER JOIN t_practitioner ON (t_institution.institution_code =
t_practitioner.institution_code)
AND (t_course.practitioner_code = t_practitioner.practitioner_code)
WHERE (date_of_registration <= NOW() - INTERVAL 1 YEAR)";

But I can't get it to return any data .
There is an entry for that is over one year old.

Any help would be gratefully received.
Thanks,

Alf

pete_bisby

If you are sure a record exists that has been submitted more than a year ago, check to see if records still exist in the other two tables (t_course and t_institution).

Because you're using INNER JOIN, the query requires records to be present in all tables that satisfy your JOIN criteria - if one doesn't exist (e.g. course codes have changed) then the record will not be found.

alfienobel

Thanks for the reply mate. I'm a bit unclear what you meant though. It will pull out a record on a fixed date i.e
WHERE (date_of_registration = '$date')";

'$date = 2002/04/08'

That works ok, so the joins must surely be correct. It is only when I change it to the
WHERE (date_of_registration <= NOW() - INTERVAL 1 YEAR)
code that it can't find any records.

I'm scratching my head!!

pete_bisby

Place the number of years within quotes, so:

WHERE (date_of_registration <= NOW() - INTERVAL "1" YEAR)

Looks a bit weird, I agree, but does bring back results.

alfienobel

Cheers Pete, it now works (with single quotes not double tho)

Thanks for all your help.

Mark