非常感谢您花时间来看我的问题。我对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_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);
非常感谢你的帮助,干杯
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中随心所欲地"清理"它,但这些数据可能是一个好的开始。
MySQL有一个between子句,将您的查询呈现为:
SELECT *
FROM events
JOIN event_performers
ON events.event_id = event_performers.event_id
WHERE start_time BETWEEN ? OR ? AND stop_time BETWEEN ? and ?
希望能有所帮助。。。
您可以使用类似的东西
SELECT
COUNT(*) AS amount_of_overlaps
FROM
events_table AS e1
JOIN (SELECT * FROM events_table) AS e2 ON (e2.id > e1.id)
WHERE
(e2.start_date BETWEEN e1.start_date AND e1.end_date)
OR
(e2.end_date BETWEEN e1.start_date AND e1.end_date)
以获取重叠事件的数量。你只需要为一个特定的人增加你的限制。