mysql check booking overlap time between two columns

I Have a table booking which has two columns job_time_beg and job_time_end and jobs starts from 8:00 and goes till 16:00 Now I need to if the available time overlap with the given time so that I know I dont want to book within this given time.

I am using

      select count(*)as cnt from orders
      where job_date = '2021-02-04'
      and job_truck  = '24'
      and ((job_time_beg >= '10:00' and job_time_beg < '12:00')
           or (job_time_end > '(11):00' and job_time_end <= '12:00'))
        AND order_status_id in (1,5)

this works fine for most cases but if there is a booking between 8:00 and 11:00 this query does not work and shows that the time 10:00 to 12:00 is available which overlap between 8:00 and 11:00 I also tried

SELECT count(*) as cnt
FROM orders
 WHERE
 job_date = '$data'
      and job_truck  = '$truck' and
(
   '$start' BETWEEN job_time_beg and job_time_end 
   OR
   '$end' BETWEEN job_time_beg and job_time_end
)
        AND order_status_id in (1,5)

which works for the 8:00 and 11:00 overlap but does not work when the booking is available for ex start time can be equal to end time in the next booking if we have booking 8:00 to 10:00 and we get time 10:00 to 12:00 this does not overlap because we can assign next booking from 10:00.

Please have a look at sql fiddle sqlfiddle

Solution

The logic for overlap for two time periods is:

The first begins before the second ends.
The first ends after the second begins.

In your query, the logic would be:

SELECT count(*) as cnt
FROM orders
WHERE job_date = @job_date AND
      job_truck  = @truck AND
      @start < job_time_end AND
      @end > job_time_begin AND
      order_status_id IN (1, 5);

Note the use of named parameters. Don't munge query strings with parameter values!. Parameters prevent unexpected and hard-to-debug syntax errors. They also protect the code from SQL injection attacks.