PHP: Calling MySQL Stored Procedure with Both INPUT AND OUTPUT Parameters (NOT "INOUT")

Question

From PHP I would like to call a stored procedure in MySQL. The procedure takes input and output parameters -- not "INOUT" parameters.

For a simple example, say I have the following stored procedure in MySQL:

DELIMITER $$

DROP PROCEDURE IF EXISTS `test_proc`$$
CREATE PROCEDURE `test_proc`(
    in input_param_1 int,
    in input_param_2 int,
    in input_param_3 int,
    out output_sum int,
    out output_product int,
    out output_average int
)
BEGIN
    set output_sum = input_param_1 + input_param_2 + input_param_3;
    set output_product = input_param_1 * input_param_2 * input_param_3;
    set output_average = (input_param_1 + input_param_2 + input_param_3) / 3;
END$$

DELIMITER ;

Now, from the PHP script/page side, say I have the following variables (we'll call them "proc input variables") that I want to feed to the stored procedure as input parameters when I call it:

$procInput1 = "123";
$procInput2 = "456";
$procInput3 = "789";

Let's say that on the PHP script/page side I also have the following variables (we'll call them "proc output variables") that I want to feed to the stored procedure as output parameters to be set by the stored procedure when I call it:

$procOutput_sum;
$procOutput_product;
$procOutput_average;

So, in essence, on the PHP script/page side, what I want to be able to do, in essence (I realize the following code is not valid), is...

call test_proc($procInput1, $procInput2, $procInput3, $procOutput_sum, $procOutput_product, $procOutput_average);

...and, once called, the following PHP code...

echo "Sum: ".$procOutput_sum;
echo "Product: ".$procOutput_product;
echo "Average: ".$procOutput_average;

...should produce the following output:

Sum: 1368
Product: 44253432
Average: 456

One caveat is that, if at all possible, I would like to be able to do this using the MySQLi procedural functions/interface. If not possible, then however I can get it to work is what I'll use.

I have been programming for quite some time, but the PHP language is a relatively new endeavor for me. I have found tons of tutorials on calling MySQL stored procedures from PHP. Some are tutorials on calling stored procedures with input parameters, some are tutorials on calling stored procedures with output parameters, and some are tutorials on calling stored procedures with inout parameters. I have not found any tutorials or examples on calling stored procedures that take both input and output parameters at the same time, while specifically not using "inout" parameters. I'm having trouble figuring out how to code the parameter bindings (e.g.: mysqli_stmt_bind_param and mysqli_stmt_bind_result) and getting it all to work properly.

Any help will be greatly appreciated and I give thanks in advance!

Answer 1

<?php

class UserController{
    private UserModel $model;

    public function __construct(){
        $this->model = new UserModel();
    }


    public function Get(){
        if (isset($_GET["id"])){
            $user = $this->model->GetUser($_GET["id"]);
            if(!empty($user)){
                echo json_encode($user);
                return;
            }
        }
        
        http_response_code(404);
        echo json_encode(["message" => "User not found"]);
    }

    public function Delete(): void{
        if (isset($_GET["id"])){

            $user = $this->model->GetUser($_GET["id"]);
            $res = $this->model->DeleteUser($_GET["id"]);
            if(!empty($user) && $res){
                echo json_encode(["message" => "User deleted successfully"]);
                return;
            }
        }

        http_response_code(404);
        echo json_encode(["message" => "User not found"]);
    }

    public function Update(){
        $data = (array) json_decode(file_get_contents("php://input"));
        $errors = $this->Validator($data, true);

        if(! empty($errors)){
            http_response_code(422);
            echo json_encode(["errors" => $errors]);
            return;
        }

        $user = $this->model->GetUser($data["id"]);

        if(!empty($user)){
            $res = $this->model->UpdateUser($data["id"], $data["username"], $data["email"], $data["password"]);
            if($res){
                echo json_encode(["message" => "User updated successfully"]);
                return;
            }
        }

        http_response_code(404);
        echo json_encode(["message" => "User not found"]);
        
    }

    public function New(){
        $data = (array) json_decode(file_get_contents("php://input"));
        $errors = $this->Validator($data);

        if(! empty($errors)){
            http_response_code(422);
            echo json_encode(["errors" => $errors]);
            return;
        }

    
        $res = $this->model->CreateUser($data["username"], $data["email"], $data["password"]);
        if($res){
            echo json_encode(["message" => "User created successfully"]);
            return;
        }
        
        http_response_code(404);
        echo json_encode(["message" => "Error"]);
        
    }

    private function Validator(array $data, bool $is_update = false){
        $errors  = [];

        if(empty($data["id"]) && $is_update){
            array_push($errors, "Id is required");
        }

        if(empty($data["username"])){
            array_push($errors, "Username is required");
        }

        return $errors;
    }
}

?>

<?php

class Database{

    private string $user;
    private string $password;
    private string $host;
    private string $name;

    public function __construct(string $user, string $password, string $host, string $name){
        $this->user = $user;
        $this->password = $password;
        $this->host = $host;
        $this->name = $name;
    }

    public function getConnection(): PDO{
        $dsn = "mysql:host={$this->host};port=3306;dbname={$this->name};charset=utf8";
        return new PDO($dsn, $this->user, $this->password);
    }
}


?>


<?php

class UserModel{
    private int $id;
    private string $username;
    private string $email;
    private string $password;
    private string $created_at;
    private string $updated_at;

    private PDO $conn;


    public function __construct(){
        $db = new Database("root", "username", "password", "dbname");
        $this->conn = $db->getConnection();
    }

    public function CreateUser(string $username, string $email, string $password): bool{
        $sql = "CALL CreateUser(:p_username, :p_email, :p_password)";
        $stmt = $this->conn->prepare($sql);

        $stmt->bindValue(":p_username", $username, PDO::PARAM_STR);
        $stmt->bindValue(":p_email", $email, PDO::PARAM_STR);
        $stmt->bindValue(":p_password", $password, PDO::PARAM_STR);


        return $stmt->execute();
    }

    public function DeleteUser(int $id): bool{
        $sql = "CALL DeleteUser(:p_user_id)";
        $stmt = $this->conn->prepare($sql);

        $stmt->bindValue(":p_user_id", $id, PDO::PARAM_INT);

        return $stmt->execute();
    }

    public function UpdateUser(int $id, string $username, string $email, string $password): bool{
        $sql = "CALL UpdateUser(:p_user_id, :p_username, :p_email, :p_password)";
        $stmt = $this->conn->prepare($sql);

        $stmt->bindValue(":p_user_id", $id, PDO::PARAM_INT);
        $stmt->bindValue(":p_username", $username, PDO::PARAM_STR);
        $stmt->bindValue(":p_email", $email, PDO::PARAM_STR);
        $stmt->bindValue(":p_password", $password, PDO::PARAM_STR);

        return $stmt->execute();
    }

    public function GetUser(int $id): array{
        $sql = "CALL GetUser(:p_user_id)";
        $stmt = $this->conn->prepare($sql);

        $stmt->bindValue(":p_user_id", $id, PDO::PARAM_INT);
        $stmt->execute();

        $data = $stmt->fetch(PDO::FETCH_ASSOC);

        if(!$data){
            return [];
        }
        return $data;
    }
}

?>

<?php
spl_autoload_register(function($class){
    require(_DIR_ . "/src/$class.php");
});
header("Content-type: application/json; charset=UTF-8");
header("Access-control-Allow-origin: *");
header("Access-control-Allow-Methods: GET, POST, PUT, PATCH, DELETE, OPTIONS");
$parts = explode("/", $_SERVER['REDIRECT_URL']);
if($parts[2] != "users"){
    http_response_code(404);
    exit;
}
$enpoint = $parts[3] ?? null;
$controller = new UserController();
if($enpoint == "get" && $_SERVER['REQUEST_METHOD'] === 'GET'){
    $controller->Get();
}else if($enpoint == "delete" && $_SERVER['REQUEST_METHOD'] === 'DELETE'){
    $controller->Delete();
}else if($enpoint == "new" && $_SERVER['REQUEST_METHOD'] === 'POST'){
    $controller->New();
}else if($enpoint == "update" && $_SERVER['REQUEST_METHOD'] === 'PATCH'){
    $controller->Update();
}else{
http_response_code(405);
}
?>

Answer 2

 DB::select('call SP_fci_cb_emp_comp_salary_revision_add(?,?,?,@,@)',
                        [
                            $my_compensation['detail']['employee_master_id'],
                            Carbon::createFromFormat('d/m/Y', $salary_revised_result['detail']->effective_date)->format('Y-m-d'),
                            $request->user_id
                        ]);

Answer 3

Unfortunately, MySQLi does not have any native support for output sproc parameters; one must instead output into MySQL user variables and then fetch the values using a separate SELECT statement.

Using the procedural interface:

$procInput1 = 123;
$procInput2 = 456;
$procInput3 = 789;

$mysqli = mysqli_connect();

$call = mysqli_prepare($mysqli, 'CALL test_proc(?, ?, ?, @sum, @product, @average)');
mysqli_stmt_bind_param($call, 'iii', $procInput1, $procInput2, $procInput3);
mysqli_stmt_execute($call);

$select = mysqli_query($mysqli, 'SELECT @sum, @product, @average');
$result = mysqli_fetch_assoc($select);
$procOutput_sum     = $result['@sum'];
$procOutput_product = $result['@product'];
$procOutput_average = $result['@average'];

Or, using the object-oriented interface:

$procInput1 = 123;
$procInput2 = 456;
$procInput3 = 789;

$mysqli = new mysqli();

$call = $mysqli->prepare('CALL test_proc(?, ?, ?, @sum, @product, @average)');
$call->bind_param('iii', $procInput1, $procInput2, $procInput3);
$call->execute();

$select = $mysqli->query('SELECT @sum, @product, @average');
$result = $select->fetch_assoc();
$procOutput_sum     = $result['@sum'];
$procOutput_product = $result['@product'];
$procOutput_average = $result['@average'];