How to Call MySQL Stored Procedures with Both Input and Output Parameters in PHP?

Calling MySQL Stored Procedures with Both Input and Output Parameters (Not "INOUT" Parameters) in PHP

Introduction:

Calling MySQL stored procedures from PHP is a common task, but when dealing with both input and output parameters, it can be more complex. This article will guide you through the specific approach required to make such calls using MySQLi's procedural interface.

Stored Procedure Configuration:

Consider the following stored procedure in MySQL:

DELIMITER $$
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 ;

Binding Input and Output Parameters:

To call the stored procedure and specify both input and output parameters, use the mysqli_stmt_bind_param() function:

mysqli_stmt_bind_param($call, 'iii', $procInput1, $procInput2, $procInput3);

where $procInput1, $procInput2, and $procInput3 are the input parameters.

To retrieve the output parameters, you'll need to use user variables in your stored procedure and then fetch their values after executing the call:

$select = mysqli_query($mysqli, 'SELECT @sum, @product, @average');
$result = mysqli_fetch_assoc($select);

where $select is a query that retrieves the user variables (@sum, @product, @average) set by the stored procedure.

Code Example:

Here's a complete code example:

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

By following these steps, you can successfully call MySQL stored procedures that require both input and output parameters.

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