Подготовленные запросы и хранимые процедуры. MySQL дополнительные возможности Преимущества использования связанных параметров

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

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 мы видим свои переменные, свои функции.

Термин 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 свой собственный хитровыдуманный способ соединения, называемый . Плюс во время коннекта можно задать хренову тучу опций, некоторые из которых чрезвычайно полезны. Полный список можно найти , но важными из них являются только несколько.

Пример правильного соединения:

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