php - 如何编写函数来合并两条记录

Question

我有数据库结构

--car_id 
--from_date_time
--to_date_time
--is_booked

我想做:

If any two records having is_booked == 'N" exists in database, having overlapping from_date_time and to_date_time, 
then these records will be merged into a single record.

示例:

If i have records in database like:
Record 1:
from_date_time = '2016-08-01 04:00:00';
to_date_time = '2016-08-01 06:00:00';

Record 2:
from_date_time = '2016-08-01 03:00:00';
to_date_time = '2016-08-01 05:00:00';

Then,above two records will get merged to create following:
from_date_time = '2016-08-01 03:00:00';
to_date_time = '2016-08-01 06:00:00';

我尝试过以下操作:

i) Check if the looped_from_date_time is in any database record with from_date_time >= looped_from_date_time  and to_date_time <= looped_from_date_time  and is_booked = "N" and car_id = $car_id.

   iii) Check if the looped_to_date_time is in any database record with from_date_time >= looped_to_date_time and to_date_time <= looped_to_date_time and is_booked = "N" and car_id = $car_id.

但它不起作用

Answer 1

“期间 1”与“期间 2”重叠

if

NOT ( end_of_period1 < beginning_of_period2 OR 
beginning_of_period1 > end_of_period2). 

这是涵盖所有可能情况的唯一方法。

在你的情况下，我相信第一个条件(I)总是错误的:如果'from_date_time'总是低于'to_date_time'在所有记录中，您不能有 "from_date_time >= looped_from_date_time and to_date_time <= looped_from_date_time" .