JezK
Edit File: fix_foreign_keys.php
<?php require_once 'includes/config.php'; require_once 'includes/db.php'; echo "<h1>Database Foreign Key Cleanup Utility</h1>"; try { $db = Database::getInstance(); echo "<h2>Step 1: Checking for orphaned data...</h2>"; // Check for orphaned responses (questions that don't exist) $orphanedResponses = $db->query(" SELECT COUNT(*) as count FROM responses r LEFT JOIN questions q ON r.question_id = q.id WHERE q.id IS NULL ")->fetch()['count']; echo "<p>Orphaned responses: $orphanedResponses</p>"; // Check for orphaned marked_for_review entries $orphanedMarks = $db->query(" SELECT COUNT(*) as count FROM marked_for_review m LEFT JOIN questions q ON m.question_id = q.id WHERE q.id IS NULL ")->fetch()['count']; echo "<p>Orphaned marked_for_review entries: $orphanedMarks</p>"; // Check for orphaned options $orphanedOptions = $db->query(" SELECT COUNT(*) as count FROM options o LEFT JOIN questions q ON o.question_id = q.id WHERE q.id IS NULL ")->fetch()['count']; echo "<p>Orphaned options: $orphanedOptions</p>"; // Check for orphaned questions (sections that don't exist) $orphanedQuestions = $db->query(" SELECT COUNT(*) as count FROM questions q LEFT JOIN sections s ON q.section_id = s.id WHERE s.id IS NULL ")->fetch()['count']; echo "<p>Orphaned questions: $orphanedQuestions</p>"; // Check for orphaned sections (quizzes that don't exist) $orphanedSections = $db->query(" SELECT COUNT(*) as count FROM sections s LEFT JOIN quizzes q ON s.quiz_id = q.id WHERE q.id IS NULL ")->fetch()['count']; echo "<p>Orphaned sections: $orphanedSections</p>"; echo "<h2>Step 2: Cleaning up orphaned data...</h2>"; $db->beginTransaction(); try { // Delete orphaned responses if ($orphanedResponses > 0) { $db->query(" DELETE r FROM responses r LEFT JOIN questions q ON r.question_id = q.id WHERE q.id IS NULL "); echo "<p>✅ Deleted $orphanedResponses orphaned responses</p>"; } // Delete orphaned marked_for_review entries if ($orphanedMarks > 0) { $db->query(" DELETE m FROM marked_for_review m LEFT JOIN questions q ON m.question_id = q.id WHERE q.id IS NULL "); echo "<p>✅ Deleted $orphanedMarks orphaned marked_for_review entries</p>"; } // Delete orphaned options if ($orphanedOptions > 0) { $db->query(" DELETE o FROM options o LEFT JOIN questions q ON o.question_id = q.id WHERE q.id IS NULL "); echo "<p>✅ Deleted $orphanedOptions orphaned options</p>"; } // Delete orphaned questions if ($orphanedQuestions > 0) { $db->query(" DELETE q FROM questions q LEFT JOIN sections s ON q.section_id = s.id WHERE s.id IS NULL "); echo "<p>✅ Deleted $orphanedQuestions orphaned questions</p>"; } // Delete orphaned sections if ($orphanedSections > 0) { $db->query(" DELETE s FROM sections s LEFT JOIN quizzes q ON s.quiz_id = q.id WHERE q.id IS NULL "); echo "<p>✅ Deleted $orphanedSections orphaned sections</p>"; } $db->commit(); echo "<h2>✅ Cleanup completed successfully!</h2>"; } catch (Exception $e) { $db->rollBack(); echo "<h2>❌ Error during cleanup: " . $e->getMessage() . "</h2>"; } echo "<h2>Step 3: Database integrity check...</h2>"; // Verify foreign key relationships $integrityCheck = $db->query(" SELECT 'responses' as table_name, COUNT(*) as orphaned_count FROM responses r LEFT JOIN questions q ON r.question_id = q.id WHERE q.id IS NULL UNION ALL SELECT 'marked_for_review' as table_name, COUNT(*) as orphaned_count FROM marked_for_review m LEFT JOIN questions q ON m.question_id = q.id WHERE q.id IS NULL UNION ALL SELECT 'options' as table_name, COUNT(*) as orphaned_count FROM options o LEFT JOIN questions q ON o.question_id = q.id WHERE q.id IS NULL UNION ALL SELECT 'questions' as table_name, COUNT(*) as orphaned_count FROM questions q LEFT JOIN sections s ON q.section_id = s.id WHERE s.id IS NULL UNION ALL SELECT 'sections' as table_name, COUNT(*) as orphaned_count FROM sections s LEFT JOIN quizzes q ON s.quiz_id = q.id WHERE q.id IS NULL ")->fetchAll(); $allClean = true; foreach ($integrityCheck as $check) { if ($check['orphaned_count'] > 0) { echo "<p>❌ {$check['table_name']}: {$check['orphaned_count']} orphaned records</p>"; $allClean = false; } else { echo "<p>✅ {$check['table_name']}: Clean</p>"; } } if ($allClean) { echo "<h2>🎉 Database integrity verified! All foreign key relationships are clean.</h2>"; } else { echo "<h2>⚠️ Some orphaned data still exists. You may need to run this script again.</h2>"; } } catch (Exception $e) { echo "<h2>❌ Error: " . $e->getMessage() . "</h2>"; } ?> <style> body { font-family: Arial, sans-serif; max-width: 800px; margin: 0 auto; padding: 20px; background-color: #f5f5f5; } h1, h2 { color: #333; } p { margin: 10px 0; padding: 10px; background: white; border-radius: 5px; border-left: 4px solid #007cba; } p:contains("✅") { border-left-color: #28a745; background-color: #d4edda; } p:contains("❌") { border-left-color: #dc3545; background-color: #f8d7da; } p:contains("⚠️") { border-left-color: #ffc107; background-color: #fff3cd; } </style>