php - 测试日期是否在单个查询中重叠

Question

非常感谢您抽出宝贵时间查看我的问题。我对 mysql 驱动的 Web 应用程序很陌生，我在计算查询时遇到了一些麻烦。

我有 3 个表:

事件:

event_id(INT) | event_fee(INT) | start_time(DATE_TIME) | stop_time(DATE_TIME)

表演者:

performer_id(INT) | performer_name(VARCHAR) | performer_fee(INT)

event_performers:

event_performers_id(INT) | event_id(INT) | performer_id(INT)

检索我使用的事件数据。

SELECT * 
FROM events 
JOIN event_performers
ON events.event_id = event_performers.event_id

我想运行一个测试查询来查明是否有任何表演者同时对不同的事件采取行动。

我正在尝试检索所有表演者 ID、所有事件 ID 并循环遍历它们中的每一个以比较日期，但是我在执行 foreach 循环时遇到了很多问题，无法真正让它工作

include('app/config.php');

function return_all_performers(){
include(PATH . '/core/model/db_connect.php');

try {
    $query_object = $db->query("
        SELECT performer_id 
        FROM performers
        ");
} catch (Exception $e){
    include_once(PATH . "/core/view/error.php");
    exit();
}

return $query_object->fetchAll(PDO::FETCH_COLUMN, 0);
}

function return_all_performer_events($performer_id){
include(PATH . '/core/model/db_connect.php');

try {
    $query_object = $db->prepare("
        SELECT * 
        FROM events 
        JOIN event_performers
        ON events.event_id = event_performers.event_id
        WHERE event_performers.performer_id = :performer_id
        ");
    $query_object->bindParam(':performer_id', $performer_id);
    $query_object->execute();
} catch (Exception $e){
    include_once(PATH . "/core/view/error.php");
    exit();
}

return $query_object->fetchAll(PDO::FETCH_ASSOC);   
}

$performers_ids = return_all_performers();
$overlaps = array();

foreach ($performers_ids as $performer_id) {
$performer_events = return_all_performer_events($performer_id);

foreach ($performer_events as $event => $value) {
    // Isolate Single event and compare it to others
    // TO DO
    // Check If times overlaps 
    /*
    foreach () {
        if(
            strtotime($event['start_time']) >= strtotime($isolated_event['start_time']) && 
            strtotime($event['start_time']) <= strtotime($isolated_event['stop_time'])
          ){
            return true;
        } 
        if(
            strtotime($event['stop_time']) >= strtotime($isolated_event['start_time']) && 
            strtotime($event['stop_time']) <= strtotime($isolated_event['stop_time'])
          ){
            $overlaps[] = $event;
        }
    }
    */
}
}

var_dump($overlaps);

非常感谢您的帮助，干杯

Answer 1

SELECT ep.performer_name, ep.performer_id, ep.performer_fee, e.event_id, e.event_fee,
e.start_time, e.stop_time, group_concat(e2.event_id SEPARATOR ',')
FROM events as e
JOIN event_performers ep ON e.event_id = ep.event_id
join events e2 on e2.event_id != e.event_id and 
     greatest(e2.start_time, e.start_time) < least(e2.stop_time,e.stop_time)
join  event_performers ep2 on ep2.event_id=e2.event_id and ep2.performer_id=ep.performer_id
group by ep.performer_name, ep.performer_id, ep.performer_fee, e.event_id, e.event_fee,
e.start_time, e.stop_time

这将返回所有有冲突的事件，并列出与之冲突的每个事件。它可能会导致看起来很有趣的数据，因为如果 A、B 和 C 都冲突，您将得到 3 行。 1 说 A 与 B 和 C 冲突，另一种说法是 B 与 A 和 C 冲突，还有一个说 C 与 A 和 B 冲突。也许你可以在 PHP 中随心所欲地“清理”，但这个数据很可能一个好的开始。