php - Prepared Statement; fetch() or fetch_all()? Issue with While Statement

Question

I am trying to de-bug my prepared statement code but having some problems. When using the below script, I see 'test one' and 'test two' echoed in my browser but no 'test three'; my ->fetch() statement doesn't appear to be working. No errors.

if (empty($login_errors)) { // OK to proceed!

echo 'test one';

$pas = get_password_hash($p);
$loginQuery = $dbc->prepare("SELECT id, username, type FROM user WHERE (email=? AND pass=?)");
$loginQuery->bind_param('ss',$e,$pas);
$loginQuery->execute();
$loginQuery->bind_result($id,$username,$type);

echo 'test two';

while($loginQuery->fetch()){

echo 'test three';

$idRow = $id;
$usernameRow = $username;

$_SESSION['user_id'] = $idRow[0];
$_SESSION['username'] = $usernameRow[0];
} 
echo 'test four';
}

My first thought was that ->fetch() is used when there is only one field being selected (one bind result). I tried using while($loginQuery->fetch_all()){ because i have three ( $loginQuery->bind_result($id,$username,$type);) but this brings back a HTTP 500 error.Is this thinking correct? fetch() for one, fetch_all() for many?

Why do neither fetches work? Can you see any issues with my code?

'test four' is displayed. This would suggest that the query is returning no data. This is confusing because before this prepared statement, I used concatenation and the below worked without any issues:

$q = "SELECT id, username, type, IF(date_expires >= NOW(), true, false) FROM user WHERE (email='$e' AND pass='"  .  get_password_hash($p) .  "')";      
$r = mysqli_query ($dbc, $q);

Answer 1

Is this thinking correct? fetch() for one, fetch_all() for many?

That's actually two different methods of two different objects.

• fetch() belongs to mysqli statement object and uses ugly method of assigning query result to global variables. Can be run in a loop. Loop is a thing where you can do fetch one single row... many times. So, nothing contradicting in using fetch() to get many records
• fetch_all() belongs to mysqli result object and being just a syntax sugar, running fetch_assoc() for you in a loop.