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

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

Developers often encounter the need to call stored procedures from PHP scripts. Stored procedures can take a combination of input and output parameters, with input parameters providing values to the procedure and output parameters receiving calculated values from the procedure. This article focuses on calling MySQL stored procedures that specifically take both input and output parameters, excluding "inout" parameters.

The Challenge of MySQLi Interface

Unfortunately, the MySQLi procedural and object-oriented interfaces lack native support for output stored procedure parameters. As a workaround, one can utilize MySQL user variables to receive output values and retrieve them using a separate SELECT statement. By implementing user variables and SELECT statements, it is possible to effectively handle stored procedures with both input and output parameters.

Procedural Approach

Consider the following PHP code using the procedural MySQLi 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'];

In this example, MySQL user variables (@sum, @product, @average) are defined in the stored procedure. The call to mysqli_query("SELECT @sum, @product, @average") retrieves the values set by the stored procedure and assigns them to PHP variables ($procOutput_sum, $procOutput_product, $procOutput_average).

Object-Oriented Approach

Alternatively, the object-oriented MySQLi interface can be employed:

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

The object-oriented approach follows a similar pattern, utilizing the prepare(), bind_param(), execute(), and query() methods to call the stored procedure and retrieve the output values through user variables.

By harnessing MySQL user variables and SELECT statements, PHP developers can effectively call stored procedures that require both input and output parameters. This technique provides a workaround for the lack of native output parameter support in the MySQLi interface.

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