Insert into datetime field without duplicates in interval

Question

I have table with tv shows, in this table there are columns, one for start time and one for finishing time. I need to make that if you want to insert tv show that you cannot put 2 tv shows in same time, like in real television.

Table tvshows have columns: tvshow_id, name, description, startime, finishingtime. I want to insert info via form, but if there is already tvshow which overlaps another tv show, you cannot insert this in database, you need to change time.

My try of solving this problem:

$sql = "SELECT ...";
$result = mysqli_query ($link, $sql);

if(mysqli_num_rows($result) > 0) {
        echo "Change datetime";
    } else {
    
        $sql = "INSERT ...";
    }

Answer 1

First: it is better to have Priority in your table, it helps you so much in ordering TV shows. You can control overlaps easily with this field.

Assume that you have:

tvshows table

tvshow_id, name, description, startime, finishingtime

and all your tvshows records inserted without overlap.

Your new record (with newStarttime and newFinishingtime) should not have overlap with any existed records.

Solution 1: Your new record should be external range of existing records range.

Select * from tvshows t
Where (newStarttime>=t.finishingtime AND newFinishingtime>t.finishingtime) OR
      (newFinishingtime<=t.starttime AND newStarttime<t.starttime )

Solution 2: So you should check these clauses:

1. State 1: newStarttime should not be in the range of any records.

NOT(newStarttime>=t.strattime AND newStarttime<t.finihingtime)

2. Satate 2: newFinishing time should not be in the range of any records.

NOT(newFinishing >t.strattime AND newFinishing <=t.finihingtime)

3. State 3: your new range should not overlap the existing range

NOT(newStarttime<t.strattime AND newFinishing >t.finihingtime)