Подготовленные запросы php. Подготовленные запросы и хранимые процедуры. Объектная ориентированность PDO

Термин PDO является сокращением понятия PHP Data Objects . Как можно судить по названию, эта технология позволяет работать с содержимым базы данных через объекты.

Почему не myqli или mysql?

Чаще всего, в отношении новых технологий, встает вопрос их преимуществ перед старыми-добрыми и проверенными инструментами, а также, перевода на них текущих и старых проектов.

Объектная ориентированность PDO

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

Говоря о PHP , будем подразумевать современный объектно-ориентированный PHP , позволяющий писать универсальный код, удобный для тестирования и повторного использования.

Использование PDO позволяет вынести работу с базой данных на объектно-ориентированный уровень и улучшить переносимость кода. На самом деле, использование PDO не так сложно, как можно было бы подумать.

Абстракция

Представим, что мы уже продолжительное время разрабатываем приложение, с использованием MySQL . И вот, в один прекрасный момент, появляется необходимость заменить MySQL на PostgreSQL .

Как минимум, нам придется заменить все вызовы mysqli_connect() (mysql_connect()) на pg_connect() и, по аналогии, другие функции, используемые для запроса и обработки данных.

При использовании PDO , мы ограничимся изменением нескольких параметров в файлах конфигурации.

Связывание параметров

Использование связанных параметров предоставляет большую гибкость в составлении запросов и позволяет улучшить защиту от SQL инъекций.

Получение данных в виде объектов

Те, кто уже использует ORM (object-relational mapping — объектно-реляционное отображение данных), например, Doctrine , знают удобство представления данных из таблиц БД в виде объектов. PDO позволяет получать данные в виде объектов и без использования ORM .

Расширение mysql больше не поддерживается

Поддержка расширения mysql окончательно удалена из нового PHP 7 . Если вы планируете переносить проект на новую версию PHP , уже сейчас следует использовать в нем, как минимум, mysqli. Конечно же, лучше начинать использовать PDO , если вы еще не сделали этого.

Мне кажется, что этих причин достаточно для склонения весов в сторону использования PDO . Тем более, не нужно ничего дополнительно устанавливать.

Проверяем наличие PDO в системе

Версии PHP 5.5 и выше, чаще всего, уже содержать расширение для работы с PDO . Для проверки достаточно выполнить в консоли простую команду:

php -i | grep "pdo"

Теперь откроем его в любом браузере и найдем нужные данные поиском по строке PDO .

Знакомимся с PDO

Процесс работы с PDO не слишком отличается от традиционного. В общем случае, процесс использования PDO выглядит так:

1. Подключение к базе данных;
2. По необходимости, подготовка запроса и связывание параметров;
3. Выполнение запроса.

Подключение к базе данных

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

В общем случае, DSN состоит из имени драйвера, отделенного двоеточием от строки подключения, специфичной для каждого драйвера PDO .

Для MySQL , подключение выполняется так:

$connection = new PDO("mysql:host=localhost;dbname=mydb;charset=utf8", "root", "root");

В данном случае, DSN содержит имя драйвера mysql , указание хоста (возможен формат host=ИМЯ_ХОСТА:ПОРТ ), имя базы данных, кодировка, имя пользователя MySQL и его пароль.

Запросы

В отличие от mysqli_query() , в PDO есть два типа запросов:

• Возвращающие результат (select, show );
• Не возвращающие результат (insert , detele и другие).

Первым делом, рассмотрим второй вариант.

Выполнение запросов

Рассмотрим пример выполнения запроса на примере insert .

$connection->exec("INSERT INTO users VALUES (1, "somevalue"");

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

$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"]; }

Для PDO , код будет проще и лаконичнее.

foreach($connection->query("SELECT * FROM users") as $row) { echo $row["id"] . " " . $row["name"]; }

Режимы получения данных

Как и в mysqli , PDO позволяет получать данные в разных режимах. Для определения режима, класс PDO содержит соответствующие константы.

• PDO:: FETCH_ASSOC — возвращает массив, индексированный по имени столбца в таблице базы данных;
• PDO:: FETCH_NUM — возвращает массив, индексированный по номеру столбца;
• PDO:: FETCH_OBJ — возвращает анонимный объект с именами свойств, соответствующими именам столбцов. Например, $row->id будет содержать значение из столбца id.
• PDO:: FETCH_CLASS — возвращает новый экземпляр класса, со значениями свойств, соответствующими данным из строки таблицы. В случае, если указан параметр PDO:: FETCH_CLASSTYPE (например PDO:: FETCH_CLASS | PDO:: FETCH_CLASSTYPE ), имя класса будет определено из значения первого столбца.

Примечание : это не полный список, все возможные константы и варианты их комбинации доступны в документации .

Пример получения ассоциативного массива:

$statement = $connection->query("SELECT * FROM users"); 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; } }

Теперь выберем данные и отобразим данные при помощи методов класса:

$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); }

При вызове метода prepare () , СУБД проведет анализ и скомпилирует запрос, при необходимости использует кеширование. Позже, в цикле for , происходит только выборка данных с указанным параметром. Такой подход позволяет быстрее получить данные, уменьшив время работы приложения.

При получении общего количества пользователей в базе данных был использован метод fetchColumn () . Этот метод позволяет получить значение одного столбца и является полезным при получении скалярных значений, таких как количество, сумма, максимально или минимальное значения.

Связанные значения и оператор IN

Часто, при начале работы с PDO , возникают трудности с оператором IN . Например, представим, что пользователь вводит несколько имен, разделенных запятыми. Пользовательский ввод хранится в переменной $names .

Большинство баз данных поддерживают концепцию подготовленных запросов. Что это такое? Это можно описать, как некий вид скомпилированного шаблона SQL запроса, который будет запускаться приложением и настраиваться с помощью входных параметров. У подготовленных запросов есть два главных преимущества:

• Запрос необходимо однажды подготовить и затем его можно запускать столько раз, сколько нужно, причем как с теми же, так и с отличающимися параметрами. Когда запрос подготовлен, СУБД анализирует его, компилирует и оптимизирует план его выполнения. В случае сложных запросов этот процесс может занимать ощутимое время и заметно замедлить работу приложения, если потребуется много раз выполнять запрос с разными параметрами. При использовании подготовленного запроса СУБД анализирует/компилирует/оптимизирует запрос любой сложности только один раз, а приложение запускает на выполнение уже подготовленный шаблон. Таким образом подготовленные запросы потребляют меньше ресурсов и работают быстрее.
• Параметры подготовленного запроса не требуется экранировать кавычками; драйвер это делает автоматически. Если в приложении используются исключительно подготовленные запросы, разработчик может быть уверен, что никаких SQL инъекций случиться не может (однако, если другие части текста запроса записаны с неэкранированными символами, SQL инъекции все же возможны; здесь речь идет именно о параметрах).

Подготовленные запросы также полезны тем, что PDO может эмулировать их, если драйвер базы данных не имеет подобного функционала. Это значит, что приложение может пользоваться одной и той же методикой доступа к данным независимо от возможностей СУБД.

Пример #1 Повторяющиеся вставки в базу с использованием подготовленных запросов

name и value , которые подставляются вместо соответствующих псевдопеременных:

$stmt = $dbh -> prepare ("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)" );
$stmt -> bindParam (":name" , $name );
$stmt -> bindParam (":value" , $value );

// вставим одну строку
$name = "one" ;
$value = 1 ;
$stmt -> execute ();

$name = "two" ;
$value = 2 ;
$stmt -> execute ();
?>

Пример #2 Повторяющиеся вставки в базу с использованием подготовленных запросов

В этом примере 2 раза выполняется INSERT запрос с разными значениями name и value которые подставляются вместо псевдопеременных ? .

$stmt = $dbh -> prepare ("INSERT INTO REGISTRY (name, value) VALUES (?, ?)" );
$stmt -> bindParam (1 , $name );
$stmt -> bindParam (2 , $value );

// вставим одну строку
$name = "one" ;
$value = 1 ;
$stmt -> execute ();

// теперь другую строку с другими значениями
$name = "two" ;
$value = 2 ;
$stmt -> execute ();
?>

Пример #3 Выборка данных с использованием подготовленных запросов

В этом примере производится выборка из базы по ключу, который вводит пользователь через форму. Пользовательский ввод автоматически заключается в кавычки, поэтому нет риска SQL инъекции.

Если СУБД поддерживает выходные параметры, приложение может пользоваться ими также как и входными. Выходные параметры обычно используют для получения данных из хранимых процедур. Пользоваться выходными параметрами несколько сложнее, так как разработчику необходимо знать максимальный размер извлекаемых значений еще на этапе задания этих параметров. Если извлекаемое значение окажется больше, чем предполагалось, будет вызвана ошибка.

Пример #4 Вызов хранимой процедуры с выходными параметрами

$stmt = $dbh -> prepare ("CALL sp_returns_string(?)" );
$stmt -> bindParam (1 , $return_value , PDO :: PARAM_STR , 4000 );

// вызов хранимой процедуры
$stmt -> execute ();

print "процедура вернула $return_value \n" ;
?>

Можно задать параметр одновременно входным и выходным; синтаксис при этом тот же, что и для выходных параметров. В следующем примере строка "привет" передается в хранимую процедуру, а затем эта строка будет заменена возвращаемым значением.

Пример #5 Вызов хранимой процедуры с входным/выходным параметром

$stmt = $dbh -> prepare ("CALL sp_takes_string_returns_string(?)" );
$value = "привет" ;
$stmt -> bindParam (1 , $value , PDO :: PARAM_STR | PDO :: PARAM_INPUT_OUTPUT , 4000 );

// вызов хранимой процедуры
$stmt -> execute ();

print "процедура вернула $value \n" ;
?>

Many of the more mature databases support the concept of prepared statements. What are they? They can be thought of as a kind of compiled template for the SQL that an application wants to run, that can be customized using variable parameters. Prepared statements offer two major benefits:

• The query only needs to be parsed (or prepared) once, but can be executed multiple times with the same or different parameters. When the query is prepared, the database will analyze, compile and optimize its plan for executing the query. For complex queries this process can take up enough time that it will noticeably slow down an application if there is a need to repeat the same query many times with different parameters. By using a prepared statement the application avoids repeating the analyze/compile/optimize cycle. This means that prepared statements use fewer resources and thus run faster.
• The parameters to prepared statements don"t need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible).

Prepared statements are so useful that they are the only feature that PDO will emulate for drivers that don"t support them. This ensures that an application will be able to use the same data access paradigm regardless of the capabilities of the database.

Example #1 Repeated inserts using prepared statements

name and a value for the named placeholders.

$stmt = $dbh -> prepare ("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)" );
$stmt -> bindParam (":name" , $name );
$stmt -> bindParam (":value" , $value );

// insert one row
$name = "one" ;
$value = 1 ;
$stmt -> execute ();

$name = "two" ;
$value = 2 ;
$stmt -> execute ();
?>

Example #2 Repeated inserts using prepared statements

This example performs an INSERT query by substituting a name and a value for the positional ? placeholders.

$stmt = $dbh -> prepare ("INSERT INTO REGISTRY (name, value) VALUES (?, ?)" );
$stmt -> bindParam (1 , $name );
$stmt -> bindParam (2 , $value );

// insert one row
$name = "one" ;
$value = 1 ;
$stmt -> execute ();

// insert another row with different values
$name = "two" ;
$value = 2 ;
$stmt -> execute ();
?>

Example #3 Fetching data using prepared statements

Example #4 Calling a stored procedure with an output parameter

If the database driver supports it, an application may also bind parameters for output as well as input. Output parameters are typically used to retrieve values from stored procedures. Output parameters are slightly more complex to use than input parameters, in that a developer must know how large a given parameter might be when they bind it. If the value turns out to be larger than the size they suggested, an error is raised.

$stmt = $dbh -> prepare ("CALL sp_returns_string(?)" );
$stmt -> bindParam (1 , $return_value , PDO :: PARAM_STR , 4000 );

// call the stored procedure
$stmt -> execute ();

print "procedure returned $return_value \n" ;
?>

Example #5 Calling a stored procedure with an input/output parameter

Developers may also specify parameters that hold values both input and output; the syntax is similar to output parameters. In this next example, the string "hello" is passed into the stored procedure, and when it returns, hello is replaced with the return value of the procedure.

$stmt = $dbh -> prepare ("CALL sp_takes_string_returns_string(?)" );
$value = "hello" ;
$stmt -> bindParam (1 , $value , PDO :: PARAM_STR | PDO :: PARAM_INPUT_OUTPUT , 4000 );

// call the stored procedure
$stmt -> execute ();

print "procedure returned $value \n" ;
?>

В этой заключительной статье мы рассмотрим, что такое подготовленные запросы , как отлавливать ошибки и что такое транзакции в PDO .

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

Когда мы выполняем какой-то запрос к базе данных, он анализируется и оптимизируется, что, естественно, занимает время. Если у нас много сложных запросов, то это может выполняться очень даже долго. Используя же подготовленные запросы , это делается один раз, а затем мы можем использовать наш запрос сколько угодно раз. Также, нам не нужно экранировать параметры, т.к. драйвер базы данных сделает всё сам. Давайте посмотрим, как их использовать.

$stmt = $db->prepare("INSERT INTO articles (title, text) VALUES (:title, :text)");
$stmt->bindParam(":title", $title);
$stmt->bindParam(":text", $text);

$stmt->execute();

$stmt->execute();

Чтобы подготовить запрос, мы пишем его в методе prepare , где вместо значений мы указываем строчку такого вида: ":название" . В методе bindParam мы указываем, к какой строчке какие данные привязать. В нашем случае к строчке :title привязываются данные из переменной $title , а к строчке :text - данные из переменной $text . Чтобы выполнить запрос, нужно вызвать метод execute . Такие параметры называются именованными , теперь посмотрим на неименованные .

$stmt = $db->prepare("INSERT INTO articles (title, text) VALUES (?, ?)");
$stmt->bindParam(1, $title);
$stmt->bindParam(2, $text);

$title = "название статьи 1";
$text = "Какой-то текст к первой статье";
$stmt->execute();

$title = "название статьи 2";
$text = "Какой-то текст ко второй статье";
$stmt->execute();

Здесь всё идентично, кроме того, что вместо строчки :название указывается знак вопроса, а в методе bindParam цифра 1 означает первый знак вопроса, а цифра 2 - второй знак вопроса. Используйте тот способ, который вам больше нравится.

Отлавливание ошибок

Чтобы отлавливать ошибки, мы используем уже знакомую нам конструкцию try-catch и класс PDOException .

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();
}

В качестве примера, я допустил ошибку и написал myql , а не mysql . Эта ошибка будет отловлена и нам выведится её текст и на какой линии произошла ошибка.

Транзакции

Давайте рассмотрим транзакции сразу на примере.

Try {
$db = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
$db->beginTransaction();

$stmt = $db->exec("INSERT INTO `articles`(`title`) VALUES("title1")");
$stmt = $db->exec("INSERT INTO `articles`(`title`) VALUES("title2")");
exit("error");

$stmt = $db->exec("INSERT INTO `articles`(`title`) VALUES("title3")");
$db->commit();

} catch(PDOException $e) {
$db->rollBack();
}

beginTransaction означает, что мы начинаем транзакцию. Commit подтверждает изменения, а rollBack отменяет всё.

Суть транзакций в том, что мы либо делаем всё, либо не делаем ничего. В нашем примере мы вставляем в таблицу articles значения title1 , title2 , title3 . Но после вставки второго значения, мы сэмулировали ошибку, остановив скрипт с помощью exit . Если бы мы не использовали транзакции , то у нас первые два title вставились бы, а последний нет. В нашем примере это не существенно, но бывают случаи, когда это может привести к серьёзным сбоям в работе приложения. Вот, чтобы такого не случалось, мы и используем транзакции , где метод rollBack вернёт всё в первоначальный вид и наши первые два title тоже вставлены не будут, а если всё прошло удачно, без ошибок, то метод commit подтвердит изменения и все три title будут успешно вставлены.

Заключение

Итак, в наших трёх статьях, посвящённых изучению PDO , мы разобрали всё, что нужно, чтобы с лёгкостью использовать этот интерфейс. Думаю, вы поняли, как PDO облегчает нам жизнь и будете использовать его в своих проектах. Удачи!

Это таблицы, но только для чтения (можно вносить некоторые изменения, но они крайне ограничены). По сути, это обычная таблица, но она создается на основе какого-то запроса (других таблиц), т.е. это ‘ссылка’ на какой-то запрос. Рассмотрим пример:

CREATE TABLE t(name, price); //создаем таблицу CREATE VIEW v AS SELECT name, price, name * price AS value FROM t;//создаем другую таблицу, третье поле как произведение первых двух SELECT * FROM v; //берем данные из таблицы

Т.е. мы создали таблицу с третьим полем, о котором никто не знает. И показывать его необязательно всем. Т.е. мы можем создать таблицу посредством View, например в компании, для отдела кадров, для работников, для учебного отдела, для бухгалтерии. Действие напоминает использование первой таблицы как шаблона, и добавление к нему новых полей.

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

Бывают ситуации, когда у нас много записей (например, 50000) в БД, и они в цикле выбираются. Если мы будем mysql_query туда запихивать, то 50000 раз этот запрос буде анализироваться. Чтобы не терять время на такой анализ, существует подготовленный запрос - это запрос, который отдается БД заранее, он один раз анализируется, и база готова его принимать. Пример:

Mysql_connect("localhost", "root", "password"); mysql_select_db("test"); mysql_query("PREPARE myinsert FROM // пишем имя подготовленного запроса "INSERT INTO test_table (name, price) VALUES (?, ?)""); //вот подготовленный запрос for ($i = 0; $i < 1000; $i++){ mysql_query("SET @name = "Товар # $i""); //установить значение "товар" для переменной @name mysql_query("SET @price = " . ($i * 10)); //установить значение цены для переменной @price mysql_query("EXECUTE myinsert USING @name, @price"); //исполнить подготовленный запрос, используя эти две переменные } mysql_close();

В строке подготовленного вопроса вставляемые значения неизвестны (знак?). Потом в цикле закидываем значения в таблицу. Т.е. внутри языка mysql мы видим свои переменные, свои функции.