I have a question that is giving me some headaches. I'm doing a system where I register courses (violin, guitar, flute ...) and each course will have a section (section A, section B, section C ...). Each course will have a day and a schedule of related shifts according to the section (if it is section A it will be only Monday and Wednesday from 8am to 12pm .. if it is section B it will be Tuesday and Thursday from 3pm to 5pm, and so ..) .
The problem comes when registering a teacher in a section and a course, once I register the teacher, I can select it by combobox
after filling in the information of the section with their hours And days, my question is How can I prevent a teacher who is already in a course from 8am to 12pm only on Mondays and Wednesdays can not be registered again for example in a section C where they are on Tuesdays and Wednesdays at the the same time that the section A? I want to avoid is the clash of hours per teacher when registering it in a section and course.
A teacher can give many courses, but not in the same shift (morning-evening) on the same day. But if you can take a course in the morning shift and another in the afternoon shift.
For now I have my BD this way:
CREATE TABLE 'seccion' (
'id_seccion' int(11) NOT NULL,
'descripcion' varchar(10) DEFAULT NULL,
'horaEntrada' varchar(10) DEFAULT NULL,
'horaSalida' varchar(10) DEFAULT NULL,
'dias' varchar(50) DEFAULT NULL,
'cupos' int(10) DEFAULT NULL,
'curso_id_curso' int(11) DEFAULT NULL,
'personas_cedula' varchar(13) DEFAULT NULL,
'turno' varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
and try to do the following, but as I said, it gives me headaches and how to place this that I consult:
$result1 = $mysqli->query("SELECT COUNT(personas_cedula) FROM seccion WHERE personas_cedula='$cbx_profesor' AND turno='matutino' AND dias='$dias' AND horaEntrada='$horaEntrada' ");
$row = @mysqli_fetch_row($result1);
if($row[0]>=1){
$errors[] = "El profesor ya se encuentra registrado en un curso";
} else {
$sql = "INSERT INTO seccion (descripcion, horaEntrada, horaSalida, dias,cupos,curso_id_curso,personas_cedula,turno) VALUES('$descripcion','$horaEntrada','$horaSalida','$dias','$cupos','$cbx_curso','$cbx_profesor','$turno')";
$resultado = $mysqli->query($sql);
to anyone who can help me, I would greatly appreciate it in advance!