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:

<?php

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

How it works.

First, create a new connection to the MySQL database:

$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

• Use a prepared statement to call a MySQL stored procedure from PHP.