100 lines
3.7 KiB
PHP
100 lines
3.7 KiB
PHP
<?php
|
|
require_once 'config.php';
|
|
|
|
try {
|
|
$pdo = db();
|
|
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
|
|
|
|
// Create courses table
|
|
$sql_courses = "CREATE TABLE IF NOT EXISTS courses (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
teacher_id INT NOT NULL,
|
|
FOREIGN KEY (teacher_id) REFERENCES users(id)
|
|
)";
|
|
$pdo->exec($sql_courses);
|
|
echo "Table 'courses' created successfully.\n";
|
|
|
|
// Create enrollments table
|
|
$sql_enrollments = "CREATE TABLE IF NOT EXISTS enrollments (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
student_id INT NOT NULL,
|
|
course_id INT NOT NULL,
|
|
FOREIGN KEY (student_id) REFERENCES users(id),
|
|
FOREIGN KEY (course_id) REFERENCES courses(id)
|
|
)";
|
|
$pdo->exec($sql_enrollments);
|
|
echo "Table 'enrollments' created successfully.\n";
|
|
|
|
// Create activities table
|
|
$sql_activities = "CREATE TABLE IF NOT EXISTS activities (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
enrollment_id INT NOT NULL,
|
|
activity_name VARCHAR(255) NOT NULL,
|
|
grade INT,
|
|
activity_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (enrollment_id) REFERENCES enrollments(id)
|
|
)";
|
|
$pdo->exec($sql_activities);
|
|
echo "Table 'activities' created successfully.\n";
|
|
|
|
// Sample Data
|
|
// Hash a default password
|
|
$password = password_hash('password', PASSWORD_DEFAULT);
|
|
|
|
// Truncate tables before inserting sample data to avoid duplicates on re-run
|
|
$pdo->exec("SET FOREIGN_KEY_CHECKS = 0");
|
|
$pdo->exec("TRUNCATE TABLE activities");
|
|
$pdo->exec("TRUNCATE TABLE enrollments");
|
|
$pdo->exec("TRUNCATE TABLE courses");
|
|
$pdo->exec("TRUNCATE TABLE users");
|
|
$pdo->exec("SET FOREIGN_KEY_CHECKS = 1");
|
|
echo "Tables truncated successfully.\n";
|
|
|
|
// Insert a teacher
|
|
$pdo->exec("INSERT INTO users (email, password, role) VALUES ('teacher@example.com', '$password', 'teacher')");
|
|
$teacher_id = $pdo->lastInsertId();
|
|
echo "Sample teacher inserted.\n";
|
|
|
|
// Insert courses for the teacher
|
|
$stmt = $pdo->prepare("INSERT INTO courses (name, teacher_id) VALUES (?, ?)");
|
|
$stmt->execute(['Algebra 101', $teacher_id]);
|
|
$course1_id = $pdo->lastInsertId();
|
|
$stmt->execute(['History of Algebra', $teacher_id]);
|
|
$course2_id = $pdo->lastInsertId();
|
|
echo "Sample courses inserted.\n";
|
|
|
|
// Insert students
|
|
$stmt = $pdo->prepare("INSERT INTO users (email, password, role) VALUES (?, ?, ?)");
|
|
$stmt->execute(['student1@example.com', $password, 'student']);
|
|
$student1_id = $pdo->lastInsertId();
|
|
$stmt->execute(['student2@example.com', $password, 'student']);
|
|
$student2_id = $pdo->lastInsertId();
|
|
echo "Sample students inserted.\n";
|
|
|
|
// Enroll students in courses
|
|
$stmt = $pdo->prepare("INSERT INTO enrollments (student_id, course_id) VALUES (?, ?)");
|
|
$stmt->execute([$student1_id, $course1_id]);
|
|
$enrollment1_id = $pdo->lastInsertId();
|
|
$stmt->execute([$student2_id, $course1_id]);
|
|
$enrollment2_id = $pdo->lastInsertId();
|
|
$stmt->execute([$student1_id, $course2_id]);
|
|
$enrollment3_id = $pdo->lastInsertId();
|
|
echo "Students enrolled in courses.\n";
|
|
|
|
// Add student activities
|
|
$stmt = $pdo->prepare("INSERT INTO activities (enrollment_id, activity_name, grade) VALUES (?, ?, ?)");
|
|
$stmt->execute([$enrollment1_id, 'Homework 1', 95]);
|
|
$stmt->execute([$enrollment1_id, 'Quiz 1', 88]);
|
|
$stmt->execute([$enrollment2_id, 'Homework 1', 72]);
|
|
$stmt->execute([$enrollment3_id, 'Research Paper', 92]);
|
|
echo "Student activities added.\n";
|
|
|
|
|
|
} catch (PDOException $e) {
|
|
die("ERROR: Could not able to execute script. " . $e->getMessage());
|
|
}
|
|
|
|
unset($pdo);
|
|
?>
|