CODEWITHSUNDEEP
phpmysql
Iban Dominguez Noda
Iban Dominguez Noda

Reputation: 878

Test if dates overlap in a single query

Thank you very much before hand for taking the time to have a look at my question. I am quite new to mysql driven web apps and I am having some troubles figuring out a query.

I have 3 tables:

events:

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

performers:

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

event_performers:

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

To retrieve the events data I use.

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

I would like to run a test query to find out if any performer is acting on different events at the same time.

I am trying to retrive all performers ids, all events ids and loop trough each of them to compare for dates, but I got stacked doing foreach loops and cant really get it working

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);

Thank you very much for your help, Cheers

Upvotes: 3

Views: 97

Answers (3)

Joe Love
Joe Love

Reputation: 5932

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

This will return every event that has a conflict, and will list each event that conflicts with it. It can result in funny looking data because if A,B and C all conflict, you'll get 3 rows. 1 saying that A conflicts with B and C, another saying that B conflicts with A and C, and one saying that C conflicts with A and B. Perhaps you can "clean that up" however you like in PHP, but this data is likely a good start.

Upvotes: 1

Olli
Olli

Reputation: 1738

You could use something like

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)

to get the number of overlapping events. you just have to add your limitation for a specific person.

Upvotes: 0

hd1
hd1

Reputation: 34657

MySQL has a between clause, rendering your query to:

SELECT * 
FROM events 
JOIN event_performers
ON events.event_id = event_performers.event_id 
WHERE start_time BETWEEN ? OR ? AND stop_time BETWEEN ? and ?

Hope that helps...

Upvotes: 0

Related Questions