Martin Martin - 1 month ago 5
MySQL Question

How to return a single value from a function from MySQL to PHP?

I've been struggline with this for a while now. I am simply not able to return a single value. I can return arrays just fine.
But not a single value. I have a database with just three columns, column1 is userID, column2 is friendID and column3 is IfFriends, which has the value 0 or 1.

Now if I make the following function:

function CheckFriend($id, $friendid){
global $mysqli;

$stmt = $mysqli->prepare("SELECT IfFriends FROM friends WHERE userID=?
AND friendID=?");
$stmt->bind_param("ii", $id, $friendid);
$stmt->bind_result($IfFriends);
$stmt->execute();

return $IfFriends;


Now am I doing it wrong? I tried the SQL query and it works, it gives me back the 1. However if I use the function it gives me back 0.

In PHP I have it written like this:


$iffriends = CheckFriend($_SESSION["id"], $_REQUEST["friendid"]);


And when I do:


echo $iffriends


It always gives me back a 0, no matter what.

I hope someone is able to take the time and tell me what I am doing wrong.
Thank you in advance.

Answer

Read the documentation:

Note:

Note that all columns must be bound after mysqli_stmt_execute() and prior to calling mysqli_stmt_fetch(). Depending on column types bound variables can silently change to the corresponding PHP type.

So your call to bind has to be after the execute and you will need to call fetch to read the first row.

// Parameters bound before execute
$stmt->bind_param("ii", $id, $friendid);

// Execute the statement
$stmt->execute();

// Result location bound (but this doesn't retrieve anything)
$stmt->bind_result($IfFriends);

// Fetch the first row of the result, stored into the result variable bound above
if($stmt->fetch())
{
  // Make sure you check the result of fetch().
  // If it returned false, no rows were returned.
  // In this case, it returned true, so your value is in $IfFriends
}

// If you happened to have multiple rows, you would call fetch again
// to retrieve the next row.
if($stmt->fetch())
{
  // You can continue calling fetch() until it returns false.
  // Most often this is done in a loop like:
  // while($stmt->fetch()) { /* Do something with the row */ }
}
Comments