How to Retrieve Output Parameters from MySQL Stored Procedures in PHP?

Calling MySQL Stored Procedures with Both Input and Output Parameters in PHP

When working with MySQL stored procedures, it's often necessary to pass in and retrieve data from the database. This is where input and output parameters come into play. However, it can be challenging to use both input and output parameters, especially without using "INOUT" parameters.

Understanding the Procedure's Input and Output Parameters

Let's consider the example stored procedure in the MySQL database:

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
)

This procedure expects three input parameters (input_param_1, input_param_2, input_param_3) and three output parameters (output_sum, output_product, output_average). The input parameters are used to calculate the output parameters.

Binding Input and Output Parameters Using MySQLi

To call this stored procedure from PHP using MySQLi, you'll need to bind both the input and output parameters. In the following code, we use the procedural interface to establish a connection, prepare the stored procedure call, bind the input parameters, execute the call, and retrieve the output parameters from user variables:

<?php

$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'];

mysqli_stmt_close($call);
mysqli_close($mysqli);

?>

In the above code, the mysqli_prepare function prepares the stored procedure call. The mysqli_stmt_bind_param function binds the input parameters to the call. The mysqli_stmt_execute function executes the call.

After executing the stored procedure, we have to retrieve the output parameters from the user variables using a separate SELECT statement. This is because MySQLi lacks native support for retrieving output parameters directly.

Conclusion

By understanding how to bind both input and output parameters in MySQL stored procedures using MySQLi, you can effectively interact with your database and retrieve the necessary data for your PHP applications.

The above is the detailed content of How to Retrieve Output Parameters from MySQL Stored Procedures in PHP?. For more information, please follow other related articles on the PHP Chinese website!