PHP MySQL: Call MySQL Stored Procedures

Summary: In this tutorial, you will learn how to call MySQL stored procedures from PHP, including stored procedures that accept IN & OUT parameters.

Creating stored procedures

The following creates a stored procedure GetAllTasks() that retrieves all rows from the tasks table:

DELIMITER $$

CREATE PROCEDURE GetAllTasks()
BEGIN
	SELECT * FROM tasks;
END$$

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

The following creates a stored procedure GetTaskStatus() that returns the status of a task based on its ID:

DELIMITER $$

CREATE PROCEDURE GetTaskStatus(
    in  p_id int, 
    out p_completed bool
)    
BEGIN
    DECLARE task_status bool;
    
    SELECT completed INTO p_completed
    FROM tasks
    WHERE id = p_id;
END$$

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

Calling stored procedures that return result sets

To call a stored procedure that returns a result set, you follow these steps:

• First, connect to the MySQL database.
• Second, execute a statement that calls the stored procedure using the query() method.
• Third, fetch all rows from the result set.

The following PHP script illustrates how to call the stored procedure GetAllTasks():

<?php
require_once 'config.php';

try {
    // connect to MySQL server
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    
    // execute a query
    $sql = 'CALL GetAllTasks()';
    $stmt = $conn->query($sql);
    $stmt->setFetchMode(PDO::FETCH_ASSOC);

    // process the results
    $tasks = [];
    while ($row = $stmt->fetch()) {
        $tasks[] = [
            'title' => $row['title'],
            'completed' => $row['completed'] == 0 ? false : true
        ];
    }
    
    // display the task list
    require 'select.view.php';
} catch (PDOException $e) {
    die($e);
}Code language: PHP (php)

Calling stored procedures with an OUT parameter

The following script calls the GetTaskStatus() stored procedure that accepts a task ID and returns the task’s status:

<?php
require_once 'config.php';

try {
    // connect to MySQL server
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    
    // call the GetTaskStatus
    $sql = 'CALL GetTaskStatus(:id, @task_status)';
    $stmt = $conn->prepare($sql);

    // bind the task id
    $task_id = 1;
    $stmt->bindParam(':id', $task_id, PDO::PARAM_INT);

    // execute the query & close the cursor
    $stmt->execute();
    $stmt->closeCursor();

    // execute the second query to get the task status
    $row = $conn->query('SELECT @task_status AS task_status')->fetch(PDO::FETCH_ASSOC);
    if ($row) {
        echo $row !== false ? $row['task_status'] : null;
    }
} catch (PDOException $e) {
    die($e);
}
Code language: PHP (php)

How it works.

• First, execute a query that calls a stored procedure and save the out parameter into a session variable @task_status.
• Second, execute a second query that retrieves the value from the @task_status variable.

Note that the script returns the status of the task with id 1.