setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// 1. Fix Categories (Departments) in course_categories
echo "Fixing Categories...
";
$cats = [
1 => ['Aviation Education', 'aviation'],
2 => ['Automobile Education', 'automobile'],
3 => ['Computer Education', 'computer-education'],
4 => ['Ayurveda Education', 'ayurveda'],
5 => ['Beautician Education', 'beautician']
];
// We don't truncate, just insert ignore or update
foreach ($cats as $id => $data) {
$stmt = $pdo->prepare("SELECT id FROM course_categories WHERE id = ?");
$stmt->execute([$id]);
if ($stmt->fetch()) {
$pdo->prepare("UPDATE course_categories SET name = ?, slug = ? WHERE id = ?")->execute([$data[0], $data[1], $id]);
} else {
$pdo->prepare("INSERT INTO course_categories (id, name, slug) VALUES (?, ?, ?)")->execute([$id, $data[0], $data[1]]);
}
}
// 2. Fix Courses
echo "Fixing Courses...
";
// Determine which schema we are on
$hasCategoryId = false;
try {
$pdo->query("SELECT category_id FROM courses LIMIT 1");
$hasCategoryId = true;
} catch(Exception $e) {}
$hasCategory = false;
try {
$pdo->query("SELECT category FROM courses LIMIT 1");
$hasCategory = true;
} catch(Exception $e) {}
// Update short_name based on course_code
$mappings = [
'%COMP%' => ['Computer Education', 3],
'%AY%' => ['Ayurveda Education', 4],
'%AVI%' => ['Aviation Education', 1],
'%AUTO%' => ['Automobile Education', 2],
'%BEA%' => ['Beautician Education', 5]
];
foreach ($mappings as $like => $info) {
$name = $info[0];
$cat_id = $info[1];
// Update short_name
$stmt = $pdo->prepare("UPDATE courses SET short_name = ? WHERE course_code LIKE ?");
$stmt->execute([$name, $like]);
// Update category_id if it exists
if ($hasCategoryId) {
$stmt = $pdo->prepare("UPDATE courses SET category_id = ? WHERE course_code LIKE ?");
$stmt->execute([$cat_id, $like]);
}
}
// Fix category column if it exists (append " Course" if needed)
if ($hasCategory) {
// e.g. If course_type is 'Diploma', category becomes 'Diploma Course'
$pdo->exec("UPDATE courses SET category = 'Diploma Course' WHERE course_name LIKE '%Diploma%'");
$pdo->exec("UPDATE courses SET category = 'Certificate Course' WHERE course_name LIKE '%Certificate%'");
$pdo->exec("UPDATE courses SET category = 'Short Term Course' WHERE course_name LIKE '%Short Term%'");
$pdo->exec("UPDATE courses SET category = 'Vocational Course' WHERE course_name LIKE '%Vocational%'");
}
echo "Database Fixed Successfully!
";
echo "You can now go back to All Courses.
";
} catch (Exception $e) {
echo "Error: " . $e->getMessage() . "
";
}