Организация взаимодействия интерфейса пользователя с базой данных через PHP

Расширенный пример: CRUD приложение с AJAX и PHP MySQL

В этом примере реализованы операции создания, чтения, обновления и удаления записей (users) через асинхронные запросы. Используется один PHP файл api.php, который обрабатывает все действия на основе параметра action и HTTP метода.

Структура базы данных:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

PHP api.php - полный код:

<?php
header('Content-Type: application/json; charset=utf-8');
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, POST, PUT, DELETE, OPTIONS');

if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') {
    exit(0);
}

$mysqli = new mysqli('localhost', 'root', '', 'test');
$mysqli->set_charset('utf8mb4');

$method = $_SERVER['REQUEST_METHOD'];
$action = $_GET['action'] ?? '';

switch ($action) {
    case 'list':
        if ($method !== 'GET') {
            http_response_code(405);
            echo json_encode(['error' => 'Method not allowed']);
            break;
        }
        $result = $mysqli->query('SELECT id, name, email, created_at FROM users ORDER BY id DESC');
        $users = $result->fetch_all(MYSQLI_ASSOC);
        echo json_encode($users);
        break;

    case 'get':
        if ($method !== 'GET') {
            http_response_code(405);
            break;
        }
        $id = (int)$_GET['id'];
        $stmt = $mysqli->prepare('SELECT id, name, email FROM users WHERE id = ?');
        $stmt->bind_param('i', $id);
        $stmt->execute();
        $result = $stmt->get_result();
        $user = $result->fetch_assoc();
        echo json_encode($user ?: ['error' => 'User not found']);
        break;

    case 'create':
        if ($method !== 'POST') {
            http_response_code(405);
            break;
        }
        $input = json_decode(file_get_contents('php://input'), true);
        $name = $input['name'] ?? '';
        $email = $input['email'] ?? '';
        if (empty($name) || empty($email)) {
            http_response_code(400);
            echo json_encode(['error' => 'Name and email required']);
            break;
        }
        $stmt = $mysqli->prepare('INSERT INTO users (name, email) VALUES (?, ?)');
        $stmt->bind_param('ss', $name, $email);
        if ($stmt->execute()) {
            echo json_encode(['id' => $stmt->insert_id, 'message' => 'User created']);
        } else {
            http_response_code(500);
            echo json_encode(['error' => 'Database error']);
        }
        break;

    case 'update':
        if ($method !== 'PUT') {
            http_response_code(405);
            break;
        }
        $input = json_decode(file_get_contents('php://input'), true);
        $id = (int)$_GET['id'];
        $name = $input['name'] ?? '';
        $email = $input['email'] ?? '';
        $stmt = $mysqli->prepare('UPDATE users SET name = ?, email = ? WHERE id = ?');
        $stmt->bind_param('ssi', $name, $email, $id);
        if ($stmt->execute() && $stmt->affected_rows > 0) {
            echo json_encode(['message' => 'User updated']);
        } else {
            http_response_code(404);
            echo json_encode(['error' => 'User not found or no changes']);
        }
        break;

    case 'delete':
        if ($method !== 'DELETE') {
            http_response_code(405);
            break;
        }
        $id = (int)$_GET['id'];
        $stmt = $mysqli->prepare('DELETE FROM users WHERE id = ?');
        $stmt->bind_param('i', $id);
        if ($stmt->execute() && $stmt->affected_rows > 0) {
            echo json_encode(['message' => 'User deleted']);
        } else {
            http_response_code(404);
            echo json_encode(['error' => 'User not found']);
        }
        break;

    default:
        http_response_code(404);
        echo json_encode(['error' => 'Unknown action']);
}
$mysqli->close();
?>

Фронтенд (HTML + JavaScript):

<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>CRUD Users</title>
</head>
<body>
    <h2>Управление пользователями</h2>
    <div>
        <input type="text" id="name" placeholder="Имя">
        <input type="email" id="email" placeholder="Email">
        <button onclick="createUser()">Добавить</button>
    </div>
    <table border="1">
        <thead><tr><th>ID</th><th>Имя</th><th>Email</th><th>Действия</th></tr></thead>
        <tbody id="userList"></tbody>
    </table>
    <script>
    async function fetchUsers() {
        const res = await fetch('api.php?action=list');
        const data = await res.json();
        const tbody = document.getElementById('userList');
        tbody.innerHTML = '';
        data.forEach(user => {
            const tr = document.createElement('tr');
            tr.innerHTML = `<td>${user.id}</td><td>${user.name}</td><td>${user.email}</td>
                <td><button onclick="deleteUser(${user.id})">Удалить</button></td>`;
            tbody.appendChild(tr);
        });
    }
    async function createUser() {
        const name = document.getElementById('name').value;
        const email = document.getElementById('email').value;
        const res = await fetch('api.php?action=create', {
            method: 'POST',
            headers: {'Content-Type': 'application/json'},
            body: JSON.stringify({name, email})
        });
        const data = await res.json();
        if (data.error) alert(data.error);
        else fetchUsers();
    }
    async function deleteUser(id) {
        const res = await fetch('api.php?action=delete&id=' + id, {method: 'DELETE'});
        const data = await res.json();
        if (data.error) alert(data.error);
        else fetchUsers();
    }
    fetchUsers();
    </script>
</body>
</html>

Примеры ответов JSON от API:

// GET api.php?action=list
[
  {"id":1,"name":"Иван","email":"ivan@example.com","created_at":"2025-03-10 12:00:00"},
  {"id":2,"name":"Мария","email":"maria@test.com","created_at":"2025-03-10 12:05:00"}
]
// POST api.php?action=create  body: {"name":"Петр","email":"peter@mail.ru"}
{"id":3,"message":"User created"}
// DELETE api.php?action=delete&id=3
{"message":"User deleted"}
// GET api.php?action=get&id=99
{"error":"User not found"}