my_projects.php

<?php
// Include database connection and authentication
require_once 'includes/db_connect.php';
require_once 'includes/domain_utils.php';
require_once 'includes/auth_check.php';

// This page is for students only
requireStudent();

// Get student ID from session
$studentId = $_SESSION['user_id'];
$error = '';
$studentData = null;
$studentProjects = [];
$studentName = "Student";

// Check if students_info table exists
$tableCheckQuery = "SHOW TABLES LIKE 'students_info'";
$tableExists = $conn->query($tableCheckQuery)->num_rows > 0;

if ($tableExists) {
    // Get student data structure to find the ID field
    $columnsQuery = "SHOW COLUMNS FROM students_info";
    $columnsResult = $conn->query($columnsQuery);
    $studentIdField = null;
    $studentNameFields = [];
    
    // Find student ID and name fields
    while ($column = $columnsResult->fetch_assoc()) {
        if (preg_match('/(student|stud|roll)[\s_-]?(id|number|no)/i', $column['Field'])) {
            $studentIdField = $column['Field'];
        }
        
        if (preg_match('/(student|stud)[\s_-]?(name)/i', $column['Field']) ||
            preg_match('/(first|last|full)[\s_-]?(name)/i', $column['Field'])) {
            $studentNameFields[] = $column['Field'];
        }
    }
    
    if ($studentIdField) {
        // Fetch student data
        $studentQuery = "SELECT * FROM students_info WHERE $studentIdField = ?";
        $stmt = $conn->prepare($studentQuery);
        $stmt->bind_param('s', $studentId);
        $stmt->execute();
        $result = $stmt->get_result();
        
        if ($result && $result->num_rows > 0) {
            $studentData = $result->fetch_assoc();
            
            // Set student name
            foreach ($studentNameFields as $field) {
                if (!empty($studentData[$field])) {
                    $studentName = $studentData[$field];
                    break;
                }
            }
            
            // Find project field in student data
            $projectIdField = null;
            $domainIdField = null;
            
            foreach ($studentData as $field => $value) {
                if (preg_match('/(project)[\s_-]?(id)/i', $field)) {
                    $projectIdField = $field;
                }
                if (preg_match('/(domain)[\s_-]?(id)/i', $field)) {
                    $domainIdField = $field;
                }
            }
            
            // If specific project field found, get project details
            if ($projectIdField && !empty($studentData[$projectIdField])) {
                $projectId = $studentData[$projectIdField];
                
                // If domain field is found, use it to find the right domain table
                if ($domainIdField && !empty($studentData[$domainIdField])) {
                    $domainId = $studentData[$domainIdField];
                    // Extract domain number if format is like 'D-1'
                    if (preg_match('/D-(\d+)/', $domainId, $matches)) {
                        $domainNumber = $matches[1];
                        $domainTable = "domain_" . $domainNumber;
                        
                        // Check if table exists
                        $tableCheckQuery = "SHOW TABLES LIKE '$domainTable'";
                        if ($conn->query($tableCheckQuery)->num_rows > 0) {
                            // Get project name field for this domain
                            $projectNameField = getProjectNameField($conn, $domainTable);
                            
                            // Fetch project details
                            $projectQuery = "SELECT *, '$domainNumber' as domain_number FROM $domainTable WHERE Project_ID = ?";
                            $stmt = $conn->prepare($projectQuery);
                            $stmt->bind_param('i', $projectId);
                            $stmt->execute();
                            $projectResult = $stmt->get_result();
                            
                            if ($projectResult && $projectResult->num_rows > 0) {
                                $project = $projectResult->fetch_assoc();
                                $project['project_name_field'] = $projectNameField;
                                $studentProjects[] = $project;
                            }
                        }
                    }
                } else {
                    // If no domain field, search all domain tables
                    $domainTables = getDomainTables($conn);
                    
                    foreach ($domainTables as $domainTable) {
                        $domainNumber = str_replace('domain_', '', $domainTable);
                        
                        // Get project name field for this domain
                        $projectNameField = getProjectNameField($conn, $domainTable);
                        
                        // Check for project in this domain
                        $projectQuery = "SELECT *, '$domainNumber' as domain_number FROM $domainTable WHERE Project_ID = ?";
                        $stmt = $conn->prepare($projectQuery);
                        $stmt->bind_param('i', $projectId);
                        $stmt->execute();
                        $projectResult = $stmt->get_result();
                        
                        if ($projectResult && $projectResult->num_rows > 0) {
                            $project = $projectResult->fetch_assoc();
                            $project['project_name_field'] = $projectNameField;
                            $studentProjects[] = $project;
                            break;  // Found the project, no need to search other tables
                        }
                    }
                }
            } else {
                // If no specific project field or project ID is empty, 
                // try to find projects by searching for the student ID in all domain tables
                $domainTables = getDomainTables($conn);
                
                foreach ($domainTables as $domainTable) {
                    $domainNumber = str_replace('domain_', '', $domainTable);
                    
                    // Get columns of the domain table
                    $columnsQuery = "SHOW COLUMNS FROM $domainTable";
                    $columnsResult = $conn->query($columnsQuery);
                    $studentColumns = [];
                    
                    // Find columns that might contain student IDs
                    while ($column = $columnsResult->fetch_assoc()) {
                        if (preg_match('/(student|stud|member)[\s_-]?(id|ids|no|number)/i', $column['Field'])) {
                            $studentColumns[] = $column['Field'];
                        }
                    }
                    
                    // Get project name field for this domain
                    $projectNameField = getProjectNameField($conn, $domainTable);
                    
                    // Check each potential student column
                    foreach ($studentColumns as $studentColumn) {
                        // Try exact match
                        $projectQuery = "SELECT *, '$domainNumber' as domain_number FROM $domainTable WHERE $studentColumn = ?";
                        $stmt = $conn->prepare($projectQuery);
                        $stmt->bind_param('s', $studentId);
                        $stmt->execute();
                        $projectResult = $stmt->get_result();
                        
                        if ($projectResult && $projectResult->num_rows > 0) {
                            while ($project = $projectResult->fetch_assoc()) {
                                $project['project_name_field'] = $projectNameField;
                                $studentProjects[] = $project;
                            }
                        }
                        
                        // Try LIKE match (if the column might contain multiple IDs)
                        $projectQuery = "SELECT *, '$domainNumber' as domain_number FROM $domainTable WHERE $studentColumn LIKE ?";
                        $likeParam = "%$studentId%";
                        $stmt = $conn->prepare($projectQuery);
                        $stmt->bind_param('s', $likeParam);
                        $stmt->execute();
                        $projectResult = $stmt->get_result();
                        
                        if ($projectResult && $projectResult->num_rows > 0) {
                            while ($project = $projectResult->fetch_assoc()) {
                                // Check if this project is already added
                                $duplicate = false;
                                foreach ($studentProjects as $existingProject) {
                                    if ($existingProject['Project_ID'] == $project['Project_ID'] && 
                                        $existingProject['domain_number'] == $project['domain_number']) {
                                        $duplicate = true;
                                        break;
                                    }
                                }
                                
                                if (!$duplicate) {
                                    $project['project_name_field'] = $projectNameField;
                                    $studentProjects[] = $project;
                                }
                            }
                        }
                    }
                }
            }
        } else {
            $error = "Your student record was not found in the database. Please contact your administrator.";
        }
    } else {
        $error = "Could not determine student ID field in the database.";
    }
} else {
    $error = "Student information table not found in the database.";
}

// Get domain descriptions for displaying domain names
$domainDescriptions = getDomainDescriptions($conn);

// Include header
include 'includes/header.php';
?>

<div class="row mb-4">
    <div class="col-md-12">
        <nav aria-label="breadcrumb">
            <ol class="breadcrumb">
                <li class="breadcrumb-item"><a href="index.php">Home</a></li>
                <li class="breadcrumb-item active">My Projects</li>
            </ol>
        </nav>
        <div class="d-flex justify-content-between align-items-center">
            <h2><i class="fas fa-project-diagram me-2"></i> My Projects</h2>
        </div>
        <p class="lead">View all projects assigned to you</p>
    </div>
</div>

<?php if (!empty($error)): ?>
    <div class="alert alert-danger">
        <i class="fas fa-exclamation-circle me-2"></i> <?php echo $error; ?>
    </div>
<?php endif; ?>

<div class="row">
    <div class="col-md-12">
        <div class="card shadow-sm">
            <div class="card-header bg-primary text-white">
                <h5 class="mb-0"><i class="fas fa-list me-2"></i> Project List</h5>
            </div>
            <div class="card-body">
                <?php if (count($studentProjects) > 0): ?>
                    <div class="table-responsive">
                        <table class="table table-striped table-hover">
                            <thead class="table-light">
                                <tr>
                                    <th>Project ID</th>
                                    <th>Project Name</th>
                                    <th>Type</th>
                                    <th>Domain</th>
                                    <th>Actions</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach ($studentProjects as $project): 
                                    $domainNumber = $project['domain_number'];
                                    $domainDesc = isset($domainDescriptions[$domainNumber]) ? $domainDescriptions[$domainNumber] : 'Domain ' . $domainNumber;
                                    $projectName = isset($project[$project['project_name_field']]) ? $project[$project['project_name_field']] : 'Project ' . $project['Project_ID'];
                                ?>
                                    <tr>
                                        <td><?php echo htmlspecialchars($project['Project_ID']); ?></td>
                                        <td><?php echo htmlspecialchars($projectName); ?></td>
                                        <td>
                                            <?php if (isset($project['H/S'])): ?>
                                                <span class="badge bg-<?php echo strtolower($project['H/S']) === 'h' ? 'warning' : 'info'; ?>">
                                                    <?php echo strtolower($project['H/S']) === 'h' ? 'Hardware' : 'Software'; ?>
                                                </span>
                                            <?php else: ?>
                                                <span class="badge bg-secondary">Not specified</span>
                                            <?php endif; ?>
                                        </td>
                                        <td>Domain <?php echo htmlspecialchars($domainNumber); ?>: <?php echo htmlspecialchars($domainDesc); ?></td>
                                        <td>
                                            <a href="project_details.php?domain=<?php echo urlencode($domainNumber); ?>&id=<?php echo urlencode($project['Project_ID']); ?>" class="btn btn-sm btn-info">
                                                <i class="fas fa-info-circle"></i> Details
                                            </a>
                                        </td>
                                    </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>
                <?php else: ?>
                    <div class="alert alert-info">
                        <i class="fas fa-info-circle me-2"></i> No projects are currently assigned to you. If you believe this is an error, please contact your faculty advisor.
                    </div>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>

<?php
// Include footer
include 'includes/footer.php';

// Close connection
$conn->close();
?>