PHP: Calling MySQL Stored Procedure with Both INPUT AND OUTPUT Parameters (NOT "INOUT")

Question

From PHP I would like to call a stored procedure in MySQL. The procedure takes input and output parameters -- not "INOUT" parameters.

For a simple example, say I have the following stored procedure in MySQL:

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 ;

Now, from the PHP script/page side, say I have the following variables (we'll call them "proc input variables") that I want to feed to the stored procedure as input parameters when I call it:

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

Let's say that on the PHP script/page side I also have the following variables (we'll call them "proc output variables") that I want to feed to the stored procedure as output parameters to be set by the stored procedure when I call it:

$procOutput_sum;
$procOutput_product;
$procOutput_average;

So, in essence, on the PHP script/page side, what I want to be able to do, in essence (I realize the following code is not valid), is...

call test_proc($procInput1, $procInput2, $procInput3, $procOutput_sum, $procOutput_product, $procOutput_average);

...and, once called, the following PHP code...

echo "Sum: ".$procOutput_sum;
echo "Product: ".$procOutput_product;
echo "Average: ".$procOutput_average;

...should produce the following output:

Sum: 1368
Product: 44253432
Average: 456

One caveat is that, if at all possible, I would like to be able to do this using the MySQLi procedural functions/interface. If not possible, then however I can get it to work is what I'll use.

I have been programming for quite some time, but the PHP language is a relatively new endeavor for me. I have found tons of tutorials on calling MySQL stored procedures from PHP. Some are tutorials on calling stored procedures with input parameters, some are tutorials on calling stored procedures with output parameters, and some are tutorials on calling stored procedures with inout parameters. I have not found any tutorials or examples on calling stored procedures that take both input and output parameters at the same time, while specifically not using "inout" parameters. I'm having trouble figuring out how to code the parameter bindings (e.g.: mysqli_stmt_bind_param and mysqli_stmt_bind_result) and getting it all to work properly.

Any help will be greatly appreciated and I give thanks in advance!

Answer 1

Unfortunately, MySQLi does not have any native support for output sproc parameters; one must instead output into MySQL user variables and then fetch the values using a separate SELECT statement.

Using the procedural 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'];

Or, using the object-oriented interface:

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

Answer 2

 DB::select('call SP_fci_cb_emp_comp_salary_revision_add(?,?,?,@,@)',
                        [
                            $my_compensation['detail']['employee_master_id'],
                            Carbon::createFromFormat('d/m/Y', $salary_revised_result['detail']->effective_date)->format('Y-m-d'),
                            $request->user_id
                        ]);