I am creating an area for a user where he can make an appointment.
The available appointments are inside a table called:
This table has these 5 fields
bigo_appto_id --> ID bigo_appto_data ---> DATE bigo_appto_ora ---> HOURS bigo_appto_stato --> FREE OR NOT (WHEN IT'S FREE THE VALUE IS = DISPONIBILE) bigo_appto_id_admin --> ID ADMIN
To select available dates, I use this query:
$a = "1"; $d = "DISPONIBILE"; $data = date('Y/m/d'); $ora = date('H:i'); $q_amc_d_appto = $connessione->prepare(" SELECT * FROM bigo_appuntamento WHERE bigo_appto_id_admin = ? AND bigo_appto_stato = ? AND bigo_appto_data >= ? AND bigo_appto_ora >= ? "); $q_amc_d_appto->bind_param('isss', $a,$d, $data,$ora); $q_amc_d_appto->execute(); $r_amc_d_appto = $q_amc_d_appto->get_result();
Everything works perfectly, so the query checks that:
Admin ID is set to 1
The date is greater than today
The time is greater than the current one
The appointment slot is set to available (disponibile)
If a user on the current date (2020/02/09), logs in at 15:00 pm and there is an appointment slot for the same day, at 16:00 he can book it
If, on the other hand, there is an appointment slot for the same day but at 2:59 pm, he does not see it.
It is all right.
Where is the problem:
If a user logs in at 15:00 pm
On the current date (2020/02/09), but there are no availability for that day
But there is an availability for the next day (2020/02/10), but at 11:00 am
Even if it is correct, the query does not show available appointments
Because the query verifies that:
The date of the appointment slot is greater than or equal to the current one
The time of the appointment slot is greater than or equal to the current time
So this generates such a situation:
Appointment slot date (2020/02/10) is greater than the current date (2020/02/09): YES
The appointment slot time (11:00 am) is greater than or equal to the current time (15:00 pm): NO
And so it doesn’t show anything, how can I fix this?
Change your query to only compare hour in “today” date:
$q_amc_d_appto = $connessione->prepare(" SELECT * FROM bigo_appuntamento WHERE bigo_appto_id_admin = ? AND bigo_appto_stato = ? AND ((bigo_appto_data = ? AND bigo_appto_ora >= ?) OR bigo_appto_data > ?) "); $q_amc_d_appto->bind_param('issss', $a,$d, $data,$ora, $data);