Check availability of appointment slot

I am creating an area for a user where he can make an appointment.

The available appointments are inside a table called: bigo_appuntamento

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

but

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?

Answer

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);

Advertisement

Answer