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