PHP PDO query bug

Question

I am learning how to use PHP PDO.

I have found that when i create a loop to iterate though a query i am only able to do so once.

in the code bellow both the loop for the FETCH_ASSOC mode and FETCH_OBJECT mode work on their own, but if i include both only the first one works.

I have tried changing the code so that both use the same fetch mode. this made no difference.

Also if i comment out the loop for the fetch_assoc mode, but leave the fetch_assoc mode call in place (so that setFetchMode() is called twice and the second call sets the correct mode) then the remaining loop still works.

So clearly the problem is caused by having multiple loops which fetch the data and not by having multiple setFetchMode() calls

What is going on here?

THE CODE

# setting the fetch mode  
$STH->setFetchMode(PDO::FETCH_ASSOC); 

# fetching
while($row = $STH->fetch()) { 
    print('assoc'); 
    echo $row['ID'] . "\n";  
    echo $row['Book'] . "\n";  
    echo $row['colour'] . "\n";  
    print('<p>');
}  

# setting the fetch mode again 
$STH->setFetchMode(PDO::FETCH_OBJ); 

# fetching
while($row = $STH->fetch()) { 
    print('object'); 
    echo $row->ID . "\n";  
    echo $row->Book . "\n";  
    echo $row->colour . "\n";
    print('<p>');  
}

Answer 1

After you have iterated over the result set in a first loop - there is nothing left and the second loop doesn't even perform a single iteration.

So the first loop terminates with $row = null; and the first iteration of the second loop starts with the same null value which prevents from even entering the second loop body.

So the behaviour you're experiencing is expected.