PHP: Calling MySQL Stored Procedure with Both INPUT AND OUTPUT Parameters (NOT "INOUT")
Handling mixed parameter types in MySQL stored procedures is a common stumbling block when moving beyond simple input-only queries. Many developers encounter this specific scenario when building legacy systems or optimizing complex calculations that require returning multiple computed values. The confusion often stems from the fact that PHP's mysqli interface handles IN, OUT, and INOUT parameters distinctly, requiring a different binding strategy than what is used for standard prepared statements.
Consider a stored procedure where you need to pass three integers to calculate sums, products, and averages, but return those results via separate output variables rather than modifying inputs in place.
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 ;
In this example, the procedure defines three IN parameters for data entry and three OUT parameters for retrieval. The goal is to initialize the PHP variables representing the inputs, bind the output variables to receive results, execute the procedure, and then echo those results.
The critical detail lies in how you prepare the SQL string and bind the parameters using the procedural API. Unlike standard prepared statements where you use ? placeholders for everything, calling a stored procedure requires explicitly stating the parameter names or types within the SQL call itself. Furthermore, because the parameters are split between input and output, you cannot rely on a single bind_param call to handle both directions simultaneously in the traditional sense.
To achieve this with the MySQLi procedural interface, you must execute two distinct binding operations: one for inputs and one for outputs. First, prepare the call string without placeholders for the parameters, as their names are hardcoded in the CALL statement. Then, use mysqli_stmt_bind_param to attach your input variables.
$procInput1 = "123";
$procInput2 = "456";
$procInput3 = "789";
// Initialize output variables (must exist and be initialized)
$procOutput_sum = null;
$procOutput_product = null;
$procOutput_average = null;
// Prepare the statement with explicit parameter names
$stmt = $mysqli->prepare("CALL test_proc(?, ?, ?, ?, ?, ?)");
// Bind input parameters (IN)
// 'i' for integer, 's' for string if needed, 'd' for double
mysqli_stmt_bind_param($stmt, "iii", $procInput1, $procInput2, $procInput3);
// Execute the procedure
mysqli_stmt_execute($stmt);
// Bind output parameters (OUT) using mysqli_stmt_bind_result
mysqli_stmt_bind_result($stmt,
$procOutput_sum,
$procOutput_product,
$procOutput_average
);
// Fetch the results
mysqli_stmt_fetch($stmt);
echo "Sum: " . $procOutput_sum;
echo "<br>";
echo "Product: " . $procOutput_product;
echo "<br>";
echo "Average: " . $procOutput_average;
There are specific constraints to keep in mind. The output variables must be initialized before binding them. PHP does not automatically create variables for you, so attempting to bind $procOutput_sum without declaring it or assigning a default value will result in a runtime error. Initializing them to null is the standard practice.
Additionally, while the example above uses procedural functions as requested, the object-oriented API (new mysqli) follows the exact same logic but with slightly different method syntax. If you are already using Laravel or another framework that enforces OOP patterns, ensure your connection object supports these methods appropriately. The underlying mechanism remains identical: separate binding for input preparation and result retrieval.
It is also worth noting that mixing IN and OUT parameters does not utilize the INOUT type. If a procedure requires updating an input variable and returning it simultaneously, you must use INOUT. However, if your logic strictly separates data entry from data return, the dual-bind approach described here is the correct implementation. This method ensures that the database engine can distinguish between parameters meant to be read by the server and those meant to be written back to the client context without ambiguity.