conflict time php mysql-CodePudding

if i have two courses in the same time how i can prevent student register course has conflict time with other course

enter image description here

CODE :

$getCourseConflict = $con->prepare("SELECT * FROM Registerd_course WHERE Student_id = ? AND Course_start_time > ? OR Course_end_time < ?");
$getCourseConflict->bind_param("sss", $Course_day,$s_time,$e_time);
$student_id = "201921042";
$getCourseConflict->execute();
$data = $getCourseConflict->get_result()->fetch_all(MYSQLI_ASSOC);

CodePudding user response：

I'm assuming you have a course they are trying to register, and the courses in the database that they are already registered for.

I'm also assuming there are no courses that end at midnight or span midnight; some changes will be needed if this is not true.

Assuming they are trying to register for a M,W course from 7-9, you would find conflicting courses with a query like:

select Course_id
from Registerd_course
where
    Student_id = ? and
    ( find_in_set('M',Course_day) or find_in_set('W', Course_day) ) and
    least(Course_end_time, '09:00:00') > greatest(Course_start_time, '07:00:00')

CodePudding user response：

Use Between Operator i.e, (starttime BETWEEN startdatetime AND endDatetime) OR (endTime BETWEEN startdatetime AND endDatetime);