CODEWITHSUNDEEP
phplaravel
Ali M
Ali M

Reputation: 85

How to query timeslot between 2 times in PHP / laravel

I need help in this logic, I want the user to create an appointment when he pick on a date and apply on a form but if the user takes a lot of time on the page, and someone else book before him for the giving date, he need to see error and go back to pick a date.

So here is my query 

 $result = Appointment::where('user_id', $post['user_id'])
 ->where('appointment_datetime', $post['bookingdate'] )
 ->whereBetween('appointment_time_start', [$begintime, $endtime])
 ->WhereBetween('appointment_time_end', [$begintime, $endtime])->get();


if ($result->isEmpty()) { /// Insert to database ///}

else { /// return back()->with('msg', 1); /// }

It seems that if I want to make appointment from 12:00 to 13:00
and I have an appointment in database from 11:00 to 13:00 , the query is not detecting it.

Also if I have 12:00 to 14:00 the query is not detecting it.

Can someone tell me what is wrong with my query, thank you a lot !

Upvotes: 1

Views: 1210

Answers (1)

Mesuti
Mesuti

Reputation: 908

My thoughts are you can iterate at the application level the time of appointment on the relevant date.

Firstly I prefer to use Carbon package. You can be install If you don't have. 

$appointments = Appointment::where('user_id', $post['user_id'])
                           ->where('appointment_datetime', $post['bookingdate'])
                            ->get();

$foundFlag = false;
if ($appointments->count() > 0) {
    $beginDate = \Carbon\Carbon::create(date('Y-m-d') . ' ' . $begintime); // You can be add at the end of varible ":00" if not exists
    $endDate = \Carbon\Carbon::create(date('Y-m-d') . ' ' . $endtime); //You can be add at the end of varible ":00" if not exists
    foreach($appointments as $appointment){
        $beginDateForCurrenAppointment = \Carbon\Carbon::create(date('Y-m-d') . ' ' . $appointment->appointment_time_start); // You can be add at the end of varible ":00" if not exists

        $endDateForCurrenAppointment = \Carbon\Carbon::create(date('Y-m-d') . ' ' . $appointment->appointment_time_end); // You can be add at the end of varible ":00" if not exists


        if ($beginDateForCurrenAppointment->between($beginDate, $endDate, true) || $endDateForCurrenAppointment->between($beginDate, $endDate, true) || $beginDate->between($beginDateForCurrenAppointment, $endDateForCurrenAppointment, true) || $endDate->between($beginDateForCurrenAppointment, $endDateForCurrenAppointment, true)) {
            $foundFlag = true;
            break;
        }
    }
}

if (! $foundFlag) {
    // Insert Database
} else {
    /// return back()->with('msg', 1); ///
}

The each of the logic at the if statement are means:

If the request appointment between the one exists appointment start time:

$beginDateForCurrenAppointment->between($beginDate, $endDate, true)

If the request appointment between the one exists appointment end time:

$endDateForCurrenAppointment->between($beginDate, $endDate, true)

If the exists appointment between the requested appointment start time:

$beginDate->between($beginDateForCurrenAppointment, $endDateForCurrenAppointment, true)

If the exists appointment between the requested appointment end time:

$endDate->between($beginDateForCurrenAppointment, $endDateForCurrenAppointment, true)

Upvotes: 1

Related Questions