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

Question 1

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!

Question 2

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

Question 3

To sum it up:

DELIMITER is implemented client-side, not serverside.

If you have a good driver or API, it may take care of this. PHP mysql / mysqli, as of now, do not.

If you need to use a different delimiter (e.g. because the default delimiter appears inside scripts), you have to encode/escape your SQL yourself or break it up so you don't need to change the delimiter. No help from PHP here.

Question 4

I develop and maintain a large PHP/MySQL application. Here is my experience with stored procedures.

Over time our application has grown very complex. And with all the logic on the php side, some operations would query the database with over 100 short queries.

MySQL is so quick that the performance was still acceptable, but not great.

We made the decision in our latest version of the software to move some of the logic to stored procedures for complex operations.

We did achieve a significant performance gain due to the fact that we did not have to send data back and forth between PHP and MySQL.

I do agree with the other posters here that PL/SQL is not a modern language and is difficult to debug.

Bottom Line: Stored Procedures are a great tool for certain situations. But I would not recommend using them unless you have a good reason. For simple applications, stored procedures are not worth the hassle.

Question 5

It looks like you are mixing and matching mysqli object oriented calls with the deprecated mysql_* functions. I'm not sure you can do that.

You are doing:

$r1 = $mysqli->query($sp)

And then passing that to

mysql_fetch_assoc($r1);

Looping through your results, you need to do:

while ($result = $r1->fetch_array())
{ 
    printf($fmt,$result[0], $result[1], $result[2], $result[3]);
}

Question 6

You can do this:

SELECT * FROM `information_schema`.`ROUTINES` where specific_name = 'my_procedure_name' and routine_schema = 'my_schema'

..and if it exists, should get a result. However, keep in mind that on the majority of shared hosting mysql services, routines, triggers and so on are not normally allowed to be created. If it's your own server, no problem ;)

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

Answers

php,mysql,stored-procedures