PDO (PHP Data Objects) - расширение для PHP, предоставляющее простой интерфейс для доступа к различным базам данных. Если говорить очень просто и коротко, при помощи PDO в PHP подключаются к базам данных разных типов.
В этом уроке мы будем подключаться к базе данных MySQL, так как это самая распространённая база данных.
Вы должны знать, что у сервера баз данных есть имя, также к нему могут подключаться пользователи, то есть для подключения должен быть использован логин и пароль.
Пример того, как мы можем подключиться к базе данных:
$db = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
Я думаю если вы интересуетесь PDO, то ваших знаний достаточно и этот синтаксис вам объяснять не нужно.
Итак, у нас есть объект подключения для доступа к базе данных.
Когда используется PDO, то ошибки подключений советуюю ловить при помощи конструкции try{...}catch{...} . Вот пример такого кода:
Try
{
$db = new PDO("myql:host=$host;dbname=$dbname", $user, $pass);
}
catch(PDOException $e)
{
echo "You have an error: ".$e->getMessage()."
";
echo "On line: ".$e->getLine();
}
Есть разные мнения, по поводу обработки ошибок, например не все советуют всегда использовать конструкцию try{...}catch{...} . Дело в том, что PHP и так выведет сообщение об ошибке на экран, так что этот код избыточен. Хотя если вы хотите например откатить транзакцию, то эта конструкция вам пригодится, но об этом ниже.
Для выборки из базы данных используется метод query , которому мы передаём строку запроса SQL.
$db->query("SELECT * FROM users");
Не забывайте, что для всех типов баз данных этот синтаксис сработает.
Также не забывайте про безопасность данных, передаваемых в SQL запросах. В PDO есть аналог функции mysql_real_escape_string() это метод quote .
$login = $db->quote($_POST["login"]); $sql = "SELECT * FROM users WHERE login = $login"; $result = $db->query($sql);
Теперь нам нужно преобразовать результат из переменной $res в массив. Это делается при помощи метода FETCH , которому передаётся константа.
$res = $db->query($sql); $result = $res->FETCH(PDO::FETCH_NUM); // нумерованный $result = $res->FETCH(PDO::FETCH_ASSOC); // ассоциативный $result = $res->FETCH(PDO::FETCH_BOTH); // ассоциативный и нумерованный вместе $result = $res->FETCH(PDO::FETCH_OBJ); // объектный тип $result = $res->FETCH(PDO::FETCH_LAZY); // сразу все типы
Очевидно, что константа FETCH_LAZY замедляет работу скрипта, поэтому её желательно не использовать.
Метод FETCH возвращает одну запись из результата. Если требуется получить все записи, нужно использовать метод FETCHALL . В дальнейшем результат, полученный в результате использования FETCHALL обрабаьываем в цикле foreach , как показано в примере:
$query = $db->query("SELECT * FROM users");
$result = $query->FETCHALL(PDO::FETCH_ASSOC);
foreach($result as $arry)
{
echo $arry["name"] . "
";
}
Особого объяснения требует константа FETCH_CLASS , она позволяет заполнить предварительно созданный класс данными из результата запроса к базе данных.
Давайте рассмотрим пример, с использованием константы FETCH_CLASS:
Class User
{
public $login;
public $pass;
public function showInfo()
{
echo "
" . $this->pass."
" . " : " . $this->login . "
";
}
}
$result = $stmt->FETCHALL(PDO::FETCH_CLASS, "User");
foreach($result as $user)
{
$user->showInfo();
}
Не забывайте важное правило - названия свойств в созданном классе обязаны быть такими же, как и названия полей в базе данных.
Подготовленные выражения нужно обязательно использовать если ваш SQL запрос содержит переменные.
Подготовленные выражения PDO - основная причина использовать PHP Data Objects, поскольку это единственный безопасный способ выполнения SQL запросов, в которых есть переменные, созданные пользователем.
Подготовленные выражения в PDO - это обычный SQL запрос, в котором переменная заменяется специальным маркером - плейсхолдером.
Сначала давайте рассмотрим именнованный плейсхолдер, его синтаксис например такой: :email .
Давайте рассмотрим пример запроса INSERT с использованием плейсхолдеров.
$stmt = $db->prepare("INSERT INTO messages (email, message) VALUES (:email, :message)");
В этом примере вместо переменных в запросе мы использовали два плейсхолдера (:email, :message)") .
$stmt = $db->prepare("INSERT INTO messages (email, message) VALUES (:email, :message)"); $stmt->bindParam(":email", $email); $stmt->bindParam(":message", message); $email = "Е-почта №1"; $message = "Какой-то текст сообщения"; $stmt->execute(); $email = "Е-почта №2"; $message = "Какой-то текст сообщения"; $stmt->execute();
Обратите внимание, что чтобы подготовить SQL запрос, мы пишем его в методе prepare() . Потом, чтобы указать к какому плейсхолдеру какую переменную привязать, используем метод bindParam() . Чтобы выполнить SQL запрос, мы вызываем метод execute() .
Итак, ещё раз последовательность работы с подготовленными выражениями по шагам:
Этот синтаксис можно записать
$stmt = prepare("SELECT name FROM users WHERE email = :email"); $stmt->execute(array("email" => $email));
Видно, что в метод execute() должен передаваться массив, в котором ключи должны совпадать с именами плейсхолдеров.
Кстати, у метода bindParam() есть синоним bindValue() .
Теперь рассмотрим работу с неименованными плейсхолдерами.
$stmt = prepare("SELECT name FROM users WHERE email = ?") $stmt->execute(array($email));
В этом синтаксисе вместо записи плейсхолдера:название указывается другая форма его записи - знак вопроса:? .
Тут значения массива $email будут поочерёдно присвоены плейсхолдерам:? , но в нашем примере плейсхолдер один.
Вот другой пример применения неименованных плейсхолдеров, с использованием метода bindParam() :
$stmt = $db->prepare("INSERT INTO articles (title, text) VALUES (?, ?)"); $stmt->bindParam(1, $email); $stmt->bindParam(2, $message); $email = "Е-почта №1"; $message = "Какой-то текст сообщения"; $stmt->execute(); $email = "Е-почта №2"; $message = "Какой-то текст сообщения"; $stmt->execute();
Если мы хотим что-нибудь записать в базу, тогда можно также использовать метод PDO::exec() .
$sql = "INSERT INTO (login, password) VALUES ($login, $password)"; $result = $db->exec($sql);
Если этот запрос будет выполнен, то в переменную $result попадёт количество затронутых в таблице строк.
PDO::exec() выполняет INSERT запросы, но не может получить данные из БД, этим занимается метод PDO::query() . PDO::exec() только запускает SQL запрос на выполнение и возвращает количество строк, задействованных в ходе его выполнения, он не возвращает результат выборки оператором SELECT .
PDO работает с базами данных. Если с каким-то типом базы данных не получается работать, идём в php.ini и ищем строчки начинающиеся с extension=php_pdo_{название базы данных}, и раскомментируем их.
Php для начинающих: Callback функции, урок 34! https://www.youtube.com/watch?v=2NwLHXUoXcw https://www.youtube.com/watch?v=GMzI6jR_bE4 https://www.youtube.com/watch?v=gFJsBQIqpto PHP урок 9 Рекурсия https://www.youtube.com/watch?v=gLAeJcKkd6c http://php.net/manual/ru/mysqli-result.fetch-array.php /* очищаем результаты выборки */ mysqli_free_result($result); /* закрываем подключение */ mysqli_close($link); http://myrusakov.ru/sql-osnovy.html Хорошая статья: https://ru.wikipedia.org/wiki/Join_(SQL) ООП PHP. Расширительные средства Глава "Расширительные средства" из книги Мэта Зандстра "PHP. Объекты, шаблоны и методики программирования". https://www.youtube.com/watch?v=6L2bxtTBCRo
http://phpfaq.ru/pdo#intro - тут хорошая статья. Есть про исключения важная информация.
Проще описано: http://myrusakov.ru/php-data-objects.html
Video: https://www.youtube.com/watch?v=ACUiBH5qV0U&list=PLr_acfJGVcirEijJXmKxj8QGkWkKb-Tj-&nohtml5=False
У PDO свой собственный хитровыдуманный способ соединения, называемый . Плюс во время коннекта можно задать хренову тучу опций, некоторые из которых чрезвычайно полезны. Полный список можно найти , но важными из них являются только несколько.
Пример правильного соединения:
$host
=
"127.0.0.1"
;
$db
=
"test"
;
$user
=
"root"
;
$pass
=
""
;
$charset
=
"utf8"
;
$dsn
=
"mysql:host=
$host
;dbname=
$db
;charset=
$charset
"
;
$opt
= [
PDO
::
ATTR_ERRMODE
=>
PDO
::
ERRMODE_EXCEPTION
,
PDO
::
ATTR_DEFAULT_FETCH_MODE
=>
PDO
::
FETCH_ASSOC
,
PDO
::
ATTR_EMULATE_PREPARES
=>
false
,
];
$pdo
= new
PDO
($dsn
,
$user
,
$pass
,
$opt
);
Что здесь происходит?
В $dsn задается тип БД, с которым будем работать (mysql), хост, имя базы данных и чарсет.
- затем идут имя пользователя и пароль
- после которого задается массив опций, про который ни в одном из руководств не пишут.
При том что этот массив - чрезвычайно полезная, как уже говорилось выше, штука. Самое главное - режим выдачи ошибок надо задавать только в виде исключений.
- Во-первых, потому что во всех остальных режимах PDO не сообщает об ошибке ничего внятного,
- во-вторых, потому что исключение всегда содержит в себе незаменимый stack trace,
- в-третьих - исключения чрезвычайно удобно обрабатывать.
Плюс очень удобно задать FETCH_MODE по умолчанию, чтобы не писать его в КАЖДОМ запросе, как это очень любят делать прилежные хомячки.
Также здесь можно задавать режим pconnect-а, эмуляции подготовленных выражений и много других страшных слов.
В результате мы получаем переменную $pdo, с которой и работаем далее на протяжении всего скрипта.
Для выполнения запросов можно пользоваться двумя методами.
Если в запрос не передаются никакие переменные, то можно воспользоваться функцией query(). Она выполнит запрос и вернёт специальный объект - PDO statement. Очень грубо можно его сравнить с mysql resource, который возвращала mysql_query(). Получить данные из этого объекта можно как традиционным образом, через while, так и через foreach(). Также можно попросить вернуть полученные данные в особом формате, о чем ниже.
$stmt
=
$pdo
->
query
("SELECT name FROM users"
);
while ($row
=
$stmt
->
fetch
())
{
}
Если же в запрос передаётся хотя бы одна переменная, то этот запрос в обязательном порядке должен выполняться только через подготовленные выражения
. Что это такое? Это обычный SQL запрос, в котором вместо переменной ставится специальный маркер - плейсхолдер. PDO поддерживает позиционные плейсхолдеры (?), для которых важен порядок передаваемых переменных, и именованные (:name), для которых порядок не важен. Примеры:
$sql
=
;
$sql
=
;
Чтобы выполнить такой запрос, сначала его надо подготовить с помощью функции prepare(). Она также возвращает PDO statement, но ещё без данных. Чтобы их получить, надо исполнить этот запрос, предварительно передав в него переменные. Передать можно двумя способами:
Чаще всего можно просто выполнить метод execute(), передав ему массив с переменными:
$stmt
=
$pdo
->
prepare
("SELECT name FROM users WHERE email = ?"
);
$stmt
->
execute
(array($email
));
$stmt
=
$pdo
->
prepare
("SELECT name FROM users WHERE email = :email"
);
$stmt
->
execute
(array("email"
=>
$email
));
Как видно, в случае именованных плейсхолдеров в execute() должен передаваться массив, в котором ключи должны совпадать с именами плейсхолдеров.
Иногда, очень редко, может потребоваться второй способ, когда переменные сначала привязывают к запросу по одной, с помощью bindValue() / bindParam(), а потом только исполняют. В этом случае в execute() ничего не передается. Пример можно посмотреть в мануале
Используя этот метод, всегда следует предпочесть bindValue()? поскольку поведение bindParam() не очевидно для новичков и будет приводить к проблемам.
После этого можно использовать PDO statement теми же способами, что и выше. Например, через foreach:
$stmt
=
$pdo
->
prepare
("SELECT name FROM users WHERE email = ?"
);
$stmt
->
foreach ($stmt
as
$row
)
{
echo
$row
[
"name"
] .
"\n"
;
}
ВАЖНО: Подготовленные выражения - основная причина использовать PDO, поскольку это единственный безопасный способ выполнения SQL запросов, в которых участвуют переменные.
Также prepare() / execute() могут использоваться для многократного выполнения единожды подготовленного запроса с разными наборами данных. На практике это бывает нужно чрезвычайно редко, и особого прироста в скорости не приносит. Но на случай, если понадобится делать много однотипных запросов, то можно писать так:
$data = array(
1 => 1000,
5 => 300,
9 => 200,
);
$stmt
=
$pdo
->
prepare
("UPDATE users SET bonus = bonus + ? WHERE id = ?"
);
foreach ($data
as
$id
=>
$bonus
)
{
$stmt
->
execute
([
$bonus
,
$id
]);
}
Здесь мы один раз подготавливаем запрос, а затем много раз выполняем.
Мы уже выше познакомились с методом fetch(), который служит для последовательного получения строк из БД. Этот метод является аналогом функции mysq_fetch_array() и ей подобных, но действует по-другому: вместо множества функций здесь используется одна, но ее поведение задается переданным параметром. В подробностях об этих параметрах будет написано позже, а в качестве краткой рекомендации посоветую применять fetch() в режиме FETCH_LAZY
:
$stmt
=
$pdo
->
prepare
("SELECT name FROM users WHERE email = ?"
);
$stmt
->
execute
([
$_GET
[
"email"
]]);
while ($row
=
$stmt
->
fetch
(PDO
::
FETCH_LAZY
))
{
echo
$row
[
0
] .
"\n"
;
echo
$row
[
"name"
] .
"\n"
;
echo
$row
->
name
.
"\n"
;
}
В этом режиме не тратится лишняя память, и к тому же к колонкам можно обращаться любым из трех способов - через индекс, имя, или свойство.
Также у PDO statement есть функция-хелпер для получения значения единственной колонки. Очень удобно, если мы запрашиваем только одно поле - в этом случае значительно сокращается количество писанины:
$stmt
=
$pdo
->
prepare
("SELECT name FROM table WHERE id=?"
);
$stmt
->
execute
(array($id
));
$name
=
$stmt
->
fetchColumn
();
Но самой интересной функцией, с самым большим функционалом, является fetchAll(). Именно она делает PDO высокоуровневой библиотекой для работы с БД, а не просто низкоуровневым драйвером.
FetchAll() возвращает массив, который состоит из всех строк, которые вернул запрос. Из чего можно сделать два вывода:
1. Эту функцию не стоит применять тогда, когда запрос возвращает много данных. В таком случае лучше использовать традиционный цикл с fetch()
2. Поскольку в современных РНР приложениях данные никогда не выводятся сразу по получении, а передаются для этого в шаблон, fetchAll() становится просто незаменимой, позволяя не писать циклы вручную, и тем самым сократить количество кода.
Получение простого массива.
Вызванная без параметров, эта функция возвращает обычный индексированный массив, в котором лежат строки из бд, в формате, который задан в FETCH_MODE по умолчанию. Константы PDO::FETCH_NUM, PDO::FETCH_ASSOC, PDO::FETCH_OBJ могут менять формат на лету.
Получение колонки.
Иногда бывает нужно получить простой одномерный массив, запросив единственное поле из кучи строк. Для этого используется режим PDO::FETCH_COLUMN
$data
=
$pdo
->
query
("SELECT name FROM users"
)->
fetchAll
(PDO
::
FETCH_COLUMN
);
array (
0
=>
"John"
,
1
=>
"Mike"
,
2
=>
"Mary"
,
3
=>
"Kathy"
,
)
Получение пар ключ-значение.
Также востребованный формат, когда желательно получить ту же колонку, но индексированную не числами, а одним из полей. За это отвечает константа PDO::FETCH_KEY_PAIR.
$data
=
$pdo
->
query
("SELECT id, name FROM users"
)->
fetchAll
(PDO
::
FETCH_KEY_PAIR
);
array (
104
=>
"John"
,
110
=>
"Mike"
,
120
=>
"Mary"
,
121
=>
"Kathy"
,
)
Получение всех строк, индексированных полем.
Также часто бывает нужно получить все строки из БД, но также индексированные не числами, а уникальным полем. Это делает константа PDO::FETCH_UNIQUE
$data
=
$pdo
->
query
("SELECT * FROM users"
)->
fetchAll
(PDO
::
FETCH_UNIQUE
);
array (
104
=> array (
"name"
=>
"John"
,
"car"
=>
"Toyota"
,
),
110
=> array (
"name"
=>
"Mike"
,
"car"
=>
"Ford"
,
),
120
=> array (
"name"
=>
"Mary"
,
"car"
=>
"Mazda"
,
),
121
=> array (
"name"
=>
"Kathy"
,
"car"
=>
"Mazda"
,
),
)
Следует помнить, что первой в колонкой надо обязательно выбирать уникальное поле.
Всего различных режимов получения данных в PDO больше полутора десятков. Плюс ещё их можно комбинировать! Но это уже тема для отдельной статьи.
Работая с подготовленными выражениями, следует понимать, что плейсхолдер может заменять только строку или число. Ни ключевое слово, ни идентификатор, ни часть строки или набор строк через плейсхолдер подставить нельзя. Поэтому для LIKE надо сначала подготовить строку поиска целиком, а потом ее подставлять в запрос:
$name
=
"%
$name
%"
;
$stm
=
$pdo
->
prepare
("SELECT * FROM table WHERE name LIKE ?"
);
$stm
->
execute
(array($name
));
$data
=
$stm
->
fetchAll
();
Ну, вы поняли. Тут тоже всё плохо. PDO не предоставляет вообще никаких средств для работы с идентификаторами, и их надо форматировать по-старинке, вручную (или посмотреть, все-таки, в сторону SafeMysql , в которой этот, как и многие другие вопросы, решены просто и элегантно).
Следует помнить, что правила форматирования идентификаторов отличаются для разных БД.
В mysql для ручного форматирования идентификатора необходимо выполнить два действия:
- заключить его в обратные одинарные кавычки (backticks, "`").
- проискейпить эти символы внутри идентификатора внутри путём удвоения.
$field
=
"`"
.
str_replace
("`"
,
"``"
,
$_GET
[
"field"
]).
"`"
;
$sql
=
$field
"
;
Однако, здесь есть один нюанс. Одного форматирования может быть недостаточно. приведенный выше код гарантирует нас от классической инъекции, но в некоторых случаях враг все равно может записать что-то нежелательное, если мы бездумно подставляем имена полей и таблиц прямиком в запрос. К примеру, есть в таблице users поле admin. Если входящие имена полей не фильтровать, то в это поле, при автоматическом формировании запроса из POST-а, любой дурак запишет любую гадость.
Поэтому имена таблиц и полей, приходящие от юзера, желательно проверять на допустимость, как в приведённом ниже примере
Любой код для вставки, который можно увидеть в многочисленных туториалах, навевает тоску и желание убиться апстену. Многокилометровые построения с повторением одних и тех же имен - в идексах $_POST-а, в именах переменных, в именах полей в запросе, в именах плейсхолдеров в запросе, в именах плейсходеров и именах переменных при привязке.
Глядя на этот код, хочется кого-нибудь убить, или, по крайней мере, сделать его немного короче.
Это можно сделать, если принять соглашение, по которому имена полей в форме будут соответствовать именам полей в таблице. Тогда эти имена можно будет перечислить только один раз (в целях защиты от подмены, о которой говорилось выше), и использовать небольшую функцию-хелпер для сборки запроса, которая, в силу особенностей mysql, годится как для INSERT, так и UPDATE запросов:
function
pdoSet
($allowed
, &
$values
,
$source
= array()) {
$set
=
""
;
$values
= array();
if (!
$source
)
$source
= &
$_POST
;
foreach ($allowed
as
$field
) {
if (isset($source
[
$field
])) {
$set
.=
"`"
.
str_replace
("`"
,
"``"
,
$field
).
"`"
.
"=:
$field
, "
;
$values
[
$field
] =
$source
[
$field
];
}
}
return
substr
($set
,
0
, -
2
);
}
Соответственно, для вставки код будет
$allowed
= array("name"
,
"surname"
,
"email"
);
// allowed fields
$sql
=
"INSERT INTO users SET "
.
pdoSet
($allowed
,
$values
);
$stm
=
$dbh
->
prepare
($sql
);
$stm
->
execute
($values
);
А для апдейта - такой:
$allowed
= array("name"
,
"surname"
,
"email"
,
"password"
);
// allowed fields
$_POST
[
"password"
] =
MD5
($_POST
[
"login"
].
$_POST
[
"password"
]);
$sql
=
"UPDATE users SET "
.
pdoSet
($allowed
,
$values
).
" WHERE id = :id"
;
$stm
=
$dbh
->
prepare
($sql
);
$values
[
"id"
] =
$_POST
[
"id"
];
$stm
->
execute
($values
);
Не слишком эффектно, но зато очень эффективно. Напомню, кстати, что если использовать Класс для безопасной и удобной работы с MySQL , то это всё делается в две строчки.
PDO и ключевые слова
Здесь кроме фильтрации ничего придумать невозможно. поэтому тупо прогонять все не прописанные в запросе напрямую операторы через белый список:
$dirs
= array("ASC"
,
"DESC"
);
$key
=
array_search
($_GET
[
"dir"
],
$dirs
));
$dir
=
$orders
[
$key
];
$sql
=
"SELECT * FROM `table` ORDER BY
$field
$dir
"
;
Термин PDO является сокращением понятия PHP Data Objects . Как можно судить по названию, эта технология позволяет работать с содержимым базы данных через объекты.
Чаще всего, в отношении новых технологий, встает вопрос их преимуществ перед старыми-добрыми и проверенными инструментами, а также, перевода на них текущих и старых проектов.
PHP развивается очень активно и стремится стать одним из лучших инструментов для быстрой разработки веб приложений как массового, так и корпоративного уровня.
Говоря о PHP , будем подразумевать современный объектно-ориентированный PHP , позволяющий писать универсальный код, удобный для тестирования и повторного использования.
Использование PDO позволяет вынести работу с базой данных на объектно-ориентированный уровень и улучшить переносимость кода. На самом деле, использование PDO не так сложно, как можно было бы подумать.
Представим, что мы уже продолжительное время разрабатываем приложение, с использованием MySQL . И вот, в один прекрасный момент, появляется необходимость заменить MySQL на PostgreSQL .
Как минимум, нам придется заменить все вызовы mysqli_connect() (mysql_connect()) на pg_connect() и, по аналогии, другие функции, используемые для запроса и обработки данных.
При использовании PDO , мы ограничимся изменением нескольких параметров в файлах конфигурации.
Использование связанных параметров предоставляет большую гибкость в составлении запросов и позволяет улучшить защиту от SQL инъекций.
Те, кто уже использует ORM (object-relational mapping — объектно-реляционное отображение данных), например, Doctrine , знают удобство представления данных из таблиц БД в виде объектов. PDO позволяет получать данные в виде объектов и без использования ORM .
Поддержка расширения mysql окончательно удалена из нового PHP 7 . Если вы планируете переносить проект на новую версию PHP , уже сейчас следует использовать в нем, как минимум, mysqli. Конечно же, лучше начинать использовать PDO , если вы еще не сделали этого.
Мне кажется, что этих причин достаточно для склонения весов в сторону использования PDO . Тем более, не нужно ничего дополнительно устанавливать.
Версии PHP 5.5 и выше, чаще всего, уже содержать расширение для работы с PDO . Для проверки достаточно выполнить в консоли простую команду:
php -i | grep "pdo"
Теперь откроем его в любом браузере и найдем нужные данные поиском по строке PDO .
Процесс работы с PDO не слишком отличается от традиционного. В общем случае, процесс использования PDO выглядит так:
Для подключения к базе данных нужно создать новый объект PDO и передать ему имя источника данных, так же известного как DSN .
В общем случае, DSN состоит из имени драйвера, отделенного двоеточием от строки подключения, специфичной для каждого драйвера PDO .
Для MySQL , подключение выполняется так:
$connection = new PDO("mysql:host=localhost;dbname=mydb;charset=utf8", "root", "root");
$connection = new PDO ("mysql:host=localhost;dbname=mydb;charset=utf8" , "root" , "root" ) ; |
В данном случае, DSN содержит имя драйвера mysql , указание хоста (возможен формат host=ИМЯ_ХОСТА:ПОРТ ), имя базы данных, кодировка, имя пользователя MySQL и его пароль.
В отличие от mysqli_query() , в PDO есть два типа запросов:
Первым делом, рассмотрим второй вариант.
Рассмотрим пример выполнения запроса на примере insert .
$connection->exec("INSERT INTO users VALUES (1, "somevalue"");
$connection -> exec () ; |
Конечно же, данный запрос возвращает количество затронутых строк и увидеть его можно следующим образом.
$affectedRows = $connection->exec("INSERT INTO users VALUES (1, "somevalue""); echo $affectedRows;
$affectedRows = $connection -> exec ("INSERT INTO users VALUES (1, "somevalue"" ) ; echo $affectedRows ; |
В случае использования mysqli_query () , код мог бы быть следующим.
$result = mysql_query("SELECT * FROM users"); while($row = mysql_fetch_assoc($result)) { echo $row["id"] . " " . $row["name"]; }
$result = mysql_query ("SELECT * FROM users" ) ; while ($row = mysql_fetch_assoc ($result ) ) { |
Для PDO , код будет проще и лаконичнее.
foreach($connection->query("SELECT * FROM users") as $row) { echo $row["id"] . " " . $row["name"]; }
foreach ($connection -> query ("SELECT * FROM users" ) as $row ) { echo $row [ "id" ] . " " . $row [ "name" ] ; |
Как и в mysqli , PDO позволяет получать данные в разных режимах. Для определения режима, класс PDO содержит соответствующие константы.
Примечание : это не полный список, все возможные константы и варианты их комбинации доступны в документации .
Пример получения ассоциативного массива:
$statement = $connection->query("SELECT * FROM users"); while($row = $statement->fetch(PDO::FETCH_ASSOC)) { echo $row["id"] . " " . $row["name"]; }
$statement = $connection -> while ($row = $statement -> fetch (PDO:: FETCH_ASSOC ) ) { echo $row [ "id" ] . " " . $row [ "name" ] ; |
Примечание : Рекомендуется всегда указывать режим выборки, так как режим PDO:: FETCH_BOTH потребует вдвое больше памяти — фактически, будут созданы два массива, ассоциативный и обычный.
Рассмотрим использование режима выборки PDO:: FETCH_CLASS . Создадим класс User :
class User { protected $id; protected $name; public function getId() { return $this->id; } public function setId($id) { $this->id = $id; } public function getName() { return $this->name; } public function setName($name) { $this->name = $name; } }
class User protected $id ; protected $name ; public function getId () return $this -> id ; public function setId ($id ) $this -> id = $id ; public function getName () return $this -> name ; public function setName ($name ) $this -> name = $name ; |
Теперь выберем данные и отобразим данные при помощи методов класса:
$statement = $connection->query("SELECT * FROM users"); while($row = $statement->fetch(PDO::FETCH_CLASS, "User")) { echo $row->getId() . " " . $row->getName(); }
$statement = $connection -> query ("SELECT * FROM users" ) ; while ($row = $statement -> fetch (PDO:: FETCH_CLASS , "User" ) ) { echo $row -> getId () . " " . $row -> getName () ; |
Для понимания сути и всех преимуществ связывания параметров нужно более подробно рассмотреть механизмы PDO . При вызове $statement -> query () в коде выше, PDO подготовит запрос, выполнит его и вернет результат.
При вызове $connection -> prepare () создается подготовленный запрос. Подготовленные запросы — это способность системы управления базами данных получить шаблон запроса, скомпилировать его и выполнить после получения значений переменных, использованных в шаблоне. Похожим образом работают шаблонизаторы Smarty и Twig .
При вызове $statement -> execute () передаются значения для подстановки в шаблон запроса и СУБД выполняет запрос. Это действие аналогично вызову функции шаблонизатора render () .
Пример использования подготовленных запросов в PHP PDO :
В коде выше подготовлен запрос выборки записи с полем id равным значению, которое будет подставлено вместо : id . На данном этапе СУБД выполнит анализ и компиляцию запроса, возможно с использованием кеширования (зависит от настроек).
Теперь нужно передать недостающий параметр и выполнить запрос:
$id = 5; $statement->execute([ ":id" => $id ]);
Возможно, после рассмотрения механизма работы подготовленных запросов и связанных параметров, преимущества их использования стали очевидными.
PDO предоставляет удобную возможность экранирования пользовательских данных, например, такой код больше не нужен:
Вместо этого, теперь целесообразно делать так:
Можно, даже, еще укоротить код, используя нумерованные параметры вместо именованных:
В тоже время, использование подготовленных запросов позволяет улучшить производительность при многократном использовании запроса по одному шаблону. Пример выборки пяти случайных пользователей из базы данных:
$numberOfUsers = $connection->query("SELECT COUNT(*) FROM users")->fetchColumn(); $users = ; $statement = $connection->prepare("SELECT * FROM users WHERE id = ? LIMIT 1"); for ($i = 1; $i <= 5; $i++) { $id = rand(1, $numberOfUsers); $users = $statement->execute([$id])->fetch(PDO::FETCH_OBJ); }
$numberOfUsers = $connection -> query ("SELECT COUNT(*) FROM users" ) -> fetchColumn () ; $users = ; for ($i = 1 ; $i <= 5 ; $i ++ ) { $id = rand (1 , $numberOfUsers ) ; $users = $statement -> execute ([ $id ] ) -> fetch (PDO:: FETCH_OBJ ) ; |
При вызове метода prepare () , СУБД проведет анализ и скомпилирует запрос, при необходимости использует кеширование. Позже, в цикле for , происходит только выборка данных с указанным параметром. Такой подход позволяет быстрее получить данные, уменьшив время работы приложения.
При получении общего количества пользователей в базе данных был использован метод fetchColumn () . Этот метод позволяет получить значение одного столбца и является полезным при получении скалярных значений, таких как количество, сумма, максимально или минимальное значения.
Часто, при начале работы с PDO , возникают трудности с оператором IN . Например, представим, что пользователь вводит несколько имен, разделенных запятыми. Пользовательский ввод хранится в переменной $names .
Настройка и использование PDO - расширения PHP Data Objects для работы с базами данных
Для начала создадим базу данных для этого руководства:
CREATE DATABASE solar_system; GRANT ALL PRIVILEGES ON solar_system.* TO "testuser"@"localhost" IDENTIFIED BY "testpassword";
Пользователю с логином testuser и паролем testpassword предоставили полные права доступа к базе solar_system .
Теперь создадим таблицу и заполним данными, астрономическая точность которых не подразумевается:
USE solar_system; CREATE TABLE planets (id TINYINT(1) UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), name VARCHAR(10) NOT NULL, color VARCHAR(10) NOT NULL); INSERT INTO planets(name, color) VALUES("earth", "blue"), ("mars", "red"), ("jupiter", "strange");
Теперь, когда создана база, определим DSN () - сведения для подключения к базе, представленные в виде строки. Синтаксис описания отличается в зависимости от используемой СУБД. В примере работаем с MySQL/MariaDB, поэтому указываем:
Строка DSN в этом случае выглядит следующим образом:
$dsn = "mysql:host=localhost;port=3306;dbname=solar_system;charset=utf8";
Первым указывается database prefix . В примере - mysql . Префикс отделяется от остальной части строки двоеточием, а каждый следующий параметр - точкой с запятой.
Теперь, когда строка DSN готова, создадим PDO-объект. Конструктор на входе принимает следующие параметры:
Дополнительные параметры можно также определить после создания объекта с помощью метода SetAttribute:
$pdo->SetAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
PDO::DEFAULT_FETCH_MODE - важный параметр, который определяет метод выборки по умолчанию. Указанный метод используется при получении результата выполнения запроса.
Режим по умолчанию. Результат выборки индексируется как номерами (начиная с 0), так и именами столбцов:
$stmt = $pdo->query("SELECT * FROM planets"); $results = $stmt->fetch(PDO::FETCH_BOTH);
После выполнения запроса с этим режимом к тестовой таблице планет получим следующий результат:
Array ( => 1 => 1 => earth => earth => blue => blue)
Результат сохраняется в ассоциативном массиве, в котором ключ - имя столбца, а значение - соответствующее значение строки:
$stmt = $pdo->query("SELECT * FROM planets"); $results = $stmt->fetch(PDO::FETCH_ASSOC);
В результате получим:
Array ( => 1 => earth => blue)
При использовании этого режима результат представляется в виде массива, индексированного номерами столбцов (начиная с 0):
Array ( => 1 => earth => blue)
Этот вариант полезен, если нужно получить перечень значений одного поля в виде одномерного массива, нумерация которого начинается с 0. Например:
$stmt = $pdo->query("SELECT name FROM planets");
В результате получим:
Array ( => earth => mars => jupiter)
Используем этот вариант, если нужно получить перечень значений двух полей в виде ассоциативного массива. Ключи массива - это данные первого столбца выборки, значения массива - данные второго столбца. Например:
$stmt = $pdo->query("SELECT name, color FROM planets"); $result = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
В результате получим:
Array ( => blue => red => strange)
При использовании PDO::FETCH_OBJECT для каждой извлеченной строки создаётся анонимный объект. Его общедоступные (public) свойства - имена столбцов выборки, а результаты запроса используются в качестве их значений:
$stmt = $pdo->query("SELECT name, color FROM planets"); $results = $stmt->fetch(PDO::FETCH_OBJ);
В результате получим:
StdClass Object ( => earth => blue)
В этом случае, как и в предыдущем, значения столбцов становятся свойствами объекта. Однако требуется указать существующий класс, который будет использоваться для создания объекта. Рассмотрим это на примере. Для начала создадим класс:
Class Planet { private $name; private $color; public function setName($planet_name) { $this->name = $planet_name; } public function setColor($planet_color) { $this->color = $planet_color; } public function getName() { return $this->name; } public function getColor() { return $this->color; } }
Обратите внимание, что у класса Planet закрытые (private) свойства и нет конструктора. Теперь выполним запрос.
Если используется метод fetch с PDO::FETCH_CLASS , перед отправкой запроса на получение данных нужно применить метод setFetchMode:
$stmt = $pdo->query("SELECT name, color FROM planets"); $stmt->setFetchMode(PDO::FETCH_CLASS, "Planet");
Первый параметр, который передаем методу setFetchMode , - константа PDO::FETCH_CLASS . Второй параметр - имя класса, который будет использоваться при создании объекта. Теперь выполним:
$planet = $stmt->fetch(); var_dump($planet);
В результате получим объект Planet:
Planet Object ( => earth => blue)
Значения, полученные в результате запроса, назначены соответствующим свойствам объекта, даже закрытым.
В классе Planet нет явного конструктора, поэтому проблем при назначении свойств не будет. При наличии у класса конструктора, в котором свойство было назначено или изменено, они будут перезаписаны.
При использовании константы FETCH_PROPS_LATE значения свойств будут присваиваться после выполнения конструктора:
Class Planet { private $name; private $color; public function __construct($name = moon, $color = grey) { $this->name = $name; $this->color = $color; } public function setName($planet_name) { $this->name = $planet_name; } public function setColor($planet_color) { $this->color = $planet_color; } public function getName() { return $this->name; } public function getColor() { return $this->color; } }
Мы изменили класс Planet , добавив конструктор, который принимает на входе два аргумента: name (имя) и color (цвет). Значения этих полей по умолчанию: moon (луна) и gray (серый) соответственно.
Если не использовать FETCH_PROPS_LATE , при создании объекта свойства будут перезаписаны значениями по умолчанию. Проверим это. Сначала выполним запрос:
$stmt = $pdo->query("SELECT name, color FROM solar_system WHERE name = "earth""); $stmt->setFetchMode(PDO::FETCH_CLASS, "Planet"); $planet = $stmt->fetch(); var_dump($planet);
В результате получим:
Object(Planet)#2 (2) { ["name":"Planet":private]=> string(4) "moon" ["color":"Planet":private]=> string(4) "gray" }
Как и ожидалось, извлеченные из базы данных значения перезаписаны. Теперь рассмотрим решение задачи с помощью FETCH_PROPS_LATE (запрос аналогичный):
$stmt->setFetchMode(PDO::FETCH_CLASS|PDO::FETCH_PROPS_LATE, "Planet"); $planet = $stmt->fetch(); var_dump($planet);
В результате получим то, что нужно:
Object(Planet)#4 (2) { ["name":"Planet":private]=> string(5) "earth" ["color":"Planet":private]=> string(4) "blue" }
Если у конструктора класса нет значений по умолчанию, а они нужны, параметры конструктора задаются при вызове метода setFetchMode третьим аргументом в виде массива. Например:
Class Planet { private $name; private $color; public function __construct($name, $color) { $this->name = $name; $this->color = $color; } [...] }
Аргументы конструктора обязательны, поэтому выполним:
$stmt->setFetchMode(PDO::FETCH_CLASS|PDO::FETCH_PROPS_LATE, "Planet", ["moon", "gray"]);
Входящие параметры выступают также в роли значений по умолчанию, которые нужны для инициализации. В дальнейшем они будут перезаписаны значениями из базы данных.
Множественные результаты извлекаются в виде объектов с помощью метода fetch внутри цикла while:
While ($planet = $stmt->fetch()) { // обработка результатов }
Или путём выборки всех результатов сразу. Во втором случае используется метод fetchAll , причём режим указывается в момент вызова:
$stmt->fetchAll(PDO::FETCH_CLASS|PDO_FETCH_PROPS_LATE, "Planet", ["moon", "gray"]);
При выборе этого варианта выборки PDO не создаёт новый объект, а обновляет свойства существующего. Однако это возможно только для общедоступных (public) свойств или при использовании в объекте «магического» метода __set .
В PDO два способа выполнения запросов:
Существует два метода выполнения прямых запросов:
Прямые операторы используются только в том случае, если в запросе отсутствуют переменные и есть уверенность, что запрос безопасен и правильно экранирован.
PDO поддерживает подготовленные запросы (prepared statements), которые полезны для защиты приложения от : метод prepare выполняет необходимые экранирования.
Рассмотрим пример. Требуется вставить свойства объекта Planet в таблицу Planets . Сначала подготовим запрос:
$stmt = $pdo->prepare("INSERT INTO planets(name, color) VALUES(?, ?)");
Используем метод prepare , который принимает как аргумент SQL-запрос с псевдопеременными (placeholders). Псевдопеременные могут быть двух типов: неименнованые и именованные.
Неименованные псевдопеременные (positional placeholders) отмечаются символом? . Запрос в результате получается компактным, но требуется предоставить значения для подстановки, размещенные в том же порядке. Они передаются в виде массива через метод execute:
$stmt->execute([$planet->name, $planet->color]);
При использовании именованных псевдопеременных (named placeholders) порядок передачи значений для подстановки не важен, но код в этом случае становится не таким компактным. В метод execute данные передаются в виде ассоциативного массива, в котором каждый ключ соответствует имени псевдопеременной, а значение массива - значению, которое требуется подставить в запрос. Переделаем предыдущий пример:
$stmt = $pdo->prepare("INSERT INTO planets(name, color) VALUES(:name, :color)"); $stmt->execute(["name" => $planet->name, "color" => $planet->color]);
Методы prepare и execute используются как при выполнении запросов на изменение, так и при выборке.
А информацию о количестве обработанных строк при необходимости предоставит метод rowCount .
Параметр выбора режима ошибок PDO::ATTR_ERRMODE используется для определения поведения PDO в случае ошибок. Доступно три варианта: PDO::ERRMODE_SILENT , PDO::ERRMODE_EXCEPTION и PDO::ERRMODE_WARNING .
Вариант по умолчанию. PDO просто запишет информацию об ошибке, которую помогут получить методы errorCode и errorInfo .
Это предпочтительный вариант, при котором в дополнение к информации об ошибке PDO выбрасывает исключение (PDOException). Исключение прерывает выполнение скрипта, что полезно при использовании транзакций PDO. Пример приведён при описании транзакций.
В этом случае PDO также записывает информацию об ошибке. Поток выполнения скрипта не прерывается, но выдаются предупреждения.
Для подстановки значений в запросе можно также использовать методы bindValue и bindParam . Первый связывает значение переменной с псевдопеременной, которая использована при подготовке запроса:
$stmt = $pdo->prepare("INSERT INTO planets(name, color) VALUES(:name, :color)"); $stmt->bindValue("name", $planet->name, PDO::PARAM_STR);
Связали значение переменной $planet->name с псевдопеременной:name . Обратите внимание, что при использовании методов bindValue и bindParam как третий аргумент указывается тип переменной, используя соответствующие константы PDO. В примере - PDO::PARAM_STR .
Метод bindParam привязывает переменную к псевдопеременной. В этом случае переменная связана с псевдопеременной ссылкой, а значение будет подставлено в запрос только после вызова метода execute . Рассмотрим на примере:
$stmt->bindParam("name", $planet->name, PDO::PARAM_STR);
Представим необычный пример. Пользователю требуется выбрать список планет, причём каждый раз при выполнении запроса текущие данные удаляются из базы, а потом вставляются новые. Если после удаления произойдёт ошибка, то следующий пользователь получит пустой список. Чтобы этого избежать, используем транзакции:
$pdo->beginTransaction(); try { $stmt1 = $pdo->exec("DELETE FROM planets"); $stmt2 = $pdo->prepare("INSERT INTO planets(name, color) VALUES (?, ?)"); foreach ($planets as $planet) { $stmt2->execute([$planet->getName(), $planet->getColor()]); } $pdo->commit(); } catch (PDOException $e) { $pdo->rollBack(); }
Метод beginTransaction отключает автоматическое выполнение запросов, а внутри конструкции try-catch запросы выполняются в нужном порядке. Если не возникнет исключений PDOException , запросы выполнятся с помощью метода commit . В противном случае откатятся с помощью метода rollback , а автоматическое выполнение запросов восстановится.
Таким образом появилась согласованность выполнения запросов. Очевидно, что для этого параметру PDO::ATTR_ERRMODE необходимо установить значение PDO::ERRMODE_EXCEPTION .
Теперь, когда работа с PDO описана, отметим его основные преимущества:
Множество PHP-разработчиков привыкли использовать для работы с базами данных расширения mysql и mysqli. Но с версии 5.1 в PHP существует более удобный способ - PHP Data Objects . Этот класс, сокращенно именуемый PDO, предоставляет методы для работы с объектами и prepared statements , которые заметно повысят вашу продуктивность!
«PDO – PHP Data Objects – это прослойка, которая предлагает универсальный способ работы с несколькими базами данных.»
$DBH расшифровывается как «database handle» и будет использоваться на протяжении всей статьи.
Закрыть любое подключение можно путем переопределения его переменной в null.
# закрывает подключение
$DBH = null;
Больше информации по теме отличительных опций разных СУБД и методах подключения к ним можно найти на php.net .
Использование prepared statements укрепляет защиту от SQL-инъекций.
Ниже находятся три примера prepared statements.
# без placeholders - дверь SQL-инъекциям открыта!
$STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values ($name, $addr, $city)");
# безымянные placeholders
$STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values (?, ?, ?)");
# именные placeholders
$STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values (:name, :addr, :city)");
Первый пример здесь лишь для сравнения, его стоит избегать. Разница между безымянными и именными placeholder’ами в том, как вы будете передавать данные в prepared statements.
Если в вашем SQL-выражении много параметров, то назначать каждому по переменной весьма неудобно. В таких случаях можно хранить данные в массиве и передавать его:
# набор данных, которые мы будем вставлять
$data = array("Cathy", "9 Dark and Twisty Road", "Cardiff");
$STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values (?, ?, ?)");
$STH->execute($data);
$data вставится на место первого placeholder’а, $data - на место второго, и т.д. Но будьте внимательны: если ваши индексы сбиты, это работать не будет.
Если ваши данные нуждаются в обязательной обработке сразу после их получения из базы данных, ее можно реализовать в конструкторе класса.
Для примера возьмем ситуацию, когда вам нужно скрыть часть адреса проживания человека.
class secret_person {
public $name;
public $addr;
public $city;
public $other_data;
function __construct($other = "") {
$this->addr = preg_replace("//", "x", $this->addr);
$this->other_data = $other;
}
}
При создании объекта все латинские буквы в нижнем регистре должны замениться на x. Проверим:
$STH = $DBH->query("SELECT name, addr, city from folks");
$STH->setFetchMode(PDO::FETCH_CLASS, "secret_person");
while($obj = $STH->fetch()) {
echo $obj->addr;
}
Если в базе данных адрес выглядит как ’5 Rosebud’, то на выходе получится ’5 Rxxxxxx’.
Конечно, иногда будет требоваться, чтобы конструктор вызывался ПЕРЕД присваиванием значений. PDO такое тоже позволяет.
$STH->setFetchMode(PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE, "secret_person");
Теперь, когда вы дополнили предыдущий пример дополнительной опцией (PDO::FETCH_PROPS_LATE), адрес видоизменяться не будет, так как после записи значений ничего не происходит.
Наконец, при необходимости можно передавать конструктору аргументы прямо при создании объекта:
$STH->setFetchMode(PDO::FETCH_CLASS, "secret_person", array("stuff"));
Можно даже передавать разные аргументы каждому объекту:
$i = 0;
while($rowObj = $STH->fetch(PDO::FETCH_CLASS, "secret_person", array($i))) {
// что-то делаем
$i++;
}