beginTransaction();
try {
// Update submission status
$stmt = $pdo->prepare("UPDATE submissions SET status = ? WHERE id = ?");
$stmt->execute([$status, $submission_id]);
if ($status == 'approved') {
// Check if a certificate already exists
$stmt_check = $pdo->prepare("SELECT id FROM certificates WHERE submission_id = ?");
$stmt_check->execute([$submission_id]);
if ($stmt_check->rowCount() == 0) {
// Generate a unique certificate code
$certificate_code = uniqid('CERT-');
$stmt_insert = $pdo->prepare("INSERT INTO certificates (submission_id, certificate_code) VALUES (?, ?)");
$stmt_insert->execute([$submission_id, $certificate_code]);
}
} else {
// If status is not 'approved', delete any existing certificate
$stmt_delete = $pdo->prepare("DELETE FROM certificates WHERE submission_id = ?");
$stmt_delete->execute([$submission_id]);
}
// Commit transaction
$pdo->commit();
echo "
Failed to update submission status: " . $e->getMessage() . "
";
}
}
// Fetch all competitions
$stmt_competitions = $pdo->query("SELECT id, title FROM competitions ORDER BY start_date DESC");
$competitions = $stmt_competitions->fetchAll();
// Fetch all submissions with user and competition info
$stmt_submissions = $pdo->query("
SELECT
s.id,
s.file_path,
s.uploaded_at,
s.status,
u.name as user_name,
c.title as competition_title,
c.id as competition_id
FROM
submissions s
JOIN
users u ON s.user_id = u.id
JOIN
competitions c ON s.competition_id = c.id
ORDER BY
c.id, s.uploaded_at DESC
");
$submissions_by_competition = [];
while ($row = $stmt_submissions->fetch()) {
$submissions_by_competition[$row['competition_id']][] = $row;
}
?>