Restrict of overlap of schedule PHP

Question

I have a PHP code and it work my only problem is if i create a schedule that overlaps the previous schedule

for example i schedule

**7:00 am to 8:30 am ** Succesfully Added

**6:30am to 7:30am ** Error Message: The schedule is conflict with other schedules

**6:30 am to 9:00 am ** Successfully Added (this one overlap a schedule so this needs to be a error)

**7:30am to 9:30am ** Error Message: The schedule is conflict with other schedules

This is the sample of my code

    $d_start = strtotime($datetime_start);
            $d_end = strtotime($datetime_end);
            $roomID = $assembly_hall_id;

            $chk = $this->conn->query("SELECT * FROM `schedule_list` where (('{$d_start}'   
            Between unix_timestamp(datetime_start) and unix_timestamp(datetime_end)) or ('{$d_end}' 
            Between unix_timestamp(datetime_start) and unix_timestamp(datetime_end))) ".(($roomID > 0) ? "
            and assembly_hall_id ='{$roomID}' and sched_status = '1' " : ""))->num_rows;
            
            if($chk > 0 ){
                $resp['status'] = 'failed';
                $resp['err_msg'] = "The schedule is conflict with other schedules.";
            }elseif(strtotime($datetime_start) == null)
            {   
                $resp['status'] = 'failed';
                $resp['err_msg'] = "Date and Time Schedule is Invalid.";
            }

Please don't post the entire file or project. Just the snippet which is minimally reproducible. — nice_dev, Dec 14 '22 at 06:25
welcome to stackoverflow! *"my code does not work"* is generally a bad starting point in writing question. anyway, you need to define how many cases of overlap can be e.g: 1) start earlier but end within existing schedule (5:00-7:30); 2) within existing schedule (07:00-8:00); 3) starts within existing schedule (7:30-9:00); 4) covers entire existing schedule (06:30-09:00). now, the next question is where you want to solve your problem (is it in [tag:sql]? or purely php?). i saw you had a query but you didn't mention the rdbms you are using.. — Bagus Tesa, Dec 14 '22 at 06:25
Your code is open to [SQL injection](https://stackoverflow.com/questions/601300/what-is-sql-injection) — DarkBee, Dec 14 '22 at 06:57

Nikkorian · Accepted Answer · 2022-12-14T06:55:03.087

0

You have not covered the case where d_start<datetime_start AND d_end>datetime_end

Change the SELECT to include this:

...
('{$d_start}' Between unix_timestamp(datetime_start) and unix_timestamp(datetime_end))
 OR
 ('{$d_end}' Between unix_timestamp(datetime_start) and unix_timestamp(datetime_end))
 OR
 (
  '{$d_start}' < unix_timestamp(datetime_start)
  AND
  '{$d_end}' > unix_timestamp(datetime_end)
 )
...

EDIT: A simpler structure is this:

...
('{$d_start}' < unix_timestamp(datetime_end)
 AND  
 '{$d_end}' > unix_timestamp(datetime_start)) 
...

edited Dec 14 '22 at 06:55

answered Dec 14 '22 at 06:43

Nikkorian

770
4
10

This answer is open to [SQL injection](https://stackoverflow.com/questions/601300/what-is-sql-injection) – DarkBee Dec 14 '22 at 06:57
@DarkBee, not really. strtotime() expects a string with a valid datetime format. If the supplied string does not parse, the function returns FALSE. So my component of the QUERY is protected by this at least. As for $assembly_hall_id, that's another matter, but that is not part of my answer. – Nikkorian Dec 14 '22 at 07:03

Restrict of overlap of schedule PHP

1 Answers1