{"id":1362,"date":"2021-04-23T02:07:51","date_gmt":"2021-04-23T02:07:51","guid":{"rendered":"https:\/\/phptutorial.net\/?page_id=1362"},"modified":"2025-04-09T03:28:50","modified_gmt":"2025-04-09T03:28:50","slug":"php-pdo-call-mysql-stored-procedure","status":"publish","type":"page","link":"https:\/\/www.phptutorial.net\/php-pdo\/php-pdo-call-mysql-stored-procedure\/","title":{"rendered":"Call a MySQL Stored Procedure Using PHP PDO"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to call a MySQL stored procedure using the PHP PDO.<\/p>\n\n\n\n

Creating a stored procedure in MySQL #<\/a><\/h2>\n\n\n\n

To execute a statement in the MySQL database, you can use any MySQL client tool e.g., mysql client tool or MySQL Workbench.<\/p>\n\n\n\n

First, insert data into the authors<\/code> table by executing the following INSERT<\/a><\/code> statement:<\/p>\n\n\n

INSERT<\/span> INTO<\/span> books(title, isbn, published_date,publisher_id)\nVALUES<\/span>\n('Goodbye to All That'<\/span>,'9781541619883'<\/span>,'2013-01-05'<\/span>, 3<\/span>),\n('The Mercies'<\/span>,'9780316529235'<\/span>,'2020-01-28'<\/span>, 3<\/span>),\n('On the Farm'<\/span>,'9780763655914'<\/span>,'2012-03-27'<\/span>, 2<\/span>),\n('Joseph Had a Little Overcoat'<\/span>,'9780140563580'<\/span>,'1977-03-15'<\/span>, 2<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, execute the following CREATE PROCEDURE<\/a><\/code> statement to create a new stored procedure called get_books_published_after<\/code>:<\/p>\n\n\n
USE<\/span> `bookdb`<\/span>;\n\nDELIMITER $$\nUSE<\/span> `bookdb`<\/span>$$\nCREATE<\/span> PROCEDURE<\/span> `get_books_published_after`<\/span> (IN<\/span> published_year INT<\/span>)\nBEGIN<\/span>\n\tSELECT<\/span> \n\t\tbook_id, title, isbn, published_date, name<\/span> as<\/span> publisher\n\tFROM<\/span> \n\t\tbooks b\n\tINNER<\/span> JOIN<\/span> publishers p \n\t\tON<\/span> p.publisher_id = b.publisher_id\n\tWHERE<\/span> year<\/span>(published_date) > published_year;  \nEND<\/span>$$\n\nDELIMITER ;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The stored procedure get_books_published_after<\/code> returns all books published after a specific year.<\/p>\n\n\n\n
Third, execute the stored procedure to check the result set:<\/p>\n\n\n
CALL<\/span> get_books_published_after(2010<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The statement returns the following result set:<\/p>\n\n\n
+---------+---------------------+---------------+----------------+----------------------+\n| book_id | title               | isbn          | published_date | publisher            |\n+---------+---------------------+---------------+----------------+----------------------+\n|       1 | Goodbye to All That | 9781541619883 | 2013-01-05     | Hachette Book Group  |\n|       2 | The Mercies         | 9780316529235 | 2020-01-28     | Hachette Book Group  |\n|       3 | On the Farm         | 9780763655914 | 2012-03-27     | Penguin\/Random House |\n+---------+---------------------+---------------+----------------+----------------------+\n3 rows in set (0.005 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Calling a MySQL stored procedure from PHP using PDO #<\/a><\/h2>\n\n\n\n
The following script illustrates how to call the get_books_published_after<\/code> stored procedure:<\/p>\n\n\n
<?php<\/span>\n\n$published_year = 2010<\/span>;\n\n\/\/ connect to the database and select the publisher<\/span>\n$pdo = require<\/span> 'connect.php'<\/span>;\n\n$sql = 'CALL get_books_published_after(:published_year)'<\/span>;\n\n$publishers = [];\n\n$statement = $pdo->prepare($sql);\n$statement->bindParam(':published_year'<\/span>, $published_year, PDO::PARAM_INT);\n$statement->execute();\n\n$publishers = $statement->fetchAll(PDO::FETCH_ASSOC);\n\nprint_r($publishers);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
$pdo = require<\/span> 'connect.php'<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, construct a SQL statement that calls the get_books_published_after<\/code> stored procedure:<\/p>\n\n\n
$sql = 'CALL get_books_published_after(:published_year)'<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
The statement accepts a named placeholder :published_year<\/code> so that you can bind a value to it later.<\/p>\n\n\n\n
Third, create a prepared statement by calling the prepare()<\/code> method of the PDO instance:<\/p>\n\n\n
$statement = $pdo->prepare($sql);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, bind a value to the statement:<\/p>\n\n\n
$statement->bindParam(':published_year'<\/span>, $published_year, PDO::PARAM_INT);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, execute the stored procedure call:<\/p>\n\n\n
$statement->execute();<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Since the stored procedure returns a result set, you can fetch each row in the result set into an associative array using the fetchAll()<\/code> method:<\/p>\n\n\n
$publishers = $statement->fetchAll(PDO::FETCH_ASSOC);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use a prepared statement to call a MySQL stored procedure from PHP.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Did you find this tutorial useful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
First, create a new connection to the MySQL database<\/a>:<\/p>\n\n\n
Note that the publishers<\/code> table should have rows with id 2 and 3. If it doesn’t, you can run the script that inserts rows<\/a> into the publishers<\/code> table.<\/p>\n\n\n\n