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

by

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

More Related Contents:

  1. How to call a MySQL stored procedure from within PHP code?
  2. How to write a stored procedure using phpmyadmin and how to use it through php?
  3. Store procedures in phpMyAdmin
  4. Calling stored procedure with Out parameter using PDO
  5. PHP + MySql + Stored Procedures, how do I get access an “out” value?
  6. Stored Procedures, MySQL and PHP
  7. How to write a stored procedure in phpMyAdmin?
  8. php+mysql Fatal error: Call to undefined function mysql_connect()
  9. Using PHP data in MySQL [closed]
  10. drop down menu using php and mysql [closed]
  11. php code for multiple search box after connecting to mysql [closed]
  12. Checking the availability of rooms
  13. How to sort user data by id
  14. Example of how to use bind_result vs get_result
  15. mysqli::query(): Couldn’t fetch mysqli
  16. Incorrect Integer (2147483647) is inserted into MySQL?
  17. I need my PHP page to show my BLOB image from mysql database
  18. jQuery UI Sortable, then write order into a database
  19. Delete multiple rows by selecting checkboxes using PHP
  20. mysql_real_escape_string VS addslashes
  21. How to add a delete button to a PHP form that will delete a row from a MySQL table
  22. MYSQLi error: User already has more than ‘max_user_connections’ active connections [duplicate]
  23. Is there any way to check the performance of mysql Indexing
  24. Understanding pdo mysql transactions
  25. “No such file or directory” or “No such host is known” when running migrations
  26. Using utf8mb4 with php and mysql
  27. how do I get month from date in mysql
  28. Strange problem with the length of the field gotten from database [duplicate]
  29. Can I detect and handle MySQL Warnings with PHP?
  30. mysql count into PHP variable

Leave a Comment