Hi! Could we please enable some services and cookies to improve your experience and our website?
Online Sandbox for SQL and PHP: Write, Run, Test, and Share SQL Queries and PHP Code
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL
);
CREATE TABLE documents (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
user_id INTEGER,
FOREIGN KEY(user_id) REFERENCES users(id)
);
INSERT INTO users (name) VALUES
("Иван"), ("Мария"), ("Алексей");
INSERT INTO documents (title, user_id) VALUES
("Договор аренды", 1),
("Заявление на отпуск", 1),
("Отчет по проекту", 2);
<?php
class DocumentManager{
private $pdo;
public function __construct($pdo){
$this->pdo = $pdo;
}
public function getUsersWithDocs(){
$stmt = $this->pdo->prepare("
SELECT users.name, documents.title
FROM users
LEFT JOIN documents on users.id = documents.user_id
ORDER BY users.name, documents.title
");
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
public function addDocument($userId, $title){
$stmt = $this->pdo->prepare("
INSERT INTO documents (title, user_id)
VALUES(:title, :user_id)
");
$stmt->execute([
'title' => $title,
'user_id' -=> $userId
]);
}
}
$stmtUsers = $pdo->query("SELECT id, name FROM users ORDER BY name");
$users = $stmtUsers->fetchAll(PDO::FETCH_ASSOC);
$manager = new DocumentManager($pdo);
$data = $manager->getUsersWithDocs();
$currentUser = "";
echo "<h2>Список сотрудников и их документов</h2>";
foreach($data as $row){
$user = htmlspecialchars($row['name']);
$doc = isset($row['title']) ? htmlspecialchars($row['title']) : 'Нет документов';
if ($user !== $currentUser){
if ($currentUser !== "") echo "<br>";
echo "<strong>$user</strong><br>";
$currentUser = $user;
}
if ($_SERVER['REQUEST_METHOD'] === 'POST'){
$userId = (int) $_POST['user_id'];
$title = htmlspecialchars(trim($_POST['title']));
if ($title && $userId){
$manager->addDocument($userId, $title);
echo "Документ добавлен!";
}
}
foreach($$users as $user){
$uid = (int)$user['id'];
$uname = htmlspecialchars($user['name']);
echo "<option value=\"$uid\">$uname</option>";
}
echo '</select>
</label><br><br>
<label>Название документа:
<input type="text" name="title" required>
</label><br><br>
<button type="submit">Добавить</button>
</form><hr>';
?>