prepare("SELECT Time_Slot FROM Manager_Group_Name WHERE Group_ID = ? AND Program_ID = ?");
$stmt->bind_param("ii", $Group_ID, $Program_ID);
$stmt->execute();
$group = $stmt->get_result()->fetch_assoc();
$stmt->close();
if (!$group) {
echo "";
exit;
}
$groupTimeSlot = trim($group['Time_Slot']);
$timeSlotArray = array_map('trim', explode(',', $groupTimeSlot));
$groupTime = $timeSlotArray[0]; // Use the first slot
// 2. Get all courses for this group
$stmt = $mysqli->prepare("SELECT Course_ID FROM Course_Group_Schedule_Main WHERE Group_ID = ? AND Program_ID = ? AND Assigned= 0");
$stmt->bind_param("ii", $Group_ID, $Program_ID);
$stmt->execute();
$courseResults = $stmt->get_result();
$courseIDs = [];
while ($row = $courseResults->fetch_assoc()) {
$courseIDs[] = $row['Course_ID'];
}
$stmt->close();
if (empty($courseIDs)) {
echo "";
exit;
}
$courseIDList = implode(",", array_map('intval', $courseIDs)); // safe comma list
// 3. Get available teachers with preferred course and preferred time slot
$stmt = $mysqli->prepare("
SELECT tp.Teacher_ID, tp.First_Name, tp.Last_Name
FROM Teacher_Profile tp
WHERE tp.User_Access = 1
AND FIND_IN_SET(?, tp.Time_Slot) > 0
AND EXISTS (
SELECT 1 FROM Teacher_Course_Preferences tcp
WHERE tcp.Teacher_ID = tp.Teacher_ID
AND tcp.Course_ID IN ($courseIDList)
)
ORDER BY tp.First_Name ASC
");
$stmt->bind_param("s", $groupTime);
$stmt->execute();
$teachers = $stmt->get_result();
$options = "";
while ($teacher = $teachers->fetch_assoc()) {
$options .= "";
}
echo $options ?: "";
}
/*
include_once '../includes/db_connect.php';
include_once '../includes/functions.php';
sec_session_start();
if (isset($_POST['Program_ID'], $_POST['Group_ID'])) {
$Program_ID = intval($_POST['Program_ID']);
$Group_ID = intval($_POST['Group_ID']);
// Get the group's time slot from Manager_Group_Name
$stmt = $mysqli->prepare("SELECT Time_Slot FROM Manager_Group_Name WHERE Group_ID = ? AND Program_ID = ?");
if (!$stmt) {
die("Error preparing group query: " . $mysqli->error);
}
$stmt->bind_param("ii", $Group_ID, $Program_ID);
$stmt->execute();
$result = $stmt->get_result();
if ($group = $result->fetch_assoc()) {
$groupTimeSlot = trim($group['Time_Slot']); // e.g., "Morning" or "Morning,Afternoon"
} else {
echo "";
exit;
}
$stmt->close();
// For simplicity, if the group has multiple time slots, you might decide to pick one or let the user choose.
// Here we'll assume you want to filter by the first time slot:
$timeSlotArray = array_map('trim', explode(',', $groupTimeSlot));
$groupTime = $timeSlotArray[0];
// Query teachers who are available in this group time slot.
$stmt = $mysqli->prepare("SELECT Teacher_ID, First_Name, Last_Name
FROM Teacher_Profile
WHERE User_Access=1 AND FIND_IN_SET(?, Time_Slot) > 0
ORDER BY First_Name ASC");
if (!$stmt) {
die("Error preparing teacher query: " . $mysqli->error);
}
$stmt->bind_param("s", $groupTime);
$stmt->execute();
$result = $stmt->get_result();
$options = "";
while ($teacher = $result->fetch_assoc()) {
$options .= "";
}
echo $options;
$stmt->close();
}
*/
?>