Call a MySQL Stored Procedure Using PHP PDO
Summary: in this tutorial, you will learn how to call a MySQL stored procedure using the PHP PDO.
Setting up a stored procedure in MySQL
To execute a statement in the MySQL database, you can use any MySQL client tool e.g., mysql client tool or MySQL Workbench.
First, insert data into the authors table by executing the following INSERT statement:
INSERT INTO books(title, isbn, published_date,publisher_id) VALUES ('Goodbye to All That','9781541619883','2013-01-05', 3), ('The Mercies','9780316529235','2020-01-28', 3), ('On the Farm','9780763655914','2012-03-27', 2), ('Joseph Had a Little Overcoat','9780140563580','1977-03-15', 2);Code language: SQL (Structured Query Language) (sql)Note that the publishers table should have rows with id 2 and 3. If it doesn’t, you can run the script that inserts rows into the publishers table.
Second, execute the following CREATE PROCEDURE statement to create a new stored procedure called get_books_published_after :
USE `bookdb`; DELIMITER $$ USE `bookdb`$$ CREATE PROCEDURE `get_books_published_after` (IN published_year INT) BEGIN SELECT book_id, title, isbn, published_date, name as publisher FROM books b INNER JOIN publishers p ON p.publisher_id = b.publisher_id WHERE year(published_date) > published_year; END$$ DELIMITER ;Code language: SQL (Structured Query Language) (sql)The stored procedure get_books_published_after returns all books published after a specific year.
Third, execute the stored procedure to check the result set:
CALL get_books_published_after(2010);Code language: SQL (Structured Query Language) (sql)The statement returns the following result set:
+---------+---------------------+---------------+----------------+----------------------+ | book_id | title | isbn | published_date | publisher | +---------+---------------------+---------------+----------------+----------------------+ | 1 | Goodbye to All That | 9781541619883 | 2013-01-05 | Hachette Book Group | | 2 | The Mercies | 9780316529235 | 2020-01-28 | Hachette Book Group | | 3 | On the Farm | 9780763655914 | 2012-03-27 | Penguin/Random House | +---------+---------------------+---------------+----------------+----------------------+ 3 rows in set (0.005 sec)Code language: plaintext (plaintext)Calling a MySQL stored procedure from PHP using PDO
The following script illustrates how to call the get_books_published_after stored procedure:
$published_year = 2010; // connect to the database and select the publisher $pdo = require 'connect.php'; $sql = 'CALL get_books_published_after(:published_year)'; $publishers = []; $statement = $pdo->prepare($sql); $statement->bindParam(':published_year', $published_year, PDO::PARAM_INT); $statement->execute(); $publishers = $statement->fetchAll(PDO::FETCH_ASSOC); print_r($publishers); Code language: HTML, XML (xml)$pdo = require 'connect.php';Code language: PHP (php)Second, construct a SQL statement that calls the get_books_published_after stored procedure:
$sql = 'CALL get_books_published_after(:published_year)';Code language: PHP (php)The statement accepts a named placeholder :published_year so that you can bind a value to it later.
Third, create a prepared statement by calling the prepare() method of the PDO instance:
$statement = $pdo->prepare($sql);Code language: PHP (php)Fourth, bind a value to the statement:
$statement->bindParam(':published_year', $published_year, PDO::PARAM_INT);Code language: PHP (php)Fifth, execute the stored procedure call:
$statement->execute();Code language: PHP (php)Since the stored procedure returns a result set, you can fetch each row in the result set into an associative array using the fetchAll() method:
$publishers = $statement->fetchAll(PDO::FETCH_ASSOC);Code language: PHP (php)Summary
Подготовленные запросы и хранимые процедуры
Большинство баз данных поддерживают концепцию подготовленных запросов. Что это такое? Это можно описать, как некий вид скомпилированного шаблона SQL запроса, который будет запускаться приложением и настраиваться с помощью входных параметров. У подготовленных запросов есть два главных преимущества:
- Запрос необходимо однажды подготовить и затем его можно запускать столько раз, сколько нужно, причём как с теми же, так и с отличающимися параметрами. Когда запрос подготовлен, СУБД анализирует его, компилирует и оптимизирует план его выполнения. В случае сложных запросов этот процесс может занимать ощутимое время и заметно замедлить работу приложения, если потребуется много раз выполнять запрос с разными параметрами. При использовании подготовленного запроса СУБД анализирует/компилирует/оптимизирует запрос любой сложности только один раз, а приложение запускает на выполнение уже подготовленный шаблон. Таким образом подготовленные запросы потребляют меньше ресурсов и работают быстрее.
- Параметры подготовленного запроса не требуется экранировать кавычками; драйвер это делает автоматически. Если в приложении используются исключительно подготовленные запросы, разработчик может быть уверен, что никаких SQL-инъекций случиться не может (однако, если другие части текста запроса создаются с неэкранированным вводом, то SQL инъекция по-прежнему возможна).
Подготовленные запросы также полезны тем, что PDO может эмулировать их, если драйвер базы данных не имеет подобной функциональности. Это значит, что приложение может пользоваться одной и той же методикой доступа к данным независимо от возможностей СУБД.
Пример #1 Повторяющиеся вставки в базу с использованием подготовленных запросов
В этом примере 2 раза выполняется INSERT запрос с разными значениями 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 инъекции.
$stmt = $dbh -> prepare ( «SELECT * FROM REGISTRY where name = ?» );
$stmt -> execute ([ $_GET [ ‘name’ ]]);
foreach ( $stmt as $row ) print_r ( $row );
>
?>
Пример #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» ;
?>
Пример #6 Неправильное использование псевдопеременной
$stmt = $dbh -> prepare ( «SELECT * FROM REGISTRY where name LIKE ‘%?%'» );
$stmt -> execute ([ $_GET [ ‘name’ ]]);
// псевдопеременная может использоваться только в виде отдельного значения
$stmt = $dbh -> prepare ( «SELECT * FROM REGISTRY where name LIKE ?» );
$stmt -> execute ([ «% $_GET [ name ] %» ]);
?>