Validation to check no existing overlaps with previous bookings

I need to validate that a booking doesn’t overlap in date and time with an
existing booking and return and error if a booking is overlapping an existing booking.

This is the code I have right now but it isn’t working, and when I make another booking that overlaps an existing booking, it is still passing through and not returning an error.

CREATE TABLE IF NOT EXISTS books (
id integer not null primary key autoincrement
created_at datetime, 
updated_at datetime,
carid integer,
sdate date not null,
edate date not null,
stime time not null,
etime time not null,
userid integer,
 foreign key("carid") references "cars"("id") on delete cascade, foreign key("userid") 
    references "users"("id") on delete cascade
);



$temp=DB::table('books')->where('sdate','>=',$sdate)->where('edate', '<=',$edate)->where('stime', '>=',$stime) ->where('etime','<=',$etime)->get();
if($temp) {
    $errorMsg="This overlaps with an existing booking, please choose another time.";
    $code='1';
    echo '<script type="text/javascript">alert("'.$errorMsg.'");</script>';
}
else {
    //ok
}

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Try something like :

if (count($temp) > 0) {
    $errorMsg="This overlaps with an existing booking, please choose another time.";
    $code='1';
    echo '<script type="text/javascript">alert("'.$errorMsg.'");</script>';
} else {
    //ok
}

Method 2

try

$temp=DB::table('books')
->where(function($query)use($sdate,$edate){
     $query->whereBetween('sdate', [$sdate,$edate])
    ->orWhereBetween('edate', [$sdate,$edate]);
 })->where('stime','>=',$stime)->orWhere('etime','<=',$etime)->count();
if($temp > 0){
....
}else{
....
}


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x