Stored Procedure ouput parameter via php mysqli function

Hi,

I am trying to get the value of a stored procedure output parameter via php, but keep getting this error: “Number of bind variables doesn’t match number of fields in prepared statement.” I have the latest versions of mysql and php install.

The stored procedure is as follows:

DELIMITER $$

DROP PROCEDURE IF EXISTS `database`.`sproc_function` $$
CREATE DEFINER='database`@`host` PROCEDURE `sproc_function`(IN input_data VARCHAR(20), OUT output_data VARCHAR(20))
BEGIN
SELECT myvalue FROM my_table WHERE myvalue = @input_data;
END $$

The php is as follows:

$generic="example";
$link = mysqli_connect("host", "username", "password", "database");
$stmt = mysqli_prepare($link, "CALL sproc_function(?)");
mysqli_stmt_bind_param($stmt, "s", $generic);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $output_data);
mysqli_stmt_fetch($stmt);
mysqli_stmt_close($stmt);

I need to get the output $output_data without the error, “Number of bind variables doesn’t match number of fields in prepared statement”. I have doing a lot of research and am getting nowhere. If anyone had the solution via PDO and procedural PHP that would be awesome and would be greatly appreciated.

Sincerely,
Ryan

MySQL prepared statements work a bit differently than your standard run of the mill select result. Basically you’d pass in a session based variable as your out parameter, use a SELECT INTO to set the output, then do a select on that when you’re done. So:


DELIMITER $$

DROP PROCEDURE IF EXISTS `database`.`sproc_function` $$
CREATE DEFINER='database`@`host` PROCEDURE `sproc_function`(IN input_data VARCHAR(20), OUT output_data VARCHAR(20))
BEGIN
SELECT myvalue INTO output_data FROM my_table WHERE myvalue = @input_data;
END $$

As for calling it:


CALL sproc_function("something", @output);
SELECT @output;

which would give you the output result. Let me know if this makes sense, MySQL stored procedures can get weird sometimes.

Thanks Chris!

This info will definately give me some direction. However, once you get the output parameter, how would you get it via mysqli php functions.

Sincerely,
Ryan

Not tested, but here goes:


$generic="example";
$link = mysqli_connect("host", "username", "password", "database");
$stmt = mysqli_prepare($link, "CALL sproc_function(?,@output)");
mysqli_stmt_bind_param($stmt, "s", $generic);
mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt);
$result = mysqli_query($link,'SELECT @output');
list($output) = mysqli_fetch_row($result);
mysqli_free_result($result);

That’s about how it should work out.

Once again thanks Chris your help does not go unoticed.

Ok. A few more things. Now is there any way to get the result into one php variable say $results. I will need this $result variable to compare to another variable in an if statement later on in the PHP script.

Yup, here:

list($output) = mysqli_fetch_row($result);

Is where the output is assigned to a variable, so you’d just replace $output with $result to use for comparison and what not.