How can I call a MySQL Stored Procedure with Both Input and Output Parameters in PHP?

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

When calling a stored procedure in MySQL that requires both input and output parameters (excluding INOUT parameters), it's essential to understand the nuances of PHP's connection and statement interfaces.

Procedure Setup

Consider the following MySQL stored procedure, which takes input parameters and calculates output values:

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 ;

PHP Connection and Statement

To call this procedure, you can use either the procedural or object-oriented interface for MySQLi.

Procedural Interface:

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

Object Oriented Interface:

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

Parameter Binding

For both interfaces, the parameter binding step is crucial. Input parameters are typically bound with 'i' for integers, and output parameters are bound using '@' followed by the parameter name.

Output Retrieval

After executing the CALL statement, a separate SELECT statement is executed to retrieve the values from the MySQL user variables (@sum, @product, @average) that the stored procedure populated.

Conclusion

While MySQLi does not natively support direct output parameter binding, using user variables and a subsequent SELECT statement allows developers to work with procedures that require both input and output parameters.

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