PDO. Основы работы с MySQL

Почему стоит использовать PDO

Функции mysql в PHP для работы с БД устарели, на сегодняшний день желательно использовать mysqli или PDO (PHP Data Objects).

mysqli - эта библиотека, которая по большому счёту, не предназначена для использования напрямую в коде. Она может послужить хорошим строительным материалом для создания библиотеки более высокого уровня.

При работе с mysqli следует также помнить об обеспечении безопасности вашего приложения, в частности о защите от SQL-инъекций. В случае использования PDO (с его подготовленными запросами), такая защита идёт уже «из коробки», главное правильно применить необходимые методы.

Установка PDO

Установка в Linux:

sudo apt update
sudo apt install php7.4-mysql
sudo apt-get install pdo-mysql

В php.ini добавить:

extension=pdo.so
extension=pdo_mysql.so

На Windows, как правило драйвер уже установлен, нужно просто проверить включен ли он в php.ini

extension=php_pdo_mysql.dll

Проверить доступные драйвера

print_r(PDO::getAvailableDrivers());

Соединение с базой данных

Соединения устанавливаются автоматически при создании объекта PDO от его базового класса.

Пример #1. Простое подключение:

$db = new PDO('mysql:host=localhost;dbname=pdo', 'root', 'password');

При ошибке подключения PHP выдаст ошибку:

Fatal error: Uncaught PDOException: ...

Пример #2. Обработка ошибок подключения

try {
  $dbh = new PDO('mysql:host=localhost;dbname=pdo', 'root', 'password');
} catch (PDOException $e) {
  print "Error!: " . $e->getMessage();
  die();
}

В этом примере подключения используем конструкцию try...catch.

Подготовленные и прямые запросы

В PDO два способа выполнения запросов:

Прямой - состоит из одного шага;
Подготовленный - состоит из двух шагов.

Прямые запросы

query() используется для операторов, которые не вносят изменения, например SELECT. Возвращает объект PDOStatemnt, из которого с помощью методов fetch() или fetchAll извлекаются результаты запроса. Можно его сравнить с mysql resource, который возвращала mysql_query().
exec() используется для операторов INSERT, DELETE, UPDATE. Возвращает число обработанных запросом строк.

Прямые запросы используются только в том случае, если в запросе отсутствуют переменные и есть уверенность, что запрос безопасен и правильно экранирован.

$stmt = $db->query("SELECT * FROM categories");
while ($row = $stmt->fetch())
{
  echo '<pre>';
  print_r($row);
}

Подготовленные запросы

Если же в запрос передаётся хотя бы одна переменная, то этот запрос в обязательном порядке должен выполняться только через подготовленные выражения.

Подготовленное выражение - это обычный SQL запрос, в котором вместо переменной ставится специальный маркер - плейсхолдер.

PDO поддерживает позиционные плейсхолдеры (?), для которых важен порядок передаваемых переменных, и именованные (:name), для которых порядок не важен. Примеры:

$sql = "SELECT name FROM categories WHERE id = ?";
$sql = "SELECT name FROM categories WHERE name = :name";

Чтобы выполнить такой запрос, сначала его надо подготовить с помощью метода prepare(). Он также возвращает PDO statement, но ещё без данных. Чтобы их получить, надо исполнить этот запрос, предварительно передав в него наши переменные.

Передать можно двумя способами просто выполнив метод execute() и передав ему массив с переменными:

вар.1

$stmt = $pdo->prepare("SELECT `name` FROM categories WHERE `id` = ?");
$stmt->execute([$id]);

вар.2

$stmt = $pdo->prepare("SELECT `name` FROM categories WHERE `name` = :name");
$stmt->execute(['name' => $name]);

в случае именованных плейсхолдеров в execute() должен передаваться массив, в котором ключи должны совпадать с именами плейсхолдеров.

После этого можно извлечь результаты запроса:

$id = 1;
$stmt = $db->prepare("SELECT * FROM categories WHERE `id` = ?");
$stmt->execute([$id]);
$category = $stmt->fetch(PDO::FETCH_LAZY);
echo '<pre>';
print_r($category);

Важно! Подготовленные запросы - основная причина использовать PDO, поскольку это единственный безопасный способ выполнения SQL запросов, в которых участвуют переменные.

Получение данных.

Метод fetch()

Мы уже выше познакомились с методом fetch(), который служит для последовательного получения строк из БД.

У метода fetch() имеется множество праметров, которые здесь описываться не будут.
В основном же рекомендуется применять fetch() в режиме FETCH_LAZY

$id = 1;
$stmt = $db->prepare("SELECT * FROM categories WHERE `id` = ?");
$stmt->execute([$id]);
while ($row = $stmt->fetch(PDO::FETCH_LAZY)) {
    echo 'Category name: '.$row->name;
}

В этом режиме не тратится лишняя память, и к тому же к колонкам можно обращаться любым из трех способов - через индекс, имя, или свойство (через ->).

Недостатком данного режима является то, что он не работает с fetchAll()

Метод fetchColumn()

Это метод для получения значения единственной колонки. Очень удобно, если запрашиваем только одно поле - в этом случае значительно сокращается количество кода:

$id = 1;
$stmt = $db->prepare("SELECT `name` FROM categories WHERE `id` = ?");
$stmt->execute([$id]);
$name = $stmt->fetchColumn();
echo 'Category name: '.$name;

Метод fetchAll()

$data = $db->query("SELECT * FROM categories")->fetchAll(PDO::FETCH_ASSOC);
foreach ($data as $k => $v){
  echo 'Category name: '.$v['name'].'<br>';
}

PDO и использование операторов

Работая с подготовленными запросами, следует понимать, что плейсхолдер может заменять только строку или число. Ни ключевое слово, ни идентификатор, ни часть строки или набор строк через плейсхолдер подставить нельзя.

PDO и оператор LIKE

Поэтому для LIKE необходимо сначала подготовить строку поиска целиком, а потом ее подставлять в запрос:

$search = 'комп';
$query = "SELECT * FROM categories WHERE `name` LIKE ?";
$params = ["%$search%"];
$stmt = $db->prepare($query);
$stmt->execute($params);
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
$i = 1;
foreach ($data as $category){
  echo $i++ . '. ' . $category['name'].'<br>';
}

Здесь может вознинуть проблема! Поиск может не работать, потому как из базы у вас приходят данные в неправильной кодировке. Необходимо добавить кодировку в подключение, если она там не указана:

$db = new PDO('mysql:host=localhost;dbname=pdo;charset=utf8mb4', 'root', '');

PDO и оператор LIMIT

==Важно!== Когда PDO работает в режиме эмуляции, все данные, которые были переданы напрямую в execute(), форматируются как строки. То есть, эскейпятся и обрамляются кавычками. Поэтому LIMIT ?,? превращается в LIMIT '10', '10' и очевидным образом вызывает ошибку синтаксиса и, соответственно, пустой массив данных.

Решение #1: Отключить режим эмуляции:

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Решение #2: Биндить эти цифры через bindValue(), принудительно выставляя им тип PDO::PARAM_INT:

$limit = 3;
$stm = $db->prepare('SELECT * FROM categories LIMIT ?');
$stm->bindValue(1, $limit, PDO::PARAM_INT);
$stm->execute();
$data = $stm->fetchAll();
echo '<pre>';
print_r($data);

PDO и оператор IN

При выборке из таблицы необходимо доставать записи, соответствующие всем значениям массива.

$arr = [1,3,6];
$in  = str_repeat('?,', count($arr) - 1) . '?';
$sql = "SELECT * FROM categories WHERE `id` IN ($in)";
$stm = $db->prepare($sql);
$stm->execute($arr);
$data = $stm->fetchAll();
echo '<pre>';
print_r($data);

Примеры

Добавление записей

$name = 'Новая категория';
$query = "INSERT INTO `categories` (`name`) VALUES (:name)";
$params = [
    ':name' => $name
];
$stmt = $pdo->prepare($query);
$stmt->execute($params);

Изменение записей

$id = 1;
$name = 'Изменённая запись';
$query = "UPDATE `categories` SET `name` = :name WHERE `id` = :id";
$params = [
    ':id' => $id,
    ':name' => $name
];
$stmt = $pdo->prepare($query);
$stmt->execute($params);

Удаление записей

$id = 1;
$query = "DELETE FROM `categories` WHERE `id` = ?";
$params = [$id];
$stmt = $pdo->prepare($query);
$stmt->execute($params);

Содержание