How to call a MySQL function and store the return value in variable using PHP?

Question

How can I call a MySQL function and store the return value in a PHP variable?

I have tried this:

$ret = mysqli_query($connection, "call FP_USER_EXISTS('$emailid')");

if ($ret==1)
{
    echo "User already exists \n";
}
else
{
  echo 'Inserted successfully';
}

Unfortunately, this does not provide the boolean return value that I expect. I want it to retrieve whether the user exists, but it's returning the success/fail status of the mysqli_query. The docs indicate this is TRUE for this type of query.

How do I get the return code from the actual MySQL function?

Possible duplicate of [PHP MySQL - Function](http://stackoverflow.com/questions/25076310/php-mysql-function) — Youcef LAIDANI, Jun 20 '16 at 10:06
[All this basic information is explained in the PHP manual](http://php.net/manual/en/mysqli.quickstart.statements.php) — Jocelyn, Jun 20 '16 at 11:35
mysqli_query returns a statement HANDLE on success, or a boolean false on failure, and a query which returns no data is NOT a failure, it's simply a valid successful EMPTY result set. you cannot test for your results with `== 1`. — Marc B, Jun 20 '16 at 14:34

score 1 · Answer 1 · answered Jun 20 '16 at 14:25

The MySQLi Stored Procedures documentation gives some specific guidance on calling stored procedures with MySQLi:

Stored procedures can return result sets. Result sets returned from a stored procedure cannot be fetched correctly using mysqli_query(). The mysqli_query() function combines statement execution and fetching the first result set into a buffered result set, if any. However, there are additional stored procedure result sets hidden from the user which cause mysqli_query() to fail returning the user expected result sets.

Result sets returned from a stored procedure are fetched using mysqli_real_query() or mysqli_multi_query(). Both functions allow fetching any number of result sets returned by a statement, such as CALL. Failing to fetch all result sets returned by a stored procedure causes an error.

Given this information, it's better to use the mysqli_real_query function to retrieve the results that you need. Use this code, instead:

$ret=mysqli_real_query($connection,"call FP_USER_EXISTS('$emailid')");
if($ret==1)
{
    echo "Error: " . mysqli_error();
}
else
{
    if ($row = mysqli_fetch_array($result)) {
        if ($row[0]) {
            echo 'Inserted successfully';
        } else {
            echo "User already exits";
        }
    } else {
        echo "No result returned";
    }
}

This code assumes that the stored procedure returns TRUE if a new record is inserted, or FALSE if it is not. If this is not the case (because these details are not present in the question), you will have to adjust accordingly. The additional error handling is used to make sure that the statement itself was successfully executed, and that at least one result row was returned.

How to call a MySQL function and store the return value in variable using PHP?

1 Answers1