Урок - Параметрические запросы PDO.

Главная » Курсы » Курс PHP5, PDO - PHP Data Objects » Урок - Параметрические запросы PDO.

Обучающий онлайн курс
PHP5, PDO - PHP Data Objects

Лицензия: Копирование запрещено.

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

Основные преимущества параметрических запросов:

  • Компилируется один раз. Запрос должен быть обработан или подготовлен только один раз, но может быть выполнен несколько раз с тем же или разными параметрами. Когда запрос подготовлен, база данных будет анализировать, обобщать и оптимизировать план выполнения запроса.
  • Скорость выполнения. Для сложных запросов процесс компиляции запроса может занять много времени, что будет заметно замедлять выполнение запроса, если есть необходимость повторять тот же запрос много раз с различными параметрами. Использовании параметрических запросов позволяет избежать цикл - анализ-компиляция-оптимизация. Это означает, что параметрический запрос будет использовать меньше ресурсов, и таким образом работать быстрее.
  • Разделение между структурой и входящими данными (предотвращение SQL-инъекций)
  • Эмуляция общего интерфейса. PDO может эмулировать работу параметрических запросов и для драйверов, которые не поддерживают их. Это гарантирует, что приложение будет в состоянии использовать такую же парадигму доступа к данным независимо от возможности базы данных.

Сначала рассмотрим примеры использования PDO::prepare() и PDOStatement::execute():

$pdoStatement = $pdo->prepare('SELECT * FROM `articles` WHERE id=?');
$pdoStatement->execute(array(1));
print_r($pdoStatement->fetchAll());
$pdoStatement->execute(array(2));
print_r($pdoStatement->fetchAll());

Как видите, запрос компилируется один раз, а затем передается новое значение в метод PDOStatement::execute() и значение вставляется на место вопросительного знака (?).

Вместо вопросительного знака можно использовать именные маркеры:

$pdoStatement = $pdo->prepare('SELECT * FROM `articles` WHERE id=:id');
$pdoStatement->execute(array(':id', 1));
print_r($pdoStatement->fetchAll());
$pdoStatement->execute(array(':id', 2));
print_r($pdoStatement->fetchAll());

Метод PDOStatement::bindParam() позволяет назначать маркеру внешнюю переменную:

$pdoStatement = $pdo->prepare('SELECT * FROM `articles` WHERE `id`=:id1');
$pdoStatement->bindParam(':id1', $id1);
$id1 = 1;
$pdoStatement->execute();
print_r( $pdoStatement->fetchAll());
$id2 = 2;
$pdoStatement->execute();
print_r( $pdoStatement->fetchAll());

Пример повторных вставок с использованием параметрических запросов:

$pdoStatement = $pdo->prepare("INSERT INTO `articles` (`title`, `weight`)
VALUES (:title, :weight)");
$pdoStatement->bindParam(':title', $title);
$pdoStatement->bindParam(':weight', $weight);

$title = 'Первая запись';
$weight = 1;
$pdoStatement->execute();

$title = 'Другая запись';
$weight = 2;
$pdoStatement->execute();

Метод PDOStatement::bindParam() может принимать 3-й параметр, который указывает тип данных:

  • PDO::PARAM_INT- обрабоать как целое значение
  • PDO::PARAM_STR- обработать как строку
  • PDO::PARAM_LOB - обработать большие объемы данных

PDO::PARAM_STR и PDO::PARAM_INT определяют тип входных данных и делают работу которую раньше выполнял mysql_real_escape_string().

$pdoStatement = $pdo->prepare("INSERT INTO `articles` (`title`, `weight`) VALUES (:title, :weight)");
$pdoStatement->bindParam(':title', $title, PDO::PARAM_STR);
$pdoStatement->bindParam(':weight', $weight, PDO::PARAM_INT);

$title = 'Первая запись';
$weight = 1;
$pdoStatement->execute();

$title = 'R'a"m\'e\"c\h';
$weight = 2;
$pdoStatement->execute();

PDO::PARAM_LOB дает возможность работать с большими объемами данных. PDO::PARAM_LOB будет рассмотрен в части "Работа с данными больших размеров средствами PDO".

Для метода PDOStatement::bindParam() предусмотрена возможность не только передавать данные параметризованными запросами, но и получать результат в теже самые переменные:

// Вызов известной процедуры с параметром INOUT
$colour = 'red';
$pdoStatement = $pdo->prepare('CALL puree_fruit(?)');
$pdoStatement->bindParam(1, $colour, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 12);
$pdoStatement->execute();
print("After pureeing fruit, the colour is: $colour");

Пример взят из официальной документации, но в некоторых версиях PDO он не работает. Ошибка в некоторых версиях PDO приводит к тому, что код указанный ниже не будет работать!!! Ссылки на описание ошибок http://bugs.php.net/bug.php?id=46657, http://bugs.php.net/bug.php?id=43887, http://bugs.php.net/bug.php?id=35935

Ниже приведен дополнительный пример, с созданием процедуры и проверками:

// пересоздаем процедуру
$pdo->query('DROP PROCEDURE IF EXISTS ramech');
$pdo->query('
CREATE PROCEDURE `ramech`(INOUT ramechVar INTEGER(11))
BEGIN
SET ramechVar = 123;
END;
');

// проверяем наличие функции
$pdoStatement = $pdo->query('SHOW PROCEDURE STATUS LIKE "ramech"');
print_r($pdoStatement->fetchObject());

// проверяем работу стандартными методами
$pdo->query('SET @a = 1;');
$pdo->query('CALL ramech(@a);');
$pdoStatement = $pdo->query('SELECT @a;');
print_r($pdoStatement->fetchAll());

// Запускаем
$pdoStatement = $pdo->prepare('CALL ramech(?)');
$value = 100; // начальное значение
$pdoStatement->bindParam(1, $value, PDO::PARAM_INT | PDO::PARAM_INPUT_OUTPUT, 12);
$pdoStatement->execute();
print_r($value); // должно вернуть 123, но вернет 100
// просматриваем ошибку
print_r($pdoStatement->errorInfo());

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

$pdo->query('DROP PROCEDURE IF EXISTS ramech');
$pdo->query('
CREATE PROCEDURE `ramech`(
IN ramechIn varChar(255),
OUT ramechOut1 varChar(255),
OUT ramechOut2 varChar(255)
)
BEGIN
SET ramechOut1 = CONCAT("Return Value 1", ramechIn);
SET ramechOut2 = CONCAT("Return Value 2", ramechIn);
END;
');

$inValue = '-suffix';
$stmt = $pdo->prepare("CALL ramech3(?, @ret1, @ret2)");
$stmt->bindParam(1, $inValue, PDO::PARAM_STR);
$stmt->execute();
$stmt = $pdo->query('SELECT @ret1, @ret2;');
print_r($stmt->fetchObject());