MilosM MilosM - 1 month ago 6
MySQL Question

While and foreach different behaviour with fetching PDO::FETCH_OBJECT

While using something like:

$db = new PDO(connection_details)
$query = "SELECT * FROM vservers LIMIT 5";
$result = $db->query($query);


And then try to get records with while f.e.

while ($row = $result->fetchAll(PDO::FETCH_OBJ)) {
var_dump($row);
}


it returns an array with StdObjects like this:

array (size=5)
0 =>
object(stdClass)[3]
public 'vserverid' => string '898' (length=3)
public 'templatename' => string 'Debian' (length=14)
public 'template' => string 'debian-7.0-x86' (length=14)
1 =>
object(stdClass)[4]
public 'vserverid' => string '792' (length=3)
public 'templatename' => string 'Ubuntu' (length=33)
public 'template' => string 'ubuntu-15.04' (length=27)


And with
foreach
it returns StdObjects

foreach ($result->fetchAll(PDO::FETCH_OBJ) as $key) {
var_dump($key);
}

object(stdClass)[3]
public 'vserverid' => string '898' (length=3)
public 'templatename' => string 'Debian' (length=6)
public 'template' => string 'debian' (length=6)


object(stdClass)[4]
public 'vserverid' => string '792' (length=3)
public 'templatename' => string 'Ubuntu' (length=6)
public 'template' => string 'ubuntu' (length=6)


Can someone please explain this behaviour? Normally, I would like to return Objects like with
foreach
, but is it a good practice ?

Answer

fetchAll() returns all the results as an array, where each element is an object that represents a row from the table.

In your while code, the first iteration sets $row to the entire result set, and dumps it as a single array. There's only one iteration because the next call to fetchAll() returns an empty array, because there's nothing left to fetch.

In your foreach code, fetchAll() returns the array to foreach, which then iterates over it one element at a time, setting $key to each object. Then you dump that one object in your body.

Normally when you're using while you use fetch(), not fetchAll(). This code will be equivalent to the foreach:

while ($key = $result->fetch(PDO::FETCH_OBJ)) {
    var_dump($key);
}