How to Execute MySQL Stored Procedures with Both Input and Output Parameters Using PHP?

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

In PHP, executing MySQL stored procedures that require both input and output parameters can be a challenge. While MySQLi provides specific functions for handling input and output parameters separately, there is no direct support for this scenario.

To overcome this limitation, a workaround involving MySQL user variables is necessary. Here's a breakdown of how it works:

Using MySQLi Procedural Interface:

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

$mysqli = mysqli_connect('host', 'user', 'password', 'database');

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

Here, we first initialize the input variables and establish a connection to the MySQL database. A prepared statement is used to execute the stored procedure, with input parameters bound. After execution, a separate query is used to retrieve the output parameters stored in MySQL user variables.

Using MySQLi Object-Oriented Interface:

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

$mysqli = new mysqli('host', 'user', 'password', 'database');

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

This code demonstrates the same approach using the object-oriented interface, where database connection and stored procedure execution are handled through object methods.

By leveraging the workaround with MySQL user variables, you can effectively execute stored procedures with both input and output parameters in PHP using the MySQLi interface.

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