Почему стоит пользоваться PDO для работы с базой данных. PHP PDO — работаем с базами данных правильно Php запрос delete pdo примеры
На днях решил разобраться с php PDO. Это интересно и необходимо изучать новые технологии. Русской документации нигде не нашел, потому и решил выложить. Надеюсь эта статья будет Вам интересна.
Введение
Начнем сначала PHP Data Objects(PDO) – легкий интерфейс для доступа к базам данных в языке PHP. Он может работать с большинством баз данных, такими как MS SQL ,Firebird, MySQL , Oracle, PostgreSQL , SQLite и другими. Но тут необходимо обратить внимание, что PDO предоставляет необходимый функционал для работы с базами данных, но для каждого типа базы данных должен быть установлен свой драйвер доступа для базы данных в виде расширения PHP.
С помощью PDO можно создавать приложения полностью независимые от типа базы данных, при этом есть возможность использовать большую часть функционала баз данных, например создание подготовленных выражений или транзакции. В том случае, если данный функционал не поддерживается базой данных, PDO эмулирует эту функцию своими средствами, тем самым никак не нарушая логику программы.
Подключение довольно простое, за тем исключением, что теперь одной строкой необходимо сразу указать, к какому типу базы данных вы подключаетесь, имя хоста, а также имя базы данных.
Формат вот такой:
тип_базы_данных:host=имя_хоста;db=name
Давайте рассмотрим на примере, только немного усложним пример, использованием исключений из библиотеки PDO (PDOException). Чтобы в случае неудачного подключения к базе данных, мы получили вразумительное сообщение об этом, а не кучу косяков коде, неизвестно откуда взявшихся.
try { $db = new PDO("mysql:host=localhost;dbname=test" , "root" , "" ) ; $rows = $db -> exec ("CREATE TABLE `testing`(id INT PRIMARY KEY AUTO_INCREMENT, fname VARCHAR(20) NOT NULL DEFAULT "", email VARCHAR(50) NOT NULL DEFAULT "")" ) ; } catch(PDOException $e ) { die ("Error: " . $e -> getMessage () ) ; }
Если же в SQL выражении вы допустили ошибку, в PDO есть специальные функции:
errorCode() – возвращает номер ошибки, и
errorInfo() – возвращает массив, в котором, как номер ошибки, так и текст описания
Запросы непосредственно можно делать двумя функциями:
exec() и query()
Отличие их состоит в типе возвращаемого результата, exec возвращает количество затронутых в результате выполнения запроса строк, а вторая, возвращает результат запроса в объекте PDOStatement, о нем поговорим чуть ниже.
Теперь добавим эти функции в код и сделаем пример чуть более сложным:
// в начале конфиг
define
("DB_DRIVER"
,
"mysql"
)
;
define
("DB_HOST"
,
"localhost"
)
;
define
("DB_NAME"
,
"test"
)
;
define
("DB_USER"
,
"root"
)
;
define
("DB_PASS"
,
""
)
;
try
{
// соединяемся с базой данных
$connect_str
=
DB_DRIVER .
":host="
.
DB_HOST .
";dbname="
.
DB_NAME;
$db
=
new
PDO($connect_str
,
DB_USER,
DB_PASS)
;
// вставляем несколько строк в таблицу из прошлого примера
$rows
=
$db
->
exec
("INSERT INTO `testing` VALUES
(null, "Ivan", "[email protected]"),
(null, "Petr", "[email protected]"),
(null, "Vasiliy", "[email protected]")
"
)
;
$error_array
=
$db
->
errorInfo
()
;
if
($db
->
"
;
// если запрос был выполнен успешно,
// то выведем количество затронутых строк
if
($rows
)
echo
"Количество затронутых строк: "
.
$rows
.
"
"
;
// теперь выберем несколько строчек из базы
$result
=
$db
->
query
("SELECT * FROM `testing` LIMIT 2"
)
;
// в случае ошибки SQL выражения выведем сообщене об ошибке
$error_array
=
$db
->
errorInfo
()
;
if
($db
->
errorCode
()
!=
0000)
echo
"SQL ошибка: "
.
$error_array
[
2
]
.
"
"
;
// теперь получаем данные из класса PDOStatement
while
($row
=
$result
->
fetch
()
)
{
// в результате получаем ассоциативный массив
print_r
($row
)
;
}
}
catch(PDOException $e
)
{
die
("Error: "
.
$e
->
getMessage
()
)
;
}
Подготовленные выражения
Подготовленные выражения очень похожи на обычные SQL запросы, но имеют некоторые преимущества. Во-первых имеют большую скорость выполнения, а во-вторых являются более надежными с точки зрения безопасности, так как все параметры передаваемые в них, автоматически экранируются от всевозможных инъекций.
Они имеют весомое преимущество в скорости, при выполнении многократных одинаковых запросов, чем если каждый раз составлять запрос заново. Также экономится траффик между приложением и базой данных.
PDO предоставляет удобные функции для работы с подготовленными выражениями. В случае, если выбранный тип базы данных не поддерживает работу с подготовленными выражениями, PDO просто будет эмулировать их работу своими методами.
И так для начала создадим подготовленное выражение, это делается функцией Prepare()
В качестве параметра она принимает SQL запрос, но в нем, вместо значений, которые необходимо менять, ставятся псевдо переменные, которые могут быть в виде знака вопроса(?), либо имени псевдо переменой, которое начинается с двоеточия (:)
$sth1 = $db->prepare(“SELECT * FROM `testing` WHERE id=:id”);
$sth2 = $db->prepare(“SELECT * FROM `testing` WHERE id=?”);
В зависимости от того, как вы определите переменную, будет зависеть ваша дальнейшая работа.
Если вы определили переменные знаком вопроса, то потом, в функцию execute передайте массив значений, в той, последовательности, в которой стоят переменные.
Если же вы обозначили переменные именами, то надо будет назначить каждой переменной значение посредством функций:
bindValue() – присваивает псевдопеременной значение
bindParam() – связывает псевдопеременную с настоящей переменной, и при изменении настоящей переменной, не нужно больше вызывать никаких дополнительных функций, можно сразу execute()
Вот пример использования первого варианта:
Обратите внимание, что после создания объектом PDO подготовленного выражения в классе PDOStatement, мы пользуемся только им, соответственно, в нем есть свои функции errorCode, errorInfo, а также результат выполнения запросов, также сразу же хранится в нем.
А теперь второй способ.
Для присваивания значения псевдо переменной, воспользуемся функцией bindValue()
Этот код можно записать еще проще, связав псевдопеременную с реальной:
Тут же необходимо добавить, что очень желательно (чтобы не возникало лишних ошибок) третьим параметром указывать тип переменной. У меня лично, в случае отсутствия типа переменной, возникали ошибки в операторе WHERE, так как он считал переменную текстом, а не числом.
$sth3->bindParam(‘:id’,$id, PDO::PARAM_INT);
$sth3->bindParam(‘:id’,$id, PDO::PARAM_STR);
Еще одним из очень приятных плюсов использования таких подготовленных выражений, это экранирование переменных. Перед подстановкой в процедуру все переменные экранируются и никакие SQL инъекции не страшны.
Транзакции
Транзакция – это совокупность запросов базу данных, которые должны быть обязательно выполнены все. Если какой-либо запрос не выполнен или выполнен с ошибкой, то транзакция отменяется и изменений данных в базе не происходит.
Это нужно, чтобы гарантировать сохранение целостности данных при нескольких запросах. например при переводе денежных средств со счета на счет.
Чтобы выполнить транзакцию в PDO необходимо перейти в режим ручного подтверждения запросов.
Кстати говоря, транзакции используются постоянно, но обычно PDO работает в режиме автоподтверждения, потому все транзакции состоят из одного запроса.
Чтобы выключить режим автоподтверждения, выполняем команду:
$db->beginTransaction();
После этого выполняем столько запросов к базе данных сколько необходимо сделать в этой транзакции.
И только после того как все запросы будут выполнены, Вы можете подтвердить транзакцию функцией
$db->commit();
или отменить транзакцию
$db->rollback();
Вот небольшой пример транзакций:
try { $connect_str = DB_DRIVER . ":host=" . DB_HOST . ";dbname=" . DB_NAME; $db = new PDO($connect_str , DB_USER, DB_PASS) ; $rows = $db -> exec ("CREATE TABLE `testing`(id INT PRIMARY KEY AUTO_INCREMENT, fname VARCHAR(20) NOT NULL DEFAULT "", email VARCHAR(50) NOT NULL DEFAULT "", money INT NOT NULL DEFAULT 0) ENGINE=InnoDB;" ) ; $rows = $db -> exec ("INSERT INTO `testing` VALUES (null, "Ivan", "[email protected]", 15000), (null, "Petr", "[email protected]", 411000), (null, "Vasiliy", "[email protected]", 1500000) " ) ; // Попробуем от Ивана перевести сумму 50000 // Петру $summ = 50000 ; $transaction = true ; $db -> beginTransaction () ; $sth1 = $db -> query ("SELECT money FROM testing WHERE fname="Ivan"" ) ; $sth2 = $db -> query ("SELECT money FROM testing WHERE fname="Petr"" ) ; $row1 = $sth1 -> fetch () ; $row2 = $sth2 -> fetch () ; if (! $row1 || ! $row2 ) $transaction = false ; $total2 = $summ + $row2 [ "money" ] ; $total1 = $row1 [ "money" ] - $summ ; if ($total1 < 0 || $total2 < 0) $transaction = false ; $num_rows1 = $db -> exec ( . $total1 . "" WHERE fname="Ivan"" ) ; $num_rows2 = $db -> exec ("UPDATE `testing` SET money="" . $total2 . "" WHERE fname="Petr"" ) ; if ($transaction ) { echo "Транзакция успешно прошла" ; $db -> commit () ; } else { echo "Транзакция не прошла" ; $db -> rollback () ; } } catch(PDOException $e ) { die ("Error: " . $e -> getMessage () ) ; }
Тут еще следует заметить, что не все типы таблиц поддерживают транзакции, потому в этом примере я использовал таблицу InnoDb вместо стандартной MyISAM.
В заключении хотелось бы сказать, что это еще далеко не полный мануал по PDO. Всю самую свежую и полную информацию Вы всегда можете раздобыть вот здесь: http://www.php.net/manual/en/book.pdo.php
Изучайте и создавайте.
Сегодня я начинаю цикл статей, посвящённых PDO , в котором мы разберём, что такое PDO , зачем он нам нужен и как его использовать.
Наверняка, многие уже слышали аббревиатуру PDO , но мало кто знает, что же это. Вот давайте сегодня об этом и поговорим.
Что такое PDO?PDO(PHP Data Objects) - это просто интерфейс , позволяющий нам абстрагироваться от конкретной базы данных. Лучше всего показать на примере.
Mysql_connect($host, $user, $pass); // MySQL
mysql_select_db($db);
Sqlite_open($db); // sqlite
Pg_connect("host=$host, dbname=$db, user=$user, password=$pass"); // PostgreSQL
В коде выше представлены способы для подключения к трём разным базам данных: MySQL , sqlite и PostgreSQL . Как видите, функции у каждой БД отличаются.
То же самое с другими действиями. Например, выборка данных из БД.
$sql = "INSERT INTO(name, pass) VALUES($name, $pass)";
Mysql_query($sql); // MySQL
sqlite_query($sql); // sqlite
pg_query($sql); // PostgreSQL
Представим, что у нас есть огромная база данных PostgreSQL , и мы решили сменить её на MySQL . Нам придётся переписывать очень много кода, и, скорее всего, без ошибок не обойдётся. Чтобы решить эту проблему и существует PDO , позволяющий нам не зависеть от конкретной базы.
Давайте рассмотрим, как мы теперь можем подключиться.
$db = new PDO("mysql:host=$host;dbname=$db", $user, $pass); // MySQL
$db = new PDO("sqlite:host=$host;dbname=$db", $user, $pass); // sqlite
$db = new PDO("pgsql:host=$host;dbname=$db", $user, $pass); // PostgreSQL
Как видно из кода выше, в этих трёх подключениях меняется только строчка с названием БД, а остальное всё то же самое.
Чтобы что-нибудь выбрать, мы можем написать так:
$db->exec($sql);
Всё! Запрос выполнится независимо от того, какая у нас база данных.
ПоддержкаPDO доступен с PHP 5.1 . Чтобы мы могли "забыть", какую базу данных мы используем, за нас всё делают их драйверы . Чтобы их активировать, зайдите в файл php.ini и найдите там строчки, которые начинаются на extension=php_pdo_ , а затем идёт название базы данных, и раскоментируйте их.
На этом всё для вступительной статьи, а в следующей мы уже начнём разбираться, как использовать PDO .
У 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. Не имеет значения, какой драйвер Вы хотите использовать; Вам всегда нужно будет использовать класс PDO. Его конструктор принимает параметры для того, чтобы определить источник базы данных (известный как DSN), и необязательные параметры для имени пользователя и пароля.
Соединение с MySQL: $dbh = new PDO("mysql:host=localhost;dbname=test", $user, $pass);Если произойдут какие либо ошибки соединения, то будет выброшено исключение: объект класса PDOException. Вы можете поймать его, если захотите обработать эту ситуацию, или можете оставить его для глобального обработчика исключений, который устанавливается через set_exception_handler ().
Обработка ошибок соединения: try { $dbh = new PDO("mysql:host=localhost;dbname=test", $user, $pass); foreach($dbh->query(‘SELECT * from FOO’) as $row) { print_r($row); } $dbh = null; } catch (PDOException $e) { die("Ошибка! Всё. Приехали... ".$e->getMessage()); }Внимание: Если Вы не поймаете исключение, брошенное конструктором PDO, действие по умолчанию, предпринятое движком zend, должно остановить скрипт и показать трассировку. Эта хрень выдаст все Ваши интимные подробности общения с базой данной. То есть покажет подробные детали соединения с базой данных, включая имя пользователя и пароль! На Вашей совести поймать это исключение, либо явно (через утверждение try catch ), либо неявно через set_exception_handler ().
После успешного соединения с базой данных оно остается активным на протяжении всей жизни экземпляра объекта PDO. Чтобы закрыть соединение, Вы должны разрушить объект, гарантируя, что все остающиеся на него ссылки удалены – сделать это можно, присвоив значение NULL переменной, которая содержит объект. Если Вы не сделаете этого явно, то PHP автоматически закроет соединение, когда скрипт завершит работу.
Закрытие соединения: $dbh = new PDO("mysql:host=localhost;dbname=test", $user, $pass); // Здесь что то делаем: ... // А теперь, внимание: конец связи! $dbh = null;Многие веб-приложения выигрывают от создания постоянных соединений с серверами баз данных. Постоянные соединения не закрываются, когда скрипт завершает работу, а кэшируются и снова используются, когда другой скрипт просит соединения, используя те же учётные данные для соединения. Кэш постоянных соединений позволяет избежать накладных расходов на установление нового соединения каждый раз, когда скрипт должен общаться с базой данных, в результате чего веб-приложения работают быстрее.
Установка постоянного соединения: $dbh = new PDO("mysql:host=localhost;dbname=test", $user, $pass, array(PDO::ATTR_PERSISTENT => true));Имейте ввиду: Если Вы хотите использовать постоянное соединение, Вы должны установить PDO::ATTR_PERSISTENT в массиве опций драйвера, который передаётся конструктору класса PDO. Устанавливая этот атрибут через PDO::setAttribute() после создания экземпляра объекта, драйвер не будет использовать постоянные связи. А так же, в этом случае вам не удастся расширить класс PDOStatement для каких то своих нужд, т.е. нельзя будет установить: PDO::ATTR_STATEMENT_CLASS
- Перевод
Множество PHP-разработчиков привыкли использовать для работы с базами данных расширения mysql и mysqli. Но с версии 5.1 в PHP существует более удобный способ - PHP Data Objects . Этот класс, сокращенно именуемый PDO, предоставляет методы для работы с объектами и prepared statements , которые заметно повысят вашу продуктивность!
Введение в PDO«PDO – PHP Data Objects – это прослойка, которая предлагает универсальный способ работы с несколькими базами данных.»Заботу об особенностях синтаксиса различных СУБД она оставляет разработчику, но делает процесс переключения между платформами гораздо менее болезненным. Нередко для этого требуется лишь изменить строку подключения к базе данных.
Эта статья написана для людей, которые пользуются mysql и mysqli, чтобы помочь им в переходе на более мощный и гибкий PDO.Поддержка СУБД Это расширение может поддерживать любую систему управления базами данных, для которой существует PDO-драйвер. На момент написания статьи доступны следующие драйвера:
- PDO_CUBRID (CUBRID)
- PDO_DBLIB (FreeTDS / Microsoft SQL Server / Sybase)
- PDO_FIREBIRD (Firebird/Interbase 6)
- PDO_IBM (IBM DB2)
- PDO_INFORMIX (IBM Informix Dynamic Server)
- PDO_MYSQL (MySQL 3.x/4.x/5.x)
- PDO_OCI (Oracle Call Interface)
- PDO_ODBC (ODBC v3 (IBM DB2, unixODBC and win32 ODBC))
- PDO_PGSQL (PostgreSQL)
- PDO_SQLITE (SQLite 3 and SQLite 2)
- PDO_SQLSRV (Microsoft SQL Server)
- PDO_4D (4D)
print_r(PDO::getAvailableDrivers()); Подключение Способы подключения к разным СУБД могут незначительно отличаться. Ниже приведены примеры подключения к наиболее популярным из них. Можно заметить, что первые три имеют идентичный синтаксис, в отличие от SQLite.
try { # MS SQL Server и Sybase через PDO_DBLIB $DBH = new PDO("mssql:host=$host;dbname=$dbname", $user, $pass); $DBH = new PDO("sybase:host=$host;dbname=$dbname", $user, $pass); # MySQL через PDO_MYSQL $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass); # SQLite $DBH = new PDO("sqlite:my/database/path/database.db"); } catch(PDOException $e) { echo $e->getMessage(); }
Пожалуйста, обратите внимание на блок try/catch – всегда стоит оборачивать в него все свои PDO-операции и использовать механизм исключений (об этом чуть дальше).
$DBH расшифровывается как «database handle» и будет использоваться на протяжении всей статьи.
Закрыть любое подключение можно путем переопределения его переменной в null.
# закрывает подключение
$DBH = null;
Больше информации по теме отличительных опций разных СУБД и методах подключения к ним можно найти на php.net .
$DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT); $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING); $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
Но стоит заметить, что ошибка при попытке соединения будет всегда вызывать исключение.PDO::ERRMODE_SILENT Это режим по умолчанию. Примерно то же самое вы, скорее всего, используете для отлавливания ошибок в расширениях mysql и mysqli. Следующие два режима больше подходят для DRY программирования.PDO::ERRMODE_WARNING Этот режим вызовет стандартный Warning и позволит скрипту продолжить выполнение. Удобен при отладке.PDO::ERRMODE_EXCEPTION В большинстве ситуаций этот тип контроля выполнения скрипта предпочтителен. Он выбрасывает исключение, что позволяет вам ловко обрабатывать ошибки и скрывать щепетильную информацию. Как, например, тут:
# подключаемся к базе данных try { $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass); $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); # Черт! Набрал DELECT вместо SELECT! $DBH->prepare("DELECT name FROM people")->execute(); } catch(PDOException $e) { echo "Хьюстон, у нас проблемы."; file_put_contents("PDOErrors.txt", $e->getMessage(), FILE_APPEND); }
В SQL-выражении есть синтаксическая ошибка, которая вызовет исключение. Мы можем записать детали ошибки в лог-файл и человеческим языком намекнуть пользователю, что что-то случилось.Insert и Update Вставка новых и обновление существующих данных являются одними из наиболее частых операций с БД. В случае с PDO этот процесс обычно состоит из двух шагов. (В следующей секции все относится как к UPDATE, так и INSERT)
Тривиальный пример вставки новых данных:
# STH означает "Statement Handle" $STH = $DBH->prepare("INSERT INTO folks (first_name) values ("Cathy")"); $STH->execute();
Вообще-то можно сделать то же самое одним методом exec(), но двухшаговый способ дает все преимущества prepared statements. Они помогают в защите от SQL-инъекций, поэтому имеет смысл их использовать даже при однократном запросе.Prepared StatementsИспользование prepared statements укрепляет защиту от SQL-инъекций.
Prepared statement - это заранее скомпилированное SQL-выражение, которое может быть многократно выполнено путем отправки серверу лишь различных наборов данных. Дополнительным преимуществом является невозможность провести SQL-инъекцию через данные, используемые в placeholder’ах.
Ниже находятся три примера 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.
Здесь два шага. На первом мы назначаем всем placeholder’ам переменные (строки 2-4). Затем назначаем этим переменным значения и выполняем запрос. Чтобы послать новый набор данных, просто измените значения переменных и выполните запрос еще раз.
Если в вашем 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 - на место второго, и т.д. Но будьте внимательны: если ваши индексы сбиты, это работать не будет.
Здесь тоже можно передавать массив, но он должен быть ассоциативным. В роли ключей должны выступать, как можно догадаться, имена placeholder’ов.
# данные, которые мы вставляем $data = array("name" => "Cathy", "addr" => "9 Dark and Twisty", "city" => "Cardiff"); $STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values (:name, :addr, :city)"); $STH->execute($data);
Одним из удобств использования именных placeholder’ов является возможность вставки объектов напрямую в базу данных, если названия свойств совпадают с именами параметров. Вставку данных, к примеру, вы можете выполнить так:
# класс для простенького объекта class person { public $name; public $addr; public $city; function __construct($n,$a,$c) { $this->name = $n; $this->addr = $a; $this->city = $c; } # так далее... } $cathy = new person("Cathy","9 Dark and Twisty","Cardiff"); # а тут самое интересное $STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values (:name, :addr, :city)"); $STH->execute((array)$cathy);
Преобразование объекта в массив при execute() приводит к тому, что свойства считаются ключами массива.Выборка данных
Данные можно получить с помощью метода ->fetch(). Перед его вызовом желательно явно указать, в каком виде они вам требуются. Есть несколько вариантов:
- PDO::FETCH_ASSOC: возвращает массив с названиями столбцов в виде ключей
- PDO::FETCH_BOTH (по умолчанию): возвращает массив с индексами как в виде названий стобцов, так и их порядковых номеров
- PDO::FETCH_BOUND: присваивает значения столбцов соответствующим переменным, заданным с помощью метода ->bindColumn()
- PDO::FETCH_CLASS: присваивает значения столбцов соответствующим свойствам указанного класса. Если для какого-то столбца свойства нет, оно будет создано
- PDO::FETCH_INTO: обновляет существующий экземпляр указанного класса
- PDO::FETCH_LAZY: объединяет в себе PDO::FETCH_BOTH и PDO::FETCH_OBJ
- PDO::FETCH_NUM: возвращает массив с ключами в виде порядковых номеров столбцов
- PDO::FETCH_OBJ: возвращает анонимный объект со свойствами, соответствующими именам столбцов
$STH->setFetchMode(PDO::FETCH_ASSOC);
Также можно задать его напрямую при вызове метода ->fetch().FETCH_ASSOC При этом формате создается ассоциативный массив с названиями столбцов в виде индексов. Он должен быть знаком тем, кто использует расширения mysql/mysqli.
# поскольку это обычный запрос без placeholder’ов, # можно сразу использовать метод query() $STH = $DBH->query("SELECT name, addr, city from folks"); # устанавливаем режим выборки $STH->setFetchMode(PDO::FETCH_ASSOC); while($row = $STH->fetch()) { echo $row["name"] . "\n"; echo $row["addr"] . "\n"; echo $row["city"] . "\n"; }
Цикл while() переберет весь результат запроса.FETCH_OBJ Данный тип получения данных создает экземпляр класса std для каждой строки.
# создаем запрос $STH = $DBH->query("SELECT name, addr, city from folks"); # выбираем режим выборки $STH->setFetchMode(PDO::FETCH_OBJ); # выводим результат while($row = $STH->fetch()) { echo $row->name . "\n"; echo $row->addr . "\n"; echo $row->city . "\n"; } FETCH_CLASS При использовании fetch_class данные заносятся в экземпляры указанного класса. При этом значения назначаются свойствам объекта ДО вызова конструктора. Если свойства с именами, соответствующими названиям столбцов, не существуют, они будут созданы автоматически (с областью видимости public).
Если ваши данные нуждаются в обязательной обработке сразу после их получения из базы данных, ее можно реализовать в конструкторе класса.
Для примера возьмем ситуацию, когда вам нужно скрыть часть адреса проживания человека.
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++;
}
$DBH->lastInsertId();
Метод ->lastInsertId() возвращает id последней вставленной записи. Стоит заметить, что он всегда вызывается у объекта базы данных (в статье он именуется $DBH), а не объекта с выражением ($STH).
$DBH->exec("DELETE FROM folks WHERE 1"); $DBH->exec("SET time_zone = "-8:00"");
Метод ->exec() используется для операций, которые не возвращают никаких данных, кроме количества затронутых ими записей.
$safe = $DBH->quote($unsafe);
Метод ->quote() ставит кавычки в строковых данных таким образом, что их становится безопасно использовать в запросах. Пригодится, если вы не используете prepared statements.
$rows_affected = $STH->rowCount();
Метод ->rowCount() возвращает количество записей, которые поучаствовали в операции. К сожалению, эта функция отказывалась работать с SELECT-запросами вплоть до PHP 5.1.6. Если обновить версию PHP не представляется возможным, количество записей можно получить так:
$sql = "SELECT COUNT(*) FROM folks"; if ($STH = $DBH->query($sql)) { # проверяем количество записей if ($STH->fetchColumn() > 0) { # делаем здесь полноценную выборку, потому что данные найдены! } else { # выводим сообщение о том, что удовлетворяющих запросу данных не найдено } } Заключение Надеюсь, этот материал поможет кому-то из вас осуществить миграцию с расширений mysql и mysqli.
