📊 UserHub · Dashboard & DB

⚡ Live SQL + PHP Backend
Total Users
—
active records
Admins
—
role: admin
Editors
—
content managers
Viewers
—
read-only access
✍️ Add / Update User
👥 Registered users
Loading users...
setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Create table if not exists $db->exec("CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, email TEXT UNIQUE NOT NULL, role TEXT NOT NULL DEFAULT 'viewer', created_at DATETIME DEFAULT CURRENT_TIMESTAMP )"); $method = $_SERVER['REQUEST_METHOD']; // Helper to return JSON error function sendError($msg, $code=400) { http_response_code($code); echo json_encode(['success'=>false, 'error'=>$msg]); exit; } // GET requests - fetch stats or user list if ($method === 'GET') { $action = $_GET['action'] ?? ''; if ($action === 'getStats') { $total = $db->query("SELECT COUNT(*) FROM users")->fetchColumn(); $admin = $db->query("SELECT COUNT(*) FROM users WHERE role='admin'")->fetchColumn(); $editor = $db->query("SELECT COUNT(*) FROM users WHERE role='editor'")->fetchColumn(); $viewer = $db->query("SELECT COUNT(*) FROM users WHERE role='viewer'")->fetchColumn(); echo json_encode(['success'=>true, 'total'=>$total, 'admin'=>$admin, 'editor'=>$editor, 'viewer'=>$viewer]); exit; } elseif ($action === 'getUsers') { $stmt = $db->query("SELECT id, name, email, role FROM users ORDER BY id DESC"); $users = $stmt->fetchAll(PDO::FETCH_ASSOC); echo json_encode($users); exit; } else { sendError('Invalid action', 400); } } // POST requests (create, update, delete, seed) if ($method === 'POST') { $action = $_POST['action'] ?? ''; // CREATE user if ($action === 'createUser') { $name = trim($_POST['name'] ?? ''); $email = trim($_POST['email'] ?? ''); $role = $_POST['role'] ?? 'viewer'; if (!$name || !$email) sendError('Name and email required'); if (!in_array($role, ['admin','editor','viewer'])) $role = 'viewer'; try { $stmt = $db->prepare("INSERT INTO users (name, email, role) VALUES (:name, :email, :role)"); $stmt->execute([':name'=>$name, ':email'=>$email, ':role'=>$role]); echo json_encode(['success'=>true, 'id'=>$db->lastInsertId()]); } catch(PDOException $e) { if (strpos($e->getMessage(), 'UNIQUE') !== false) sendError('Email already exists', 409); else sendError('DB error: '.$e->getMessage()); } exit; } // UPDATE user if ($action === 'updateUser') { $id = (int)($_POST['id'] ?? 0); $name = trim($_POST['name'] ?? ''); $email = trim($_POST['email'] ?? ''); $role = $_POST['role'] ?? 'viewer'; if (!$id || !$name || !$email) sendError('ID, name and email required'); if (!in_array($role, ['admin','editor','viewer'])) $role = 'viewer'; try { $stmt = $db->prepare("UPDATE users SET name=:name, email=:email, role=:role WHERE id=:id"); $stmt->execute([':name'=>$name, ':email'=>$email, ':role'=>$role, ':id'=>$id]); if ($stmt->rowCount() === 0) sendError('User not found or no changes', 404); echo json_encode(['success'=>true]); } catch(PDOException $e) { if (strpos($e->getMessage(), 'UNIQUE') !== false) sendError('Email already taken', 409); else sendError('Update error: '.$e->getMessage()); } exit; } // DELETE user if ($action === 'deleteUser') { $id = (int)($_POST['id'] ?? 0); if (!$id) sendError('Valid ID required'); $stmt = $db->prepare("DELETE FROM users WHERE id = :id"); $stmt->execute([':id'=>$id]); if ($stmt->rowCount() === 0) sendError('User not found', 404); echo json_encode(['success'=>true]); exit; } // SEED demo users (avoid duplicates) if ($action === 'seedDemo') { $demoUsers = [ ['name'=>'Olivia Chen', 'email'=>'olivia@example.com', 'role'=>'admin'], ['name'=>'Marcus Rivera', 'email'=>'marcus@example.com', 'role'=>'editor'], ['name'=>'Sophia Williams', 'email'=>'sophia@example.com', 'role'=>'viewer'] ]; $inserted = 0; foreach ($demoUsers as $user) { try { $check = $db->prepare("SELECT id FROM users WHERE email = :email"); $check->execute([':email'=>$user['email']]); if (!$check->fetchColumn()) { $stmt = $db->prepare("INSERT INTO users (name, email, role) VALUES (:name, :email, :role)"); $stmt->execute([':name'=>$user['name'], ':email'=>$user['email'], ':role'=>$user['role']]); $inserted++; } } catch(Exception $e) { /* ignore individual fails */ } } echo json_encode(['success'=>true, 'inserted'=>$inserted, 'message'=>"Added $inserted demo users"]); exit; } sendError('Unknown POST action', 400); } sendError('Unsupported method', 405); } catch(PDOException $e) { http_response_code(500); echo json_encode(['success'=>false, 'error'=>'Database connection failed: '.$e->getMessage()]); exit; } ?>