prepare($query);
$stmt->bind_param("i", $programID);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
$courseID = $row["Course_ID"];
// Check if an unassigned schedule exists for this course and group
$checkStmt = $mysqli->prepare("SELECT COUNT(*) FROM Course_Group_Schedule_Main
WHERE Program_ID = ? AND Course_ID = ? AND Group_ID = ? AND Assigned = 0");
if (!$checkStmt) {
die("Error preparing assignment check query: " . $mysqli->error);
}
$checkStmt->bind_param('iii', $programID, $courseID, $groupID);
$checkStmt->execute();
$checkStmt->bind_result($count);
$checkStmt->fetch();
$checkStmt->close();
if ($count > 0) {
// Check if teacher prefers this course
if ($teacherID > 0) {
$prefStmt = $mysqli->prepare("SELECT COUNT(*) FROM Teacher_Course_Preferences
WHERE Teacher_ID = ? AND Course_ID = ?");
$prefStmt->bind_param("ii", $teacherID, $courseID);
$prefStmt->execute();
$prefStmt->bind_result($prefCount);
$prefStmt->fetch();
$prefStmt->close();
if ($prefCount == 0) {
continue; // Skip this course if not preferred
}
}
// Fetch the schedule details for this course
$fetchStmt = $mysqli->prepare("SELECT Reserve_Course, Time_Slot, Start_Date, End_Date, Start_Time, End_Time
FROM Course_Group_Schedule_Main
WHERE Program_ID = ? AND Course_ID = ? AND Group_ID = ? AND Assigned = 0");
if (!$fetchStmt) {
echo "Error preparing schedule fetch query for Course ID $courseID: " . $mysqli->error . " ";
continue;
}
$fetchStmt->bind_param('iii', $programID, $courseID, $groupID);
$fetchStmt->execute();
$fetchStmt->bind_result($Reserve_Course, $Time_Slot, $Start_Date, $End_Date, $Start_Time, $End_Time);
if (!$fetchStmt->fetch()) {
$fetchStmt->close();
continue;
}
$fetchStmt->close();
$checkedReserve = ($Reserve_Course == 1) ? 'checked' : '';
// Check conflict with teacher's current schedule
if ($teacherID > 0) {
$conflictStmt = $mysqli->prepare("SELECT COUNT(*) FROM Teacher_Course_Assignments
WHERE Teacher_ID = ?
AND Time_Slot = ?
AND (? <= End_Date AND ? >= Start_Date)");
if ($conflictStmt) {
$conflictStmt->bind_param("isss", $teacherID, $Time_Slot, $Start_Date, $End_Date);
$conflictStmt->execute();
$conflictStmt->bind_result($conflictCount);
$conflictStmt->fetch();
$conflictStmt->close();
if ($conflictCount > 0) {
continue;
}
}
}
$Start_Time = date("h:i A", strtotime($Start_Time));
$End_Time = date("h:i A", strtotime($End_Time));
echo "
" . htmlspecialchars($row["Course_Name"]) . "
";
}
}
$stmt->close();
$mysqli->close();
}
/*
include_once '../includes/db_connect.php';
include_once '../includes/functions.php';
sec_session_start();
$error_msg = "";
if (isset($_POST["Program_ID"]) && isset($_POST["Group_ID"])&& isset($_POST["Teacher_ID"])) {
$programID = $_POST["Program_ID"];
$groupID = $_POST["Group_ID"];
// Teacher_ID is optional. If provided, we will check for conflicts.
$teacherID = $_POST["Teacher_ID"];
//echo $programID .'-'. $groupID.'-'. $teacherID;
//exit();
// Query to get courses for this program
$query = "SELECT Course_ID, Course_Name FROM Courses WHERE Program_ID = ? ORDER BY Course_ID";
$stmt = $mysqli->prepare($query);
$stmt->bind_param("i", $programID);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
$courseID = $row["Course_ID"];
// Check if an unassigned schedule exists for this course and group
$checkStmt = $mysqli->prepare("SELECT COUNT(*) FROM Course_Group_Schedule
WHERE Program_ID = ? AND Course_ID = ? AND Group_ID = ? AND Assigned = 0");
if (!$checkStmt) {
die("Error preparing assignment check query: " . $mysqli->error);
}
$checkStmt->bind_param('iii', $programID, $courseID, $groupID);
$checkStmt->execute();
$checkStmt->bind_result($count);
$checkStmt->fetch();
$checkStmt->close();
// Only process the course if there is an unassigned schedule record.
if ($count > 0) {
// Fetch the schedule details for this course
$fetchStmt = $mysqli->prepare("SELECT Reserve_Course, Time_Slot, Start_Date, End_Date, Start_Time, End_Time
FROM Course_Group_Schedule
WHERE Program_ID = ? AND Course_ID = ? AND Group_ID = ? AND Assigned = 0");
if (!$fetchStmt) {
echo "Error preparing schedule fetch query for Course ID $courseID: " . $mysqli->error . " ";
continue;
}
$fetchStmt->bind_param('iii', $programID, $courseID, $groupID);
$fetchStmt->execute();
$fetchStmt->bind_result($Reserve_Course, $Time_Slot, $Start_Date, $End_Date, $Start_Time, $End_Time);
if (!$fetchStmt->fetch()) {
echo "No unassigned schedule found for Course ID $courseID. ";
$fetchStmt->close();
continue;
}
$fetchStmt->close();
// Prepare the Reserve_Course checkbox value
$checkedReserve = ($Reserve_Course == 1) ? 'checked' : '';
// If a teacher is selected, check for conflicts with existing teacher assignments.
if ($teacherID > 0) {
$conflictStmt = $mysqli->prepare("SELECT COUNT(*) FROM Teacher_Course_Assignments
WHERE Teacher_ID = ?
AND Time_Slot = ?
AND (? <= End_Date AND ? >= Start_Date)");
if ($conflictStmt) {
$conflictStmt->bind_param("isss", $teacherID, $Time_Slot, $Start_Date, $End_Date);
$conflictStmt->execute();
$conflictStmt->bind_result($conflictCount);
$conflictStmt->fetch();
$conflictStmt->close();
// If there is a conflict, skip this course row.
if ($conflictCount > 0) {
continue;
}
}
}
$Start_Time = date("h:i A", strtotime($Start_Time));
$End_Time = date("h:i A", strtotime($End_Time));
// Output the row for this course.
echo "