Работа с базами данных в PHP

[Intel]

Руководство по выполнению запросов к базам данных в PHP с использованием PDO и mysqli.

Подключение через PDO

<?php
try {
    $pdo = new PDO(
        'mysql:host=localhost;dbname=mydb;charset=utf8mb4',
        'username',
        'password',
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ]
    );
    echo "Подключение успешно";
} catch (PDOException $e) {
    echo "Ошибка подключения: " . $e->getMessage();
}
?>

SELECT запросы с PDO

<?php
// Простой SELECT
$stmt = $pdo->query("SELECT * FROM users");
$users = $stmt->fetchAll();

foreach ($users as $user) {
    echo $user['name'] . " - " . $user['email'] . "\n";
}

// SELECT с параметрами
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([1]);
$user = $stmt->fetch();

if ($user) {
    echo $user['name'];
}

// SELECT с именованными параметрами
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email AND status = :status");
$stmt->execute([
    'email' => 'user@example.com',
    'status' => 'active'
]);
$user = $stmt->fetch();
?>

INSERT запросы

<?php
// Простой INSERT
$stmt = $pdo->prepare("INSERT INTO users (name, email, password) VALUES (?, ?, ?)");
$stmt->execute(['Иван', 'ivan@example.com', password_hash('pass123', PASSWORD_DEFAULT)]);

$userId = $pdo->lastInsertId();
echo "Создан пользователь с ID: " . $userId;

// Множественный INSERT
$users = [
    ['Петр', 'petr@example.com', 'pass1'],
    ['Мария', 'maria@example.com', 'pass2'],
    ['Анна', 'anna@example.com', 'pass3']
];

$stmt = $pdo->prepare("INSERT INTO users (name, email, password) VALUES (?, ?, ?)");
foreach ($users as $user) {
    $stmt->execute($user);
}
?>

UPDATE запросы

<?php
$stmt = $pdo->prepare("UPDATE users SET name = ?, email = ? WHERE id = ?");
$stmt->execute(['Новое имя', 'newemail@example.com', 1]);

$affectedRows = $stmt->rowCount();
echo "Обновлено строк: " . $affectedRows;

// UPDATE с условием
$stmt = $pdo->prepare("UPDATE users SET status = 'inactive' WHERE last_login < ?");
$stmt->execute([date('Y-m-d', strtotime('-30 days'))]);
?>

DELETE запросы

<?php
$stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
$stmt->execute([1]);

$deletedRows = $stmt->rowCount();
echo "Удалено строк: " . $deletedRows;

// Безопасное удаление с проверкой
$stmt = $pdo->prepare("DELETE FROM users WHERE id = ? AND status = 'inactive'");
$stmt->execute([1]);
?>

Транзакции

<?php
try {
    $pdo->beginTransaction();
    
    $stmt = $pdo->prepare("INSERT INTO orders (user_id, total) VALUES (?, ?)");
    $stmt->execute([1, 1000]);
    $orderId = $pdo->lastInsertId();
    
    $stmt = $pdo->prepare("INSERT INTO order_items (order_id, product_id, quantity) VALUES (?, ?, ?)");
    $stmt->execute([$orderId, 1, 2]);
    $stmt->execute([$orderId, 2, 1]);
    
    $pdo->commit();
    echo "Заказ создан успешно";
} catch (PDOException $e) {
    $pdo->rollBack();
    echo "Ошибка: " . $e->getMessage();
}
?>

Работа с mysqli

<?php
$mysqli = new mysqli('localhost', 'username', 'password', 'mydb');

if ($mysqli->connect_error) {
    die("Ошибка подключения: " . $mysqli->connect_error);
}

// SELECT
$result = $mysqli->query("SELECT * FROM users");
while ($row = $result->fetch_assoc()) {
    echo $row['name'] . "\n";
}
$result->free();

// Подготовленный запрос
$stmt = $mysqli->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $id);
$id = 1;
$stmt->execute();
$result = $stmt->get_result();
$user = $result->fetch_assoc();
$stmt->close();

// INSERT
$stmt = $mysqli->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);
$name = "Иван";
$email = "ivan@example.com";
$stmt->execute();
$stmt->close();

$mysqli->close();
?>

Поиск и фильтрация

<?php
// Поиск по нескольким полям
$search = '%test%';
$stmt = $pdo->prepare("SELECT * FROM users WHERE name LIKE ? OR email LIKE ?");
$stmt->execute([$search, $search]);
$results = $stmt->fetchAll();

// Сортировка и лимит
$stmt = $pdo->prepare("SELECT * FROM users ORDER BY created_at DESC LIMIT ? OFFSET ?");
$stmt->execute([10, 0]);
$users = $stmt->fetchAll();

// Группировка
$stmt = $pdo->query("SELECT status, COUNT(*) as count FROM users GROUP BY status");
$stats = $stmt->fetchAll();
?>

Сложные запросы с JOIN

<?php
// INNER JOIN
$stmt = $pdo->query("
    SELECT u.name, u.email, o.total, o.created_at
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    WHERE o.status = 'completed'
");

$orders = $stmt->fetchAll();

// LEFT JOIN
$stmt = $pdo->query("
    SELECT u.name, COUNT(o.id) as order_count
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.id
");

$userStats = $stmt->fetchAll();
?>

Подсчет записей

<?php
// COUNT
$stmt = $pdo->query("SELECT COUNT(*) as total FROM users");
$total = $stmt->fetch()['total'];

// COUNT с условием
$stmt = $pdo->prepare("SELECT COUNT(*) as active FROM users WHERE status = ?");
$stmt->execute(['active']);
$active = $stmt->fetch()['active'];

// SUM, AVG, MAX, MIN
$stmt = $pdo->query("
    SELECT 
        COUNT(*) as total,
        SUM(total) as revenue,
        AVG(total) as avg_order,
        MAX(total) as max_order,
        MIN(total) as min_order
    FROM orders
");
$stats = $stmt->fetch();
?>

Обработка ошибок

<?php
try {
    $stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
    $stmt->execute(['Иван', 'ivan@example.com']);
} catch (PDOException $e) {
    if ($e->getCode() == 23000) {
        echo "Пользователь с таким email уже существует";
    } else {
        echo "Ошибка: " . $e->getMessage();
    }
}
?>

Экранирование данных

<?php
// PDO автоматически экранирует при использовании prepared statements
$stmt = $pdo->prepare("SELECT * FROM users WHERE name = ?");
$stmt->execute([$_GET['name']]); // Безопасно

// Для mysqli
$name = $mysqli->real_escape_string($_GET['name']);
$result = $mysqli->query("SELECT * FROM users WHERE name = '$name'");
?>

Практический пример: класс для работы с БД

<?php
class Database {
    private $pdo;
    
    public function __construct() {
        $this->pdo = new PDO(
            'mysql:host=localhost;dbname=mydb;charset=utf8mb4',
            'username',
            'password',
            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
        );
    }
    
    public function getUser($id) {
        $stmt = $this->pdo->prepare("SELECT * FROM users WHERE id = ?");
        $stmt->execute([$id]);
        return $stmt->fetch();
    }
    
    public function createUser($name, $email, $password) {
        $stmt = $this->pdo->prepare("INSERT INTO users (name, email, password) VALUES (?, ?, ?)");
        $stmt->execute([$name, $email, password_hash($password, PASSWORD_DEFAULT)]);
        return $this->pdo->lastInsertId();
    }
    
    public function updateUser($id, $data) {
        $fields = [];
        $values = [];
        foreach ($data as $key => $value) {
            $fields[] = "$key = ?";
            $values[] = $value;
        }
        $values[] = $id;
        
        $sql = "UPDATE users SET " . implode(', ', $fields) . " WHERE id = ?";
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute($values);
    }
}

$db = new Database();
$userId = $db->createUser('Иван', 'ivan@example.com', 'pass123');
$user = $db->getUser($userId);
?>

Работа с базами данных - основа веб-разработки. PDO и mysqli предоставляют все необходимые инструменты для безопасной работы с данными.