students.php

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

// This page is for faculty only
requireFaculty();

// Initialize variables
$students = [];
$searchTerm = '';
$error = '';

// 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
    $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'];
        }
    }
    
    // Handle search
    if (isset($_GET['search']) && !empty($_GET['search'])) {
        $searchTerm = trim($_GET['search']);
        
        // Prepare search conditions
        $conditions = [];
        $params = [];
        $types = '';
        
        // Search by ID
        if ($studentIdField) {
            $conditions[] = "$studentIdField LIKE ?";
            $params[] = "%$searchTerm%";
            $types .= 's';
        }
        
        // Search by name fields
        foreach ($studentNameFields as $field) {
            $conditions[] = "$field LIKE ?";
            $params[] = "%$searchTerm%";
            $types .= 's';
        }
        
        if (!empty($conditions)) {
            $sql = "SELECT * FROM students_info WHERE " . implode(' OR ', $conditions);
            $stmt = $conn->prepare($sql);
            
            if (!empty($params)) {
                $stmt->bind_param($types, ...$params);
            }
            
            $stmt->execute();
            $result = $stmt->get_result();
            
            if ($result) {
                while ($row = $result->fetch_assoc()) {
                    $students[] = $row;
                }
            } else {
                $error = "Error executing search query: " . $conn->error;
            }
        } else {
            $error = "No suitable fields found for searching.";
        }
    } else {
        // Get all students if no search
        $sql = "SELECT * FROM students_info";
        $result = $conn->query($sql);
        
        if ($result) {
            while ($row = $result->fetch_assoc()) {
                $students[] = $row;
            }
        } else {
            $error = "Error fetching students: " . $conn->error;
        }
    }
} else {
    $error = "Student information table not found in the database.";
}

// 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">Students</li>
            </ol>
        </nav>
        <div class="d-flex justify-content-between align-items-center">
            <h2><i class="fas fa-user-graduate me-2"></i> Students Information</h2>
        </div>
        <p class="lead">View and search for student details</p>
    </div>
</div>

<div class="row mb-4">
    <div class="col-md-12">
        <div class="card shadow-sm">
            <div class="card-body">
                <form method="GET" action="students.php" class="row g-3">
                    <div class="col-md-10">
                        <div class="input-group">
                            <span class="input-group-text"><i class="fas fa-search"></i></span>
                            <input type="text" name="search" class="form-control" placeholder="Search by ID or name" value="<?php echo htmlspecialchars($searchTerm); ?>">
                        </div>
                    </div>
                    <div class="col-md-2">
                        <button type="submit" class="btn btn-primary w-100">Search</button>
                    </div>
                </form>
            </div>
        </div>
    </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> Students List</h5>
            </div>
            <div class="card-body">
                <?php if (count($students) > 0): ?>
                    <div class="table-responsive">
                        <table class="table table-striped table-hover">
                            <thead class="table-light">
                                <tr>
                                    <?php 
                                    // Get the first student to determine columns
                                    $firstStudent = $students[0];
                                    foreach ($firstStudent as $key => $value): 
                                    ?>
                                        <th><?php echo htmlspecialchars($key); ?></th>
                                    <?php endforeach; ?>
                                    <th>Actions</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach ($students as $student): ?>
                                    <tr>
                                        <?php foreach ($student as $key => $value): ?>
                                            <td><?php echo htmlspecialchars($value); ?></td>
                                        <?php endforeach; ?>
                                        <td>
                                            <?php if (isset($studentIdField) && isset($student[$studentIdField])): ?>
                                                <a href="student_projects.php?id=<?php echo urlencode($student[$studentIdField]); ?>" class="btn btn-sm btn-info">
                                                    <i class="fas fa-project-diagram"></i> View Projects
                                                </a>
                                            <?php endif; ?>
                                        </td>
                                    </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>
                <?php else: ?>
                    <div class="alert alert-info">
                        <i class="fas fa-info-circle me-2"></i> No students found.
                    </div>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>

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

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