Learning-Management-System/setup_user_details_db.php at main · Nerd-Ninjas/Learning-Management-System · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
<?php
/**
 * setup_user_details_db.php
 * One-time setup: Creates the employee_details table
 * inside the existing learnlikes_gurukulam database.
 * Delete this file after running.
 */
require_once __DIR__ . '/includes/config.php'; // uses existing $con

$results = [];

$sql = "
    CREATE TABLE IF NOT EXISTS `employee_details` (
        `id`               INT(11)      NOT NULL AUTO_INCREMENT,
        `username`         VARCHAR(50)  NOT NULL COMMENT 'FK → users.username',
        `email`            VARCHAR(150) NOT NULL COMMENT 'FK → users.email',
        `company_branch`   VARCHAR(150) NOT NULL,
        `company_location` VARCHAR(255) NOT NULL,
        `department`       VARCHAR(100) NOT NULL,
        `date_of_joining`  DATE         NOT NULL,
        `status`           ENUM('active','inactive','on_leave','terminated') NOT NULL DEFAULT 'active',
        `created_at`       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at`       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `uq_employee_username` (`username`),
        UNIQUE KEY `uq_employee_email`    (`email`),
        KEY `idx_username` (`username`),
        KEY `idx_email`    (`email`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
      COMMENT='Employee details stored in learnlikes_gurukulam, linked via username & email'
";

try {
    $con->exec($sql);
    $results[] = ['type' => 'success', 'msg' => '✅ Table <strong>employee_details</strong> created (or already exists) in <strong>learnlikes_gurukulam</strong>.'];
} catch (PDOException $e) {
    $results[] = ['type' => 'error', 'msg' => '❌ Error: ' . htmlspecialchars($e->getMessage())];
}

// Verify columns
try {
    $stmt = $con->query("DESCRIBE `employee_details`");
    $cols = array_column($stmt->fetchAll(PDO::FETCH_ASSOC), 'Field');
    $results[] = ['type' => 'info', 'msg' => 'ℹ️ Columns: <code>' . implode(', ', $cols) . '</code>'];
} catch (PDOException $e) {
    $results[] = ['type' => 'error', 'msg' => '❌ Could not describe table: ' . htmlspecialchars($e->getMessage())];
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Setup: employee_details table</title>
    <style>
        body { font-family: Arial, sans-serif; max-width: 720px; margin: 60px auto; padding: 20px; background: #f5f5f5; }
        .card { background: #fff; border-radius: 10px; padding: 30px; box-shadow: 0 2px 12px rgba(0,0,0,.1); }
        h2 { color: #333; }
        .success { padding: 12px 16px; margin: 8px 0; border-radius: 6px; background: #eafaf1; border-left: 4px solid #2ecc71; }
        .info    { padding: 12px 16px; margin: 8px 0; border-radius: 6px; background: #eaf4fb; border-left: 4px solid #3498db; }
        .error   { padding: 12px 16px; margin: 8px 0; border-radius: 6px; background: #fdf2f2; border-left: 4px solid #e74c3c; }
        table { width: 100%; border-collapse: collapse; margin-top: 16px; font-size: 13px; }
        th, td { border: 1px solid #ddd; padding: 8px 12px; text-align: left; }
        th { background: #4a4a9c; color: #fff; }
        .warn { margin-top: 20px; padding: 12px 16px; background: #fff3cd; border-left: 4px solid #f0ad4e; border-radius: 6px; font-size: 13px; color: #856404; }
    </style>
</head>
<body>
<div class="card">
    <h2>🚀 employee_details Table Setup</h2>
    <p>Database: <strong>learnlikes_gurukulam</strong></p>

    <?php foreach ($results as $r): ?>
        <div class="<?= $r['type'] ?>"><?= $r['msg'] ?></div>
    <?php endforeach; ?>

    <table>
        <tr><th>Column</th><th>Type</th><th>Description</th></tr>
        <tr><td>id</td><td>INT AUTO_INCREMENT</td><td>Primary key</td></tr>
        <tr><td>username</td><td>VARCHAR(50) UNIQUE</td><td>🔑 Links to <code>users.username</code></td></tr>
        <tr><td>email</td><td>VARCHAR(150) UNIQUE</td><td>🔑 Links to <code>users.email</code></td></tr>
        <tr><td>company_branch</td><td>VARCHAR(150)</td><td>Branch / office name</td></tr>
        <tr><td>company_location</td><td>VARCHAR(255)</td><td>City or address</td></tr>
        <tr><td>department</td><td>VARCHAR(100)</td><td>Employee department</td></tr>
        <tr><td>date_of_joining</td><td>DATE</td><td>Joining date</td></tr>
        <tr><td>status</td><td>ENUM</td><td>active / inactive / on_leave / terminated</td></tr>
        <tr><td>created_at</td><td>DATETIME</td><td>Auto timestamp on insert</td></tr>
        <tr><td>updated_at</td><td>DATETIME</td><td>Auto updated on change</td></tr>
    </table>

    <div class="warn">⚠️ Delete <code>setup_user_details_db.php</code> after setup is complete.</div>
</div>
</body>
</html>