"bind_param" returning 0 rows

Question

I have a query with a parameter to bind stored in the session. I tested the query on the database and it should return 1 row but it does not! I tried everything. Its not an issue with the $id because I use it for another query and it is fully working:

$resultReturn = $con->prepare( 'SELECT `returns`.`return_id`, `returns`.`return_status`  FROM 

`agents` LEFT JOIN `returns` ON `returns`.`agent_id` = `agents`.`id` AND `agents`.`id` = ?');
$resultReturn->bind_param('i', $id);
$resultReturn->execute();
$resultReturn->fetch();
$resultReturn->store_result();
$resultReturn->bind_result($returnID, $returnStatus);

if($resultReturn) 
{ 
          echo $resultReturn->num_rows; //zero 
          while($row = $resultReturn->fetch_row()) 
        { 
          echo $resultReturn->num_rows; //incrementing by one each time 
        } 
          echo $resultReturn->num_rows; // Finally the total count 
}


$con -> close();

the first if returns always FALSE; If I set manually the id it works! Here is my other query at the beginning of the same page (and its perfectly working):

$con = mysqli_connect($DATABASE_HOST, $DATABASE_USER, $DATABASE_PASS, $DATABASE_NAME);
if (mysqli_connect_errno()) {
    exit('Failed to connect to MySQL: ' . mysqli_connect_error());
}
// We don't have the password or email info stored in sessions so instead we can get the results from the database.
$stmt = $con->prepare('SELECT password, email, role FROM agents WHERE id = ?');

// In this case we can use the account ID to get the account info.
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->bind_result($password, $email, $role);
$stmt->fetch();
$stmt->close();

It is because I'm using the same connection for multiple queries?

In the first example you fetch before binding. Also this is not a valid function `$row = $resultReturn->fetch_row())` — Dharman, Nov 05 '20 at 10:46
Try PDO instead of mysqli. It looks like mysqli is too difficult for you. PDO is much better and easier for beginners like you — Dharman, Nov 05 '20 at 10:47
Its not my app i just need to correct this bug wrote by someone else and cant find the issue. If i replicate the same procedure of the stmt query (which is working) it does not work. — SariHime, Nov 05 '20 at 10:49

score 0 · Accepted Answer · answered Nov 05 '20 at 10:50

Move fetch() after bind_result().

The correct order should be:

$resultReturn = $con->prepare(/* */);
$resultReturn->bind_param('i', $id);
$resultReturn->execute();
$resultReturn->store_result();
$resultReturn->bind_result($returnID, $returnStatus);
$resultReturn->fetch();

"bind_param" returning 0 rows

1 Answers1