MySqli stored procedure with return from PHP

Question

I am trying to call a Stored Proc via PHP. But I am not getting desired output. I am getting kind of incorrect syntax error in PHP. If I run Stored Proc independently, it works as expected. Below I am giving code snippet of this -

  <?php

$host = "localhost";
$db_user = "userid";
$db_password = "password";
$db_name = "trialdb";

$con = mysqli_connect($host,$db_user,$db_password,$db_name);

$name = $_POST["Name"];
$id = $_POST["Id"];

$storedProc = "CALL CheckNameId(?,?, @returnStatus)";

$stmt = mysqli_prepare($con, $storedProc);
mysqli_stmt_bind_param($stmt,"si",$name, $id);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $returnStatus);

mysqli_stmt_fetch($stmt);
echo $returnStatus;

mysqli_stmt_close($stmt);
mysqli_close($con);

?>

I am getting error at mysqli_stmt_bind_param, mysqli_stmt_execute, mysqli_stmt_bind_result ... having first param passed as boolean. It is interpreting $stmt as boolean. Could I please know, what is wrong with my understanding ? I suspect there is something incorrect with the way I am using bind result. May be I am having the understanding gap with 'statement', 'result set' or processing 'result set' from stored procedure. If any good link, anyone could propose, I would love to refer.

Answer 1

A stored procedure OUT parameter only sets the value of the session variable, it doesn't return it in a result.

You would have to subsequently query the session variable yourself:

$storedProc = "CALL CheckNameId(?,?, @returnStatus)";
$stmt = mysqli_prepare($con, $storedProc);
mysqli_stmt_bind_param($stmt,"si",$name, $id);
mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt);

$sql = "SELECT @returnStatus";
$result = mysqli_query($con, $sql);
$row = mysqli_fetch_row($result);
echo $row[0];