setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Handle form submission
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['marks'])) {
$class = $_POST['class'];
$term = $_POST['term'];
$year = $_POST['year'];
$marks = $_POST['marks'];
$count = 0;
// Get valid student IDs from STUDENTS table for the selected class
$validStmt = $pdo->prepare("SELECT STUDENT_ID FROM STUDENTS WHERE CLASS = :class");
$validStmt->execute([':class' => $class]);
$validIDs = array_column($validStmt->fetchAll(PDO::FETCH_ASSOC), 'STUDENT_ID');
// Subject order: MTC, ENG, SST, SCI
$subjectOrder = ['MTC', 'ENG', 'SST', 'SCI'];
foreach ($marks as $id => $subjectMarks) {
if (!in_array($id, $validIDs) || !is_array($subjectMarks)) continue;
// Prepare update values
$fieldsToUpdate = [];
$updateParams = [
':id' => $id,
':class' => $class,
':term' => $term
];
foreach ($subjectOrder as $subject) {
if (isset($subjectMarks[$subject]) && $subjectMarks[$subject] !== '') {
$fieldsToUpdate[$subject] = (int)$subjectMarks[$subject];
$updateParams[":$subject"] = (int)$subjectMarks[$subject];
}
}
if (empty($fieldsToUpdate)) continue;
// Check if record exists
$checkStmt = $pdo->prepare("SELECT * FROM midterm_results WHERE STUDENT_ID = :id AND CLASS = :class AND TERM = :term");
$checkStmt->execute([
':id' => $id,
':class' => $class,
':term' => $term
]);
$existingRecord = $checkStmt->fetch(PDO::FETCH_ASSOC);
if ($existingRecord) {
// Only update the submitted subjects
$setClauses = [];
foreach ($fieldsToUpdate as $subject => $mark) {
$setClauses[] = "$subject = :$subject";
}
$updateSQL = "UPDATE midterm_results SET " . implode(', ', $setClauses) . " WHERE STUDENT_ID = :id AND CLASS = :class AND TERM = :term";
$updateStmt = $pdo->prepare($updateSQL);
$updateStmt->execute($updateParams);
} else {
// Insert: build full insert statement with nulls for missing subjects
$columns = ['STUDENT_ID', 'CLASS', 'TERM', 'YEAR'];
$placeholders = [':id', ':class', ':term', ':year'];
$insertParams = [
':id' => $id,
':class' => $class,
':term' => $term,
':year' => $year
];
foreach ($subjectOrder as $subject) {
$columns[] = $subject;
$placeholder = ":$subject";
$placeholders[] = $placeholder;
$insertParams[$placeholder] = isset($subjectMarks[$subject]) && $subjectMarks[$subject] !== '' ? (int)$subjectMarks[$subject] : null;
}
$insertSQL = "INSERT INTO midterm_results (" . implode(', ', $columns) . ") VALUES (" . implode(', ', $placeholders) . ")";
$insertStmt = $pdo->prepare($insertSQL);
$insertStmt->execute($insertParams);
}
$count++;
}
echo "✅ $count record(s) processed successfully.
";
}
// Show form
if (isset($_GET['class'])) {
$class = $_GET['class'];
$term = $_GET['term'] ?? '';
$year = $_GET['year'] ?? '';
$stmt = $pdo->prepare("SELECT STUDENT_ID, STUDENT_NAME FROM STUDENTS WHERE CLASS = :class ORDER BY STUDENT_NAME");
$stmt->execute([':class' => $class]);
$students = $stmt->fetchAll(PDO::FETCH_ASSOC);
if (count($students) > 0): ?>
Enter Midterm Marks for Class -
No students found for class: " . htmlspecialchars($class) . "";
endif;
} else {
// If class not selected
?>
Select Class to Enter Midterm Marks