4
votes

Bit of confusion about what PDO::query does. Fully understand the difference between it and a prepare and execute, however it is unclear about if query runs a fetch. According to PHP.net:

PDO::query — Executes an SQL statement, returning a result set as a PDOStatement object

which is proven by:

$s = $pdo->query('SELECT * FROM user');
var_dump($s);

which outputs:

object(PDOStatement)#2 (1) {
  ["queryString"]=> string(18) "SELECT * FROM user"
}

So its clearly giving us a PDOStatement object ready to run fetch or fetchAll on. HOWEVER. Take the following code taken from the PHP.net manual:

$sql = 'SELECT name, color, calories FROM fruit ORDER BY name';
foreach ($conn->query($sql) as $row) {
    print $row['name'] . "\t";
    print $row['color'] . "\t";
    print $row['calories'] . "\n";
}

This indicates that running a PDO::query actually runs a fetch internally and returns a result (which is mentioned in the initial description for the method). However if that is true then a subsequent call to fetch should return the second row, however it does not, you still get the first row.

Does query run a fetch and reset the pointer so a subsequent fetch also returns the first row? if so, is this not unperformant? and why can you loop query and get multiple rows? does it run some kind of coroutine using yield? Additionally, why can you not see the fetched data in the PDOStatement object? Presumerably they are hiding this data using the __set_state and __debugInfo magic methods

1

1 Answers

4
votes

This is irrelevant to what does PHP PDO::query actually do. But rather to what PDOStatement actually is. And it is an object that supports a Traversable interface, so it can be iterated over. And when iterated, it is internally calling fetch() consequently.

it is unclear about if query runs a fetch

PDO::query never runs a fetch. PDOStatement does. In the following line the short syntax is used for brevity.

foreach ($conn->query($sql) as $row)

While it can be written as

$stmt = $conn->query($sql);
foreach ($stmt as $row)

as foreach is iterating over the result, returned by query, not running query again and again

why can you not see the fetched data in the PDOStatement object?

because there is no such data.

Hope it is all clear now