DatabaseConnection.php

<?php
class Database {
    private $host = "localhost"; 
    private $db_name = "uagc_studentdatabase";
    private $username = "Michael.Brassfield"; //Look into setting up a configuration file
    private $password = "1234";
    private $con;

    // Creates connection when object is created 
    public function __construct() {
        $this->con = $this->getConnection();
    }

    // Function to create database connection 
    private function getConnection() {
        $this->con = null;
        try {
            $this->con = new PDO("mysql:host=" . $this->host . ";dbname=" . $this->db_name, $this->username, $this->password);
            $this->con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch(PDOException $exception) {
            echo "Connection error: " . $exception->getMessage();
        }
        return $this->con;
    }

    //Method to check connection status
    public function checkConnection() {
        return $this->con !== null;
    }

    // Function to execute select queries
    public function executeSelectQuery($sql, $params = []) {
        try {
            $stmt = $this->con->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch(PDOException $exception) {
            echo "Error: " . $exception->getMessage();
            return false;
        }
    }

    // Function to alter or change database
    public function executeQuery($sql, $params = []) {
        try {
            $stmt = $this->con->prepare($sql);
            $stmt->execute($params);
            return $stmt->rowCount(); 
        } catch(PDOException $exception) {
            echo "Error: " . $exception->getMessage();
            return false;
        }
    }

    //Backend fucntions for course registration and managment 

    //Fetch all avalible courses
    public function getCourses() {
        $sql = "SELECT * FROM tblcourses";
        return $this->executeSelectQuery($sql);
    }

    //List registred classes for student
    public function getRegisteredCourses($studentId, $semester) {
        $sql = "SELECT c.course_id, c.course_code, c.course_name, c.instructor_name, c.credits
                FROM tblregistrations r  
                JOIN tblcourses c ON r.course_id = c.course_id
                WHERE r.student_id = :student_id AND r.semester = :semester";
        $params = ['student_id' => $studentId, 'semester' => $semester];
        $result = $this->executeSelectQuery($sql, $params);

        if ($result === false) {
            // Log error or handle it based on your application's needs
            error_log("Failed to fetch registered courses for student_id: $studentId, semester: $semester");
            return [];
        }
        // Return the result, which should be an array of courses
        return $result;
    }

    //Register a student for a course
    public function registerCourse($studentId, $courseId, $semester) {
        $sql = "INSERT INTO tblregistrations (student_id, course_id, semester) 
                VALUES (:student_id, :course_id, :semester)";
        $params = [
            'student_id' => $studentId,
            'course_id' => $courseId,
            'semester' => $semester,
        ];
        return $this->executeQuery($sql, $params);
    }

    //Delete a class from a student's schedule
    public function deleteCourse($studentId, $courseId, $semester) {
        $sql = "DELETE FROM tblregistrations 
                WHERE student_id = :student_id AND course_id = :course_id AND semester = :semester";
        $params = [
            'student_id' => $studentId,
            'course_id' => $courseId,
            'semester' => $semester
        ];
        return $this->executeQuery($sql, $params);
    }
}
?>