I am building a booking(appointments) app and I am offering the users the option to deactivate the account they have on the site.
Before doing so though I must check if they have any pending appointments and warn them in case they have.
The logic based on emitting a warning for pending appointments is comparing that current date/time(the time instance the user goes to disable his account) with the date/time of the appointment…and if the former is is earlier then the latter then the warning is emitted…all these done with this query(some details of the query are emitted…they are not needed here):
SELECT COUNT(*) AS exists
FROM appointments,users .....AND CURDATE() <=appointments.startDate AND CURTIME() <= appointments.startime;
And here the problem begins…suppose the date/time of the appointments is this:2016-06-22/10:00:00 and the time he goes to disable his account the date/time is 2016-06-20/09:00:00.
In such a case the above query will output 1 meaning there are pending appointments-that is correct of course.
Suppose now that the user goes to disable his account at this date/time:2016-06-20/12:00:00.
…the query will output 0(no appointments pending)…which is not correct of course,
If either the date or the time that the account is disabled is past of either the date or time of the appointment this according to the query is “translated” as NO pending appointments…
What can I do here?Alter the query…and if yes how?