PHP: Call MySQL stored procedure with input and output parameters (not "INOUT")

Question

I want to call a stored procedure in MySQL from PHP. The process takes input and output parameters - is not an "INOUT" parameter.

To give a simple example, suppose 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, let's say I have the following variables (let's call them "proc input variables") and I want to provide them as input parameters to the stored procedure when I call It's:

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

Let's say on the PHP script/page side, I also have the following variables (let's call them "proc output variables") and I want to provide them as output parameters to the stored procedure when I call it When, is set by the stored procedure :

$procOutput_sum;
$procOutput_product;
$procOutput_average;

So, essentially, what I'm trying to do, in terms of PHP scripts/pages, essentially (I realize the following code is not valid) , is...< /p>

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

...Once called, the following PHP code will appear...

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

...should produce the following output:

Sum: 1368
Product: 44253432
Average: 456

Note that I would like to be able to do this using MySQLi Procedures functions/interfaces if possible. If it's not possible then I'll use a method to make it work.

I've been programming for a while, but the PHP language is a relatively new endeavor for me. I found tons of tutorials on calling MySQL stored procedures from PHP. Some are tutorials for calling stored procedures using input parameters, some are tutorials for calling stored procedures using output parameters, and some are tutorials for using inout<调用存储过程的教程/strong> parameters. I haven't found any tutorials or examples on calling a stored procedure that simultaneously inputs and output parameters, without specifically not using the "inputout" parameters. I can't figure out how to encode parameter bindings (eg: mysqli_stmt_bind_param and mysqli_stmt_bind_result) and get it to work.

Any help would be greatly appreciated, thank you in advance!

Answer 1

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

Answer 2

Unfortunately, MySQLi does not have any native support for outputting stored procedure parameters; one must instead output to a MySQL user variable and then obtain using a separate SELECT Value declaration.

Use program 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, use 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'];